Seems like it’s been eons since I posted an Excel tutorial, so I thought I’d write a brief intro on how to create pivot tables in Excel. Pivot tables are unnecessarily mysterious and perplexing to many people, but as you’ll soon see, they’re easy to set up and a convenient way to look at spreadsheet data once you get the hang of it. There are multiple ways to set up a pivot table, but this tutorial goes through how I usually do it, and the method works for both Excel 2007 and 2003.
First, suppose we have the data below, which shows two managers, their employees, and the number and amount of sales each employee made in 2008 and 2009:
Let’s pretend we want to find out how many sales each manager, Bob and Bill, had in 2009. How would we do this using a pivot table?
Step 1: Highlight the data you’re interested in analyzing. Again, different people have different ways of building pivot tables, but this just happens to be the one I use most often:
Step 2: In Excel 2007, go to the “Insert” tab (circled in red below), and click on the “PivotTable” icon. (In Excel 2003, go to the menu item “Data” and select “PivotTable and PivotChart Report”. Click on “Next”, and then “Finish”)
Once you click on the icon, a pop-up window will appear:
which prompts you to fill in blanks on where the data is that you want to analyze and a few other things. Since we already highlighted the data in Step 1, the range of the data has already been pre-populated for us. Just hit the “OK” button and the pop-up window will go away and you’ll see the following appear in your worksheet (click image to enlarge):
You’ll notice there are several areas with labels on them, like “Drop Row Fields Here” and “Drop Column Fields Here”. I think this is probably the most confusing part of building a pivot table. Chances are, you will most often use the Row and Data Fields areas, so let’s focus on these first.
Step 3: Build your pivot table by clicking and dragging items. We’re interested in looking at the data by manager, so the first thing we want to do is click on “Manager” in the field list menu (click image to enlarge):
and then drag the entire word into the “Drop Row Fields Here” Section.
Once you drop “Manager” into the “Drop Row Fields Here” area (circled in red above), you’ll see the data appear, with “Bill” and “Bob” since these are the only two “Managers” we have in our data. In Excel 2007, you can also drag the word into the “Row Labels” area within the pop-up window but I’m ignoring the additional features of 2007 for now.
Next, we wanted to look at the total # of sales by Manager, so we repeat the same thing we did above by clicking on the “# of sales” item in the list and dragging it, this time, to the “Drop Data Items Here” section (click image to enlarge):
Again, once “# of sales” is dropped into the data items area, the numbers immediately appear, summing the “# of sales” together by manager.
One final step. Remember that our data was for 2008 and 2009, so the # of sales by manager that we see is the total for both years. Suppose we just wanted to see the # of sales for this year. We could do this by clicking on “year” in the field list and dragging “year” to the “Drop Page Fields Here” area (click image to enlarge):
Again, as soon as you drop “year”, you’ll see a drop-down menu appear that defaults to “all” (click image to enlarge):
Click on the drop-down menu and you can select “2009” from the list instead (click image to enlarge):
As soon as you choose “2009”, the figures change to show “# of sales by manager in 2009” for a total of 54 sales instead of 87 :
I’ll write more tutorials on how to work with pivot tables, but this is just a way to get you started. Feel free to leave comments with questions about pivot tables and I’d be happy to try to cater future tutorials toward answering the most popular ones. Hope this has been helpful!