When you’re building a pivot table, if you add fields to the Values area, Excel automatically adds “Sum of” or “Count of” to the start of the field name. You can manually remove that text, or use macros to quickly change the headings. There is one macro example here, and more on my Contextures website.
When you create a pivot table, a default PivotTable Style is automatically applied. You can change to a different style, and you can even create custom pivot table styles. To help you keep track of the styles that you have, here’s a List All Pivot Table Styles macro.
How can you copy a pivot table custom style to a different workbook? There isn’t a built-in way to do that, but there is a workaround solution. There are instructions that worked in Excel 2013, and earlier versions (way back to Excel 2007). Those stopped working, unfortunately, but there’s an easy way to do this in Excel 2016 too.
Congratulations to the USA Women’s Hockey team, who won the Olympic gold medal. They beat our Canadian team, in a hard-fought game that went into overtime, and ended with a shootout. The team rosters were available online, so I used those for a hockey player data analysis, in Excel pivot tables. Is there anything in the player data that shows why the USA team won?
Instead of showing a budget’s forecast, actual and variance data all at once, click a button to view the values one at a time. That makes the report easier to read, and takes less space on the worksheet. See how this technique works in my Budget Reporter with value selector workbook.
If you try to group pivot table items in Excel, you might get an error message that says, “Cannot group that selection.” For older versions of Excel, if you had a problem grouping pivot table items, it was usually caused by blank cells, or text in number/date fields. For Excel 2013 and later, there’s another thing that can prevent you from grouping.
To see specific data in an Excel Table, you can select an item from the drop down filter in a column heading. Someone asked me if there was a way to scroll through the items, instead of opening the filter list each time. This technique uses a pivot table, which could be hidden on a different sheet, and a spin button, to go up or down in the list of items.
We’re still recovering from Tuesday’s Spreadsheet Day celebrations, so we’ll keep it simple today. Here’s a quick trick to add or move pivot table fields, just by typing. Watch the really short video, and there are written instructions below, if you prefer those.
If you need to make a quick report, Excel has a built-in command that makes multiple copies of a pivot table – one for each item in a Report Filter. See how that feature works, its limitations, and a macro that will copy the entire sheet, not just the pivot table.
As you know, pivot tables are a quick and easy way to summarize a huge table of data in Excel. With just a few clicks, you can create totals for thousands of rows of data, without writing a single formula. Then, with filters and Slicers, zero in on specific parts of the data. But there’s an underused feature that you might be missing – do you know how to compare pivot table values as percentages?