Polish Your PivotTables with These Excellent Tips
PivotTables in Excel are pretty awesome. In just a few clicks, you can build a detailed report that gives you quick and important insights into your data.
But creating PivotTables is only one part of being amazing at using these reports. Efficiently polishing and fine-tuning your PivotTables are skills that are just as important!
Spending the time to make your reports just a little bit easier to review & interpret is a massively useful skill to have.
The whole reason why you went through all that hard analytical work is so that others can look at your summarised results, and make a decision or have an opinion about the data you used.
So if you polish your PivotTables in the right way, your data and associated reports will be so much more valuable to your friends, colleagues or your manager.
In this post you’ll get a wide range of tips to improve your PivotTables, including various shortcuts and hints that may have never heard about.
- First: Set Up Your Data and Excel Sheet
- Next: Settle On Your Layout
- Finally: Now Go Ahead and Make Your PivotTable Look Pretty
- Bonus Tips: Provide Extra Value to Your Audience
- Your Polished PivotTables Await!
First: Set Up Your Data and Excel Sheet
There’s no point making your PivotTable look fancy and neat if you’ve selected the wrong data to present. You need to be crystal clear on the content that you’re going to display – and these first few tips will make sure you don’t have to re-do any work that will waste time later on.
1. Refresh Your Data Source (to Ensure Your Report is Correct)
Let’s get the obvious ones out of the way: before you apply any kind of formatting, you absolutely need to give yourself a sanity check and refresh the data that is being used for the PivotTable. If you have edited some of the data recently, it’s important to remember that Excel won’t automatically update the PivotTable.
The best way is to right click on any cell in the PivotTable and select Refresh. Or, if you have one of the PivotTable cells selected already, press the function key and hit R:
Alternatively, you might be using a PivotTable which has data added every week or month. If that’s the case, then make sure you select the “Change Data Source” option and update the range of data you want to present. You can do this in two ways:
- Go to the main PivotTable Options menu, and in the Data section select Change Data Source, or
- Use the shortcut Alt-J-T-I-D (ouch…not very easy to remember!)
2. Disable AutoFit Column Widths
This is definitely a tip I wish I knew earlier.
When you start populating a PivotTable by selecting the various fields, the column widths will automatically shrink or widen to fill the data. This is fine if the PivotTable is the only thing on the sheet, but if you’re including it among other pieces of data tables or graphs, it may mess up your formatting.
You can avoid this issue completely by going into the PivotTable Options (Right-click > PivotTable Options…) then unselecting the “Autofit column widths on update” checkbox:
If column widths are important to you, then definitely disable this option before getting to work!
Next: Settle On Your Layout
Now that your data is laid out correctly, you can focus on presenting the right results and making them look good.
Want to improve your Excel presentation skills? Try some of these other articles.
3. Sort Your Data to Answer the Real Questions
This may seem obvious to some, but it’s staggering to see how many PivotTable reports aren’t sorted in an intuitive way.
If you force your audience to find the cost centres which aren’t providing enough sales income in the last few months, then your PivotTable isn’t finished yet.
All you need to do is sort your data so the relevant areas appear at the very top (or very bottom).
It’s a small effort on your part that improves the value of your data.
In Microsoft Excel, sorting your PivotTable is a little different to a normal grid of data. A normal filtered set of data in Excel has sorting options on the column headers in the dropdown box:
But in a PivotTable, this option doesn’t exist. Instead, you’ll need to right-click on a value directly to open up the Sort menu:
The Sort option is also available via the PivotTable menu at the top of the screen (only visible when a PivotTable is selected) – but it’s far quicker to right-click on the specific value you want to sort.
4. How Should Blank Cells Be Presented?
Depending on the size and format of your PivotTable, you might want to display blank cells in a certain way. For numbers, you might want to display “0”, and if you are showing currency values, you might want to display “$0.00”, and so on. You might even want to show some text in those cells (eg: “no value”).
Fortunately, this is easy to update. Right click anywhere on the PivotTable and select “PivotTable Options…”. On the “Layout & Format” tab, you will see the option “For empty cells show:” – tick the checkbox, and put whatever value you like in here.
If you have applied some Conditional Formatting to the PivotTable, you might want to use this option so that empty cells are formatted in a specific way.
5. Change Subtotal Options
If you have two or more Row Labels, subtotals will appear within your PivotTable by default. Depending on your purpose (and personal preference!), you might want to display subtotals differently.
Here are three different versions of the same PivotTable, with different subtotals options selected. The two Row Labels we’ve included are the date, and the type of fruit:
You can also turn subtotals on or off for a given Label by right-clicking on the relevant item and selecting the “Subtotal XXX” checkbox:
If you aren’t sure how to present subtotals, the first question to ask yourself is: “does my audience even need to know what the subtotal values are?”. If they aren’t all that important, then you should remove them completely.
As for whether you should put the subtotals at the top or bottom…it’s totally up to you. Including subtotals at the top of each group saves on space, but having totals at the bottom of each group is what most people would expect to see.
6. Group Dates Together (By Week, Month, Quarter, Year…)
Sometimes it simply isn’t necessary to show day-by-day information in the PivotTable report you’re working on – it might be sufficient to show weekly or monthly totals. If you have a Row or Column Label in date format, you can Group the data together by any date interval you like. This will “roll up” the data to make it easier to compare the values from each period.
To choose your grouping interval, right click on any date and select the Group option:
The next dialog box that will appear will allow you to choose how to summarise your date ranges. You can select multiple options here depending on what you want to present:
To select weeks, ensure that only the “Days” option is selected, and you will be able to change the “Number of days:” option to 7 days.
If you choose multiple Group options, they will both appear in the PivotTable. In the example below I selected Days and Months, and you can see that two levels of dates appear:
If you have a larger date range in your data then make sure you select both options otherwise data from the same day of each month (ie: 1st of January, 1st of February, and 1st of March) will appear in the same cell, which is probably not what you want. If your data spans across multiple years, then you will also want to select Years from the Grouping menu as well.
7. Show Values as a Percentage or Rank
For some PivotTables the actual values themselves might not be all that important – just knowing which category is bigger or smaller might be enough for your audience. Fortunately, you can instantly show any value as a percentage or a rank by using the “Show Values As” option.
Here it is when you right-click on any value in a PivotTable:
The most generally useful options in this list are “% of Grand/Column/Row Total” and “Rank Largest to Smallest”, as they are the quickest and easiest to understand by most people reviewing at the PivotTable.
Here’s my example PivotTable with the “% of Grand Total” option selected:
Adding conditional formatting helps to quickly see where a lot of the “Amount” value comes from. Bananas sold in the East region and Watermelons in the North region had the most spend.
If you want to show both values and percentages in a PivotTable, you can drag the same field into the Values area of the PivotTable Field List again, and select a different “Show Value As” option for each field.
Finally: Now Go Ahead and Make Your PivotTable Look Pretty
8. Apply a Style to Your PivotTable to Align With Your Branding
The standard colour scheme that PivotTables have in Microsoft Excel is fine if you are doing some internal data analysis and don’t plan to present your work to anyone, but it’s a good idea to make it a little more interesting and customised if you plan on showing other people.
Microsoft Excel has plenty of default colour schemes in the PivotTable Design menu at the top of the screen:
Microsoft Excel groups the styles into Light, Medium and Dark palettes and have a wide range of colours, so even if none of the styles are exactly what you want, it should get close enough to what you want to end up with. If you want to create your own custom style to use later, you can Duplicate one of the default styles, and then Modify the details yourself:
Do you spend a lot of time formatting data in Excel? Get lightning-fast with these useful formatting shortcuts.
9. Support Your PivotTable with a PivotChart
Instead of giving your audience a Slicer to easily filter the data to their needs, you may want to graphically assist their understanding by inserting a PivotChart.
The difference between a normal table of data and a PivotTable in Microsoft Excel is exactly the same as the difference between a Chart (a graph) and a PivotChart. You’re just presenting the data in a more visual format!
A PivotChart is similar to a graph/chart that you would normally create in Excel, but it has a very similar menu to a PivotTable – you can drag and drop fields into the same four areas (with some minor differences).
Your audience will be able to understand a bar or line graph fairly quickly, as long as you are selecting the right fields from your data.
I won’t give you too many specifics on how to present charts in Excel, however I would suggest that anything date-related should probably be represented by a continuous line (to show something increasing/decreasing), while if you’re showing separate departments/classes/regions you should probably use a bar graph.
Bonus Tips: Provide Extra Value to Your Audience
10. Analyse Relevant Data in Detail by double-clicking a cell
If you’ve created more than a few PivotTables before, it’s likely that you’ll see the results and say “hmm, that value doesn’t seem quite right”. If you want to investigate the details, you will probably go to your source data and filter the data a few times to review the data that makes up that peculiar value. It’s time-consuming and error-prone.
Instead, just double click on any cell (either a value cell or a row/column header) to instantly see the raw data that relates to that value in the PivotTable.
Back to our well-known fruit sales data example, if I want to see all sales of watermelon in the North region, I would double-click on the cell selected below:
And this is the data that appears in a new sheet in the same Excel workbook:
Of course, the total of the Amount column matches up with the value we selected in the PivotTable ($52.50) so we know we’re looking at the right data.
11. Add Slicers to Enable Quick Filtering
Slicers were introduced in Microsoft Excel 2010 and provide a much faster way to filter between any field in your data.
The Slicer option is in the PivotTable Options menu, after you’ve selected a PivotTable cell in Excel:
After choosing which field (or fields) you want to filter by, a set of buttons will appear on your Excel Sheet; one button for each value. Let’s see what happens if I create a Slicer for the Product field in my sample data:
Initially, all items are selected (ie: all data is contributing to the PivotTable) but clicking on any of the products will filter the PivotTable on that product only. If you selected multiple fields when creating a Slicer, then a separate set of buttons will appear for each field.
If you want to filter on both Oranges and Red apples, you can select multiple items by holding down the CTRL key – just like most aspects of Microsoft Excel.
Clicking the icon at the top right of the Slicer will unfilter the report back to its original state.
Slicers are helpful if you want your intended audience to interact and play around with the data – especially if you want them to compare different values from a specific field like a product line, course subject, or location.
Your Polished PivotTables Await!
There’s plenty of great tips here that you can take advantage of – you’ll never have to present a mediocre PivotTable to your boss ever again.
What was your biggest takeaway from all of these tips? Is there something else you always make sure to do to your PivotTables? Let us know in the comments!