What Are Pivot Tables Used For?
What Are Pivot Tables Good For?

Many of you have heard of Pivot Tables and aren’t sure what they are. I’ll leave you to Google how to mechanically get one working but if you’re like me, teaching the mechanics still won’t show the usefulness. Much like knowing a lot of music scales doesn’t get you playing anything that people want to dance to.

So, what’s the big deal with Pivot Tables?
(Here is a screencast where I use a Pivot Table along with a VLOOKUP and Data Generator.)

Of all the tools that I use for Data Management or Data analysis, I use Pivot Tables with every single project. On one recent gig, a client had 2000 rows of data and a sponsor code on each row. Those sponsor codes represent people, and we needed to retrieve their names from a different source.

Of these 2000 entries, how many are unique?

So …
Are we going to be looking up and matching 2000 codes and names?
Or, are there just 10 codes that represent 200 entries each?

Quick use of the Pivot Table reveald that we had just 40 unique codes AND a few entries that had no sponsor (a surprising issue that had to be corrected). We didn’t use the Pivot Table any more for the rest of the project but it let us know how big our task was before we dug in.

More traditionally, Pivot Tables are used to review relationships between data and are especially useful when your existing reports aren’t set up to view data from a certain angle:

Pivot Tables are like X-Ray Vision

  • How many of a company’s 400,000 customers are in WA, TX, & OH? And are they purchasing more classrooms or products?
  • How many of a club’s 300 members are under 25 yrs old, and have  90% to 100% attendance at meetings?
  • What is the total amount a retailer spent on overnight shipping to CA, WA, NV and OR?
  • Lets say that we can see how our product lines did in 3 separate reports. How can I get ONE report that shows all 3,  year-over-year by month?

All of those answers can be found via pivot tables.

When I was Executive Director of a nonprofit someone asked, “how many of your donations have been sent overseas?” I didn’t know but I had a pivot table: 88% of donations stayed in the US and 12% went abroad. It took more time to open Excel than it did to get the answer.
In Excel, we can extract a myriad of answers using formulae. The benefit of using a pivot table, however, is data can be readily queried for ad hoc inquiries quickly, from different angles and at varying levels of detail. Pivot tables are like x-ray glasses. But there’s more than just fact-gathering. Pivot tables are also a forensics tool.
Pivot tables have led to many oh s#!t moments—both good and bad.
  • A product line is doing better in a region than the client imagined
  • Nonsensical data in important fields
  • Calculations that don’t match up
  • “We have that much activity in that area?”

These kinds of results mean: step away from the data, start knocking on doors and asking questions.

There are many a tool that can help dig into data sets. The power of Pivot Tables is their quickness for accessing info that you don’t need on a regular basis.

One downside to Pivot Tables: they dramatically increase file size. If you’re dealing with a massive load of data and have graphics in a spreadsheet, be warned about saving the document with a live Pivot Table or multiple live Pivot Tables.

Otherwise, get to know your Pivot Tables. They will help you keep your data clean.

For those with Pivot table experience, what else is there to be said? How do you use Pivot Tables?

For those with little to no experience, ask any questions or make comments or requests in the comments section below. Let’s hear from you!

NOTE: A friend said he read this post and still wasn’t clear what a Pivot Table is.
So, I attempted to clarify in Pivot Tables Part 2. Thanks for the feedback!

So what? photo credit: dandooo via photo pin cc
X-ray photo credit: slowburn♪ via photo pin cc