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.
ONE IMMEDIATE QUESTION COMES UP:
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?
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:
- 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.
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
Trackbacks/Pingbacks