What Are Pivot Tables Used For?
What Are Pivot Tables Good For?
My last post described spreadsheet Pivot Tables and tonight I was talking with my good friend Patrick Richards. He commented that he wasn’t left with a clear sense of what a Pivot Table is. Seems that I talked around Pivot Tables so much that I didn’t provide enough substance. So, here’s a second go. (A screencast of Pivot Tables in action is here.)
First. Here’s the overall point. Pivot Tables are like drag-n-drop database queries. For my clients, and anyone else who works with data, I advise learning Pivot Tables because they generate quick, custom, and concise summaries of what’s inside massive data sets. (Sometimes, it can be ugly.)
PIVOT TABLES PART 2 Let’s start our review with some raw data: A list of members and promises they made for generating donations over 4 campaigns
Without Pivot Tables, we can make easy summaries of things like:
- How much was raised in Campaign B? How about A&B compared to C&D?
- How many members are in Florida?
- What was the TOTAL promised by all the members?
So, you’re chillin’ and watching “Oh My Dayum” on YouTube when the phone rings. The membership committee wants detailed info on who’s paid their fees.
PIVOT TABLE TIME!
(Click on any of the images below to see full size)
SCENARIO I: Who’s paid their fees?
Look at the image to the right and notice how the box to the right lists the column headers from the original data. If we want that data, we check-mark the box. In this image Name and Fees Paid are the only ones checked. We aren’t distracted with the Ages, Promises, etc.
IMPORTANT: Because of the way Pivot Tables work, now we see something bizarre, as marked by the arrows!
How have Ann and Craig paid their fees AND they’ve not paid their fees? They should be one or the other. Had we just sorted or filtered this information, we wouldn’t have exposed this problem.
You make a few phone calls and learn that Ann and Craig each have duplicate profiles. The database person is now working on merging the files.
SCENARIO II: How are the membership levels performing?
Now someone is curious about how the 4 Membership Levels have been doing year-over-year.
The Pivot Table shows that Teal has held steady, but what happened to Ultramarine in 2011?
SCENARIO III: How are single people performing and what states are they in?
A Pivot Table can quickly dig into subcategories.
How have single people done in fund-raising, and list the results by their State of Residency.
Georgia’s single folks are doing the best. But look at row 19. We don’t know the State(s) where $13,242 was generated.
CONCLUSION
There is so much more that Pivot Tables can do. You can add calculation fields, modify the layout, and add many layers of investigation. Hence, we can think of a Pivot Table as drag-n-drop database queries.
Basic skill with Pivot Tables = access to so much more information than if you create and rely on a handful of canned reports.
And … you’ll see where your data isn’t so clean.
Patrick, I hope this helps!
Doug Amos Yes. This is what we were discussing. Have a read. Share your thoughts, ask questions. :thumbsup: