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.

pivot1

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

pivot2

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.

pivot3

After you click okay, Excel opens up your new Pivot Table

pivot4

Average Student Grade for Q1

Drag Stud_ID down to the ROWS box and Q1 down to Values

pivot5

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…

pivot6

Now select Average and click OK

pivot7

Now you have averages in your table

pivot8

Average grades in classes

To find averages for classes, drag Stud_ID out of ROWS and drag Class into ROWS

pivot9

Now you have average grades for classes.

pivot10

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.

pivot11

pivot12


If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT. 

Follow this link for more Excel content: Excel

One thought on “Excel: Intro to Pivot Tables

  1. I see you don’t monetize your blog, don’t waste your traffic, you can earn additional cash every month because you’ve got hi quality content.

    If you want to know how to make extra money, search for: Mrdalekjd methods for $$$

    Like

Please Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s