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.

pivotgroupnumbers03

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.

subtotalgrouped03

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.

subtotalgrouped01

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.

subtotalgrouped02

Fix Your Pivot Table Macros

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 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.

_________________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.