Designing a pivot table by hand is one thing. Calculating the values is a task for Excel! Excel’s PivotTable tool takes our pivot table design and dynamically creates the corresponding pivot table within a spreadsheet.
We’ve provided a slideshow in the Learning Environment that illustrates the process of creating a pivot table in Excel.
A quick note: you might notice that when Excel automatically references a range in the PivotTable dialogues, it will put the sheet name followed by ! before the range. This sheet name syntax is for referencing ranges that may be on a different sheet than the original table. If you are typing in a range on the sheet you have currently selected, you can type in ranges as usual without the sheet name.
When you’re ready to try creating pivot tables yourself, move on to the next set of instructions!
Instructions
When you’re ready to practice what you’ve learned, download and work through our interactive exercise spreadsheet. A few things to keep in mind:
- Because pivot tables take up space, each exercise has its own spreadsheet.
- If the pivot table menu and PivotTable Analyze tab disappear, select a cell within the pivot table to restore them.
- Remember that pivot tables are very flexible, and there’s more than one way to set up a table to answer a particular question. Feel free to experiment within the PivotTable tool options as you work on an exercise. Seeing how different decisions produce different tables helps build an instinct for constructing pivot tables!
- If you get stuck, feel free to download our solutions spreadsheet and compare your work to ours!
Reflection Question: Suppose you were asked to find out how many countries had book titles per capita data for each decade (at least, in this dataset). Could you calculate this by setting up a pivot table with row labels from Decade, values from Country, and summary method Count?
Our Answer
Count will end up overcounting certain countries. Algeria, for example, appears twice in the 50s: once in 1953 and once in 1954. Both of these will be counted in the pivot table as countries with records in the 50s, but Algeria is only one country and should only be counted once.