Pivot Tables are a built in feature to Excel that allows for better data analysis. Using Pivot Tables, you can work with aggregates with ease.
Download this Excel file to follow along: studGrades
Let’s look at the data:
This data set represents Grades for 5 students across four quarters.
Trying to calculate overall averages for each student or each class would require a lot of work using just straight spreadsheet functions. Pivot Tables make such tasks a breeze though.
Create a Pivot Table
Highlight you data set and go up to the Ribbon Bar > Insert>Pivot Table
We are just going to hit OK here. But if you want to know a bit more about the window:
- Choose the data that you want to analyze: notice the table range is A1 through F20 on the Courses tab. This is our data set. – Use an external data source lets you grab your data from another program if you want
- Choose where you want the PivotTable report to be place. – Default New Worksheet opens the table in a new sheet (our option) – Existing Worksheet allows you to place the table on an existing page
- Choose whether you want to analyze multiple tables – this is more advanced. This lets you take multiple tables and join them together for analysis.
After you click okay, Excel opens up your new Pivot Table
Average Student Grade for Q1
Drag Stud_ID down to the ROWS box and Q1 down to Values
Now we don’t want a Sum, we want an Average. So click on the down arrow by Sum of Q1 in VALUES. Select Value Field Settings…
Now select Average and click OK
Now you have averages in your table
Average grades in classes
To find averages for classes, drag Stud_ID out of ROWS and drag Class into ROWS
Now you have average grades for classes.
The other 3 quarters
Just drag Q2, Q3, and Q4 into VALUES. Note they will most likely default as SUM, so you will need to change them to Average same way you did above.
If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT.
Follow this link for more Excel content: Excel