Excel Pivot Table Example

Pivot Table ExampleMany students and other Excel users often ask me what a pivot table is in Excel. Most users really have never needed them. It really is a rather useful tool if you know how to use it. I have put together this small Excel Pivot Table Example to help students understand what it is and how to use it using this real world example.

My wife works for a school. She has a list of student names and tshirt sizes for each student. The company that will be making custom tshirts for the student asked for a list of sizes and counts for each.

To start with here is a screen shot of the spreadsheet used. (Names have been removed and I am only using 50 students in this example). If you would like to download the example used to follow along.    pivot-table-example.zip (88 downloads)

Pivot Table Example Spreadsheet

Highlight Data

To create the Pivot Table we first highlight the data (including the column headings) that we would like to include in our Pivot Table.

Pivot Table Example - highlighted data

Next Click on the Insert Menu item to bring up the Excel Insert ribbon at the top of the screen. Then click on Pivot Table.

Instet a Pivot Table

After clicking on Pivot Table (as shown above) the following screen appears. The Table/Range should be filled in as we highlighted our data before clicking the button. The option to add the table to a new or existing worksheet is up to you. If you selected New Worksheet a new tab will be added to the botton of your Excel workbook. For my example I will be adding the pivot table to the same sheet the data is on.

Pivot Table Wizard

Edit Pivot Table

Once the above options are set and OK is pressed you will see the Pivot Table Fields section in Excel on the right hand side.

As this pivot table is a very simple example there are only a few items we need to select. First select the Size checkbox in the list of Fields we would like to add. Adding the Size column will add all of the unique vaules that were in that column.

begining of pivot table

Next we need to add a second column. The data we would like to show is a count of the individual sizes for each size listed. To do this we can Click and Drag the Size column down to the Values section on the right hand side of the Excel window as shown below.

create pivot table count column

If Excel does not automatically set the option to Count of Size you can make any necessary changes by clicking on the small triangle and selecting the Value Field Settings. In here you can select various options such as Sum, Average, Min, Max and others.

Value Field Settings for Pivot Table Column

Pivot Table Example Final Result

Excel Pivot Table Final Result

As you can see in this simple examle the pivot table is shown to the right of our data on the same worksheet but it could be on a separate sheet entirely.  Although we only have 50 users in this example it may not have been very difficult to count up the various sizes, but imagine if you have 50,000 users.

Adding a Pivot Table Chart

If you would like to take this one step farther and add a Pivot Table Chart please continue reading. To add a chart we can do so in Excel very easily. Highlight the same data we highlighted earlier and on the Instert menu click on the Pivot Chart icon.

Adding a Pivot chart

Add the same field options to the Pivot Chart Fields screen as we did earlier.

Pivot chart fields

Once completed you should now have a chart with the pivot table data that you can then move to the location on the worksheet you would like it to appear as well as choose the format for the chart itself.

Pivot Table with Chart

Hope this Excel Pivot Table example helps some of my other readers and students. Please leave any questions or comments below.

To take this one step further have a look at this article that explains how to add a percentage column to your pivot table

To see more Excel Tips