Advanced PivotTable Tips & Tricks for Excel Pros
I read an article this morning about how “long tails drive everything”. This essentially means that outliers, or special cases in a data set, are where most of the value is gained/created. The author goes further, saying that tails within tails (or overachieving subsets within overachieving subsets) are where most of that value is gained.
In Excel terms, out of all Excel users, the people who know how to use PivotTables make up the upper tail, or the Excel users who can offer the most value. Within that subset, there are the users who really know how to use PivotTables, with all its hidden features and functionality — these people make up the “tail within the tail”, and are the people who can offer the most value.
While it’s probably a near-impossible task to learn everything about Excel & PivotTables, and so we’re probably all a mile away from joining this revered group of Excel intellectuals, hopefully, these tips & tricks can help you get a bit closer to joining!
I used data sets from Maven Analytics Data Playground to showcase some of these techniques, as well as some data that I had ChatGPT draw up for me.
1. Using Natural Language
This first trick is likely going to be one of those AI things that just blows your mind. I genuinely couldn’t believe this feature the first time I used it, and am still impressed every time I use it.
For this example, I looked at the first 250 rows of data from the Remote Working Survey data set on Maven’s Data Playground, with each row representing one employee. This data includes answers to over 100 questions from the employees about them, their current roles, and their thoughts on remote working.
While we could spend a while building an in-depth pivot table with this much data, wouldn’t it be so much easier if we could just ask a simple question and get an immediate answer?
For example, I wanted to know what percentage of male employees had been in their current roles for over 5 years. Instead of manually building out a Pivot table, I just asked Excel.
On the “Home” tab, click “Analyze Data” and then simply type your question into the box that comes up.
In my example I typed the following: “What percentage of male employees have been in their current roles for more than 5 years?” and received the following output from Excel:
Even better, you can insert that PivotTable with the simple click of a button and then adjust it as you would a normal PivotTable in case the output is not 100% what you wanted.
2. Show Report Filter Pages
For this one, you need to have an existing PivotTable set up and ready to use, with at least one filter on the data. Importantly, this filter must be ready to be used but not currently in use when you want to show report filter pages.
I’ll use the PivotTable from the above example, by simply clicking “Insert PivotTable” so I am working with the below.
While this is a useful table, having to adjust the filter from “Male” to “Female” to “Prefer not to Say” every time I want to view the different subsets can get tedious, especially when working with large amounts of data for which there are multiple subsets to filter through.
Rather than waste time doing this, PivotTables have the “Show Report Filter Pages” function, which will create a separate PivotTable for each filter category. For example, it will show the breakdown of employees by time in their current role (as above) for males in one table, females in one table, and those who prefer not to say in another table.
Simply click a cell in the existing PivotTable, go to the “PivotTable Analyze” tab, click the drop-down arrow underneath PivotTable, and then “Show Report Filter Pages”.
This will then generate separate PivotTables for each of the filters applied to your original table. In this example, I get the following three PivotTables, all on their own sheets.
A good point to remember when using this feature is that you need to clear all filters on the original table before creating your filter report pages. If this does not work!
3. Building Data Models
Creating pivot tables using Data Models is very similar to using JOINs in SQL, allowing you to analyse two different tables together based on a common column. (A “key”.)
To use this method, you need two separate tables in your workbook that share this key. Joining the data together should also make sense/be helpful, which is where your data analysis knowledge will come in handy!
Some tables may share a common column but add no real value when joined together. Consider the following two tables:
While both tables have a common key — Person ID — there is no clear value in analysing these two tables together. You could look at what size shoe the average person who ordered oranges has, but I’d guess there are limited situations where that knowledge is useful!
Once you have the two tables you’d like to analyse together, you can get started setting up the data model. I’ll use the following two tables which ChatGPT created for me to use in this example:
As a first step, I like to give each table a more specific name than the “TableX” default that Excel applies. This helps to make this process more straightforward and easy for others to keep track of. To do this, click any cell in the table, go to the “Table Design” tab, and on the far left of the ribbon you’ll see the Table Name box that you change to anything you’d like. (Remember, this has to be in snake case.)
The next step is to go to the “Data” tab, click “Relationships”, and then “New”. Select the first table as “Table” and the second table as “Related Table”, and the common key as both the “Column (Foreign)” and the “Related Column (Primary)” and then click OK.
That’s the complicated part of this process done — and it was pretty straightforward!
After this, all you need to do is go to the “Insert” Tab and click on the dropdown arrow below “PivotTable”, and select “From Data Model”.
From here you will be able to create pivot tables as usual but with the added functionality of being able to use data from both of the tables you joined.
As you can see in the below screenshots, I was able to take the Product Name, Category, and Price from the Product_Details table and combine these with the total Sales Amount for each product from the Sales Data table to create the following PivotTable.
This allowed me to easily see the prices and sales of the products that make up each category. From here, category and sales analysis can be carried out to make influential business decisions!
Using these techniques can definitely save you some time and enhance your analysis skills considerably so be sure to give them a try — hopefully, you’ll be on your way to being part of that long tail soon enough!
