1 of 15

Excel Pivot Tables

Valerie Collins and Allison Langham-Putrow, University Libraries

2 of 15

Overview

Excel, briefly
What is a PivotTable?
When to use PivotTables
Some tips
Limitations
Let’s make a PivotTable!

z.umn.edu/305l

3 of 15

Excel, briefly

Excel can be precisely the tool you need for the job, but...

4 of 15

Excel, briefly

When you open up a spreadsheet:

What do these colors mean?
Wait, there are hidden columns?
What is this data doing here on Worksheet 3?
What is this comment trying to say?
Why does this one worksheet have four tables of data?
Why does this formula keep getting in the way?
That was supposed to be a number, not a date!

5 of 15

Excel, briefly

Label everything
Keep documentation
Be aware of Excel’s worst habits

6 of 15

What is a PivotTable?

This is regular Excel data. I want to know how many days, on average, these 311 calls are open, per category of call type. How do I do this?

7 of 15

A PivotTable will quickly summarize and order tabular data for you - no need to mess around with formulas

8 of 15

Deciding when to use PivotTables

A Pivot Table is good for….	A Pivot Table is not good for...
Summarizing and aggregating tabular data	Non-aggregating data (not all data that fits in a tabular format should be counted, summed, or averaged: e.g., geographic coordinates)
Exploring data & finding outliers	Deep analysis
Answering specific questions	Handling more than a few variables at a time
Quick reports	Large datasets (how large is “large” will mostly depend on your computer’s processing capabilities)

9 of 15

Don’t do this to yourself

10 of 15

Stay organized

11 of 15

Tips to help your future self

Try to limit one PivotTable per worksheet
Label your worksheets
Label your charts
Any tables or charts you use in your final report should be maintained on separate worksheets in your analysis file

12 of 15

Important Limitations

PivotTables are designed to be dynamic
PivotTables take care of formulas automatically, essentially black-boxing them
PivotTables remain connected to the same source data, and changes you make to one PivotTable may affect another, even across worksheets

Microsoft Excel is an incredibly common program - the odds are that you or someone on your research team has it. There may be times where a PivotTable is your only or your most convenient option for analyzing or visualizing your data. But, while Excel PivotTables are powerful, they also have limitations when it comes to handling your data. This is particularly true when you want to make sure you can retrace your own steps later. Here are some things to keep in mind

PivotTables are designed to be dynamic. It’s easy to overwrite a PivotTable as you explore your data, and you might not remember how you got certain numbers if you don’t keep track of intermediate PivotTables
PivotTables take care of formulas for you. This is great because you can’t input formulas incorrectly, but it also makes it harder to figure out what Excel is doing to data in your PivotTable, and to correct mistakes if Excel is misinterpreting your data.
PivotTables based on the same source data update automatically as you change groupings and other components in a different PivotTable -- even across worksheets. You may be unknowingly altering a “finished” PivotTable by working on a different PivotTable with the same source data.

13 of 15

So, PivotTables and Reproducibility?

Don’t pivot and run
The things that make PivotTables so useful for analysis are also the things you need to watch out for in making sure your work will be reproducible

14 of 15

Requirements of a PivotTable

No empty column headings
Every column heading is unique
Every row is unique (no duplicates)
No empty rows or columns (Excel will not include data after an empty row/column in analysis)

15 of 15

Alright! Let’s create a PivotTable

Data: z.umn.edu/exampledata