Pivot Table Subtotal Problem in Excel 2016
If you using grouping, you might run into a pivot table subtotal problem in Excel 2016. There was a change in a recent update, so you might see this problem if you have an Office 365 subscription. I just learned about this issue, and will show you how to fix the problem if it affects your workbooks.
Pivot Item Grouping Problem
After you create a pivot table, you can group the pivot items in one or more of the pivot fields. This feature has been available since the beginning of pivot tables, as far as I know. Learn more about pivot table grouping on my Contextures website.
Grouping Numbers and Dates
If the pivot fields contains numbers or dates, use the Grouping dialog box to group them.
Grouping Text Items
You can group text items too, but you'll have to do that manually – the Grouping dialog box isn't available for text items.
In the screen shot below, I selected Bars and Cookies, then right-clicked on Bars, and clicked Group.
Excel automatically created Group1, and Bars and Cookies are listed under that heading. Also, a new field was created – Category2.
Pivot Table Grouping Change
Thanks to Ian Brown, from Onion Reporting Software Ltd, who let me know about a recent change to grouping. The new behaviour might not affect your work, but Ian's clients had macros crashing, because of the change. And nobody wants unhappy clients.
Fortunately, Ian was able to fix the macros quickly, once he discovered what had happened, and his clients were able to get back to work.
Be prepared, in case you get mysterious reports of macro problems – it might be the same issue.
Grouped Item Subtotals
So, what changed?
In the standalone version of Excel 2016 that I use, the new groups do not show subtotals. If I check the Field Settings for the new field, Subtotals are set to None.
However, in recent builds, the subscription version of Excel 2016 has changed this behaviour. Now, if you group items, the new field settings default to Automatic Subtotals.
Fix the Pivot Table Subtotal Problem
If you have macros that build pivot tables, and group some of the items, your pivot tables will look different now. The grouped items will have subtotals, where they didn't before. That could cause problems further along in your code.
To avoid the pivot table subtotal problem, you can add code that turns off the Automatic subtotals, after you group the pivot items.
There is a detailed explanation in this blog post that I wrote a couple of years ago. In the "too long, didn't read" version, use a line of code to turn Subtotals off:
ActiveSheet.PivotTables("PivotTable1") _ .PivotFields("Category").Subtotals(1) = False
Get the Sample Workbook
To see the full code, and download the sample file, go to the Show and Hide Excel Pivot Items page on my website.
The zipped file is in xlsm format, and enable macros when you open the file, if you want to test the code.
Video: Grouping Text Items in a Pivot Table
Watch this video to see the steps for creating groups from text items. Then, rename a group, or add more items. Later, you can ungroup one or more of the groups.
This video was recorded in a version of Excel that does NOT automatically create subtotals.