Are you lost with pivot tables in Excel?
A few months into my first job, once I got a bit more comfortable with Excel, I began to notice that almost all of the financial summaries that were being presented were in a strange Excel format that I later learned was a pivot table. Pivot tables were not an intuitive concept for me, and I was struggling to understand how they work or when to use them. As I became part of higher-level conversations, it was clear that the ability to use pivot tables was not optional, it was required.
I needed to learn pivot tables quickly, or I would miss out on opportunities to move up or be a part of higher-level meetings.
In order to understand pivot tables, I had to get a few things figured out:
- I needed to understand why pivot tables were used
- I needed to understand how to use pivot tables
- And I needed to figure out what overdelivering looked like with pivot tables
If this sounds like a familiar situation for you, or you just want to boost your pivot table skills, let me save you some time and give you the recipe that allowed me to become a pivot table expert.
Tip #1: I needed to understand why pivot tables were used
I didn’t understand why pivot tables were so much better than manipulating the raw data in a range or table.
The key to understanding the importance of a pivot table boiled down to a few features:
Ability to summarize data extremely fast
- After highlighting the data and inserting a pivot table, you can drag whatever category you want to summarize to the rows and whatever values you want to be summarized into the values section. Doing this exercise takes about 15 seconds. Manually, this exercise would either involve adding a subtotal to the top of the raw data and manually filtering to the categories you want to be summarized or building a separate table with the categories you want to be summarized and using a formula like SUMIF to pull in the data.
Ability to change the way data is summarized instantly
- This is the true magic of a pivot table. If you’re presenting data in a meeting or with a customer and someone asks, “What do our sales look like by product type instead of brand?”. With a standard table or a range of data, you’re screwed and you’d probably respond with “I’ll get back to you on that” or “give me 3 hours…”. But, with a pivot table, you simply remove brand from the rows and add in product type. 1 second. If the question turns into “Well what do the brands look like under X product type”. Easy. Add brand under product type in the rows and hit the “+” — expand — button on product type and that’s it.
Having finally grasped pivot tables, I understood why they were so widely used and so important to learn.
Tip #2: I needed to understand how to use pivot tables
My job not only wanted me to understand pivot tables, but they also wanted me to be able to use them (quickly).
The intimidation with pivot tables usually comes from not knowing where to put fields in the field list. The field list has the list of available fields (the columns from your data), Filters, Columns, Rows, and Values. Let’s break each down:
Filters
- In the filters section, you typically put data that you would not want to see summarized. Typically, this type of data only has a few different values. (Ex: “Include in dataset (Yes/No) column”, “Includes Rebate (Yes/No)”, “Standard Order or Credit”).
Columns
- The columns section is typically used for fields that have a smaller number of comparison-type items (Ex: Years/Quarters/Months for comparison between Prior Year / Current Year / forecast)
Rows
- The rows section, along with the values section, is going to get the most use. This is where you put all of the categories that you want to compare. Usually, you’d want to organize the rows section with the highest-level data (Ex: Customer) at the top and the lowest-level data (item number) at the bottom.
Values
- This section is only for the actual hard number data. This is the actual units, sales dollars, and/or margin dollars that you want to compare against different categories (rows) and/or timeframes (columns).
Being able to manipulate pivot tables quickly is a superpower and a huge timesaver.
Tip #3: And I needed to figure out what overdelivering looked like with pivot tables
Understanding and being able to manipulate pivot tables quickly will get you a B or a B+, but how do you get an A or an A+?
Like in my previous post on overdelivering in base Excel, I believe the same rule applies to pivot tables — formatting and storytelling:
Formatting
- Values: Units (Number format with no decimal places), Dollars (Currency or Accounting format with no decimal places), and Percentages (Percentage format with either 1 or 2 decimal places)
- Wrapped text header cells
- Centered text header cells (I prefer middle for horizontal & vertical),
- (Optional) borders on header cells
Storytelling
- Order — The order of the columns is the most important part of storytelling. As colleagues/bosses/customers/ read your summary from left to right, is the order clearly telling a story? Do you have revenue jammed in-between customer item and invoice number or do you show revenue, cost, and profit margin? Do you have the item number, brand, and then customer or do you have the customer, brand, and item number (highest to lowest level of detail)?
- Intentional Colors — Just adding colors can look nice, but adding colors that have a purpose is part of over-delivering. If every column is the same color, everything seems to be of the same importance — internal item number is of the same importance as the profit margin. If instead categories like item number, brand, and customer are a dull color and revenue, cost, and margin are bright colors, the readers’ eyes jump to the revenue, cost, and margin.
- Renamed Columns for Clarification — This may seem trivial, but having complete clarity in your column names saves time everywhere (emails, meetings, etc…) and it helps tell a better story (Ex: custItemNum should be renamed to “Customer Item Number”).
By doing these 3 things, I became an expert with Pivot Tables.
Continue to notice the people or ideas that raise the bar and aim to raise it above them.