Excel Pivot Tables ('sigh of relief')
Oh, I am pleased for pivot tables! They make your life soooo much easier. I am not an expert on pivot tables or excel, in fact, I am an absolute pivot table beginner. But this is pretty easy and worth the hour it has taken me tonight to figure out how to solve the problem. It won't take you this long, because I have the solution right here!
My goal: I wanted to analyse the student success data from NCEA results that NZQA offers which is 2004-2014. I wanted to find out if the changes I had made since becoming HOD of our department in 2007, had long term effects on our student outcomes.
I already had many years worth of NCEA data but made sure I had all of it. I had to edit the format of the data to make sure it was uniform in format.
Problem: to get the averages of % achieved, % merit, % excellence for each subject, for each level, for each year WITHOUT calculating it all by hand. I figured it was worth spending time to find an answer because the alternative was going to take me hours and hours and be mistake ridden.
Solution:
So I am writing the steps here as much for you as for me. In January when I come to add the 2015 data, I want to remember how to do this. You know how it is, you do these things once a year and then forget again by next year and have to spend that time figuring it out all over again.
By-the-way...I should also state that I am using Excel from Office 2013 but I know earlier versions have pivot tables but the details of the instructions might differ slightly.
Place cursor somewhere in data
INSERT Pivot Chart & Table - the dialogue box comes up
The data range selected all by itself
I chose where to put the pivot table, it can be in the existing sheet or another one
Click OK
The table appears at the place you chose and a 'chart' or graph appears too. You can move the graph about later to a final spot.
CHECK in the list at the top date, subject, level, %A, %M, %E. This all appears in random places in the bottom section. Pick up the Date with your mouse and move it to the AXIS box, Put the subject and level in the FILTER box, and put the %A, %M, %E in the VALUE box.
You can alter the mathematical function applied to the %A, %M, %E by clicking on it and choosing VALUE FIELD SETTINGS and selecting AVERAGES. All the time you make these changes the chart will alter to reflect your changes.
When this is complete click the X in the pivot table wizard in top right hand corner and the wizard disappears to reveal the pivot table underneath.
You can select, using the filters, the data you want to see, it shows in the table and the change is reflected in the chart too.
SAVE
If you have subject reports to write then you can copy the pivot table info and paste it into your report as PASTE VALUES and just the numbers pastes into your report, without all the excel algorithm bit behind it.
You'll have the swishest reports ever. Happy days.
:)
My goal: I wanted to analyse the student success data from NCEA results that NZQA offers which is 2004-2014. I wanted to find out if the changes I had made since becoming HOD of our department in 2007, had long term effects on our student outcomes.
I already had many years worth of NCEA data but made sure I had all of it. I had to edit the format of the data to make sure it was uniform in format.
Problem: to get the averages of % achieved, % merit, % excellence for each subject, for each level, for each year WITHOUT calculating it all by hand. I figured it was worth spending time to find an answer because the alternative was going to take me hours and hours and be mistake ridden.
Solution:
So I am writing the steps here as much for you as for me. In January when I come to add the 2015 data, I want to remember how to do this. You know how it is, you do these things once a year and then forget again by next year and have to spend that time figuring it out all over again.
By-the-way...I should also state that I am using Excel from Office 2013 but I know earlier versions have pivot tables but the details of the instructions might differ slightly.
Place cursor somewhere in data
INSERT Pivot Chart & Table - the dialogue box comes up
The data range selected all by itself
I chose where to put the pivot table, it can be in the existing sheet or another one
Click OK
The table appears at the place you chose and a 'chart' or graph appears too. You can move the graph about later to a final spot.
CHECK in the list at the top date, subject, level, %A, %M, %E. This all appears in random places in the bottom section. Pick up the Date with your mouse and move it to the AXIS box, Put the subject and level in the FILTER box, and put the %A, %M, %E in the VALUE box.
You can alter the mathematical function applied to the %A, %M, %E by clicking on it and choosing VALUE FIELD SETTINGS and selecting AVERAGES. All the time you make these changes the chart will alter to reflect your changes.
When this is complete click the X in the pivot table wizard in top right hand corner and the wizard disappears to reveal the pivot table underneath.
You can select, using the filters, the data you want to see, it shows in the table and the change is reflected in the chart too.
SAVE
If you have subject reports to write then you can copy the pivot table info and paste it into your report as PASTE VALUES and just the numbers pastes into your report, without all the excel algorithm bit behind it.
You'll have the swishest reports ever. Happy days.
:)
Comments
Post a Comment