Site icon Analytics4All

Excel: Intro to Pivot Tables


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:

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

Exit mobile version