Excel Tip of the Week: Pivot Tables

This week’s topic is pivot tables.

Pivot tables are for organizing and summarizing large amounts of data in a quick and easy fashion. Anything you do with a pivot table can also be accomplished with formulas. However, pivot tables have a couple advantages over using formulas. First, it is quicker. Pivot tables allow users to drag-and-drop their information into a summary table without having to know a single formula. Second, pivot tables allow for greater flexibility. If your data is not arranged to your liking, then a few clicks of the mouse can completely rearrange the data. This is much more flexible than rewriting several formulas.

Let’s walk through an example that highlights some of the most basic pivot table features. Please download the example spreadsheet here.

When you open the spreadsheet you’ll notice that there is a large amount of data arranged in columns with column headers. This is how data needs to be arranged before using it in a pivot table. The information in the example spreadsheet is a list of over 2000 “green” cars along with information about those cars such as: fuel type, number of cylinders, air pollution score, etc. Follow these steps to create a pivot table for this data:

1. Place your cursor anywhere inside the data range.
2. From the ‘Insert’ menu in the ‘Tables’ section click on ‘Pivot Table’ (keyboard shortcut: Alt → N → V → T) Mac: From the ‘Data’ menu click the arrow to the right of the ‘Pivot Table’ icon and select ‘Create Manual Pivot Table’
3. The ‘Create PivotTable’ box should pop up (see picture below). Make sure that Excel has selected the entire range and the Pivot Table is going to be created in a ‘New Worksheet.’ Click OK.
   
4. Excel will insert a new worksheet and in this worksheet you should see a ‘Pivot Table Field List.’ (Mac: it is called ‘Pivot Table Builder’) It is from this area you will drag and drop the information you want. (Side note: I think it is always a good idea to rename the worksheet immediately so you don’t lose track of where your pivot table is. Name it something like ‘Car Pivot.’)
5. Start by clicking on ‘Veh Class’ and dragging it to the ‘Row Labels’ box. Excel will pull out each unique vehicle class and list it alphabetically as shown in the picture below.
   
6. Now click and drag ‘Air Pollution Score’ into the ‘Values’ section. Take a look at your pivot table now. Notice how at the top of the pivot table it says: “Sum of Air Pollution Score.”
7. Having the sum of all the scores is not very helpful in this case. We would rather have the average in order to compare the vehicle classes better. Look back to the ‘Pivot Table Field List’ in the ‘Values’ box. You’ll notice it says “Sum of Air Pollution Score” there as well. Click on the small arrow next to that (Mac: It is an italicized ‘i’) and then select “Value Field Settings.” You should see a box that looks like this:
   
8. From this box select “Average” and then click OK. Now the numbers in the pivot table should make more sense. I would encourage you at this point reformat the numbers in the table to show only two decimal places.
9. Click and drag the ‘Cyl’ field into the ‘Column Labels’ Box. The Pivot table now shows the average air pollution score for a given vehicle class based on the number of cylinders in the car. For example, all the 6 cylinder minivans have an average air pollution score of 6.52. Here is a screen shot:
   
10. Click and drag ‘Fuel’ into ‘Report Filter’. Look above the pivot table in row 1. Excel just inserted a dropdown box. If you click on this dropdown box you can include or exclude certain fuel types from the data table. Try it for yourself and notice how the report changes as you select different things. Here is a screen shot:
    
11. Finally, look at cell E9 which should read 7.17 (if you don’t have any filters on). That number represents all the 5 cylinder small cars. But what if we want more information about which cars actually make up that number? Pivot tables have a feature to let you see just that. Double click the number and Excel will create a new worksheet showing you the cars that make up that specific number. One of the coolest pivot table features in my opinion.

That covers most of the basics of pivot tables. The great thing about pivot tables is that they are very conducive to trial-and-error. I would encourage you to drag-and-drop some more on the example spreadsheet to see what other ways you could look at the data.