Glide Training

How to change PivotTable grouping in one table without affecting another table


Written by Jane Hames

This post is aimed at those who already use PivotTables (as many of my trainees do!).

PivotTable grouping is easy enough to do, but it could cause a lot of frustation if you want to change the grouping in one table without changing the way that field is grouped in another table.

pivot table grouping

The image above shows my two PivotTables and in both, the individual dates have been grouped by month, using the Group Field feature.

pivot table grouping

If I change the grouping from months to quarters in one PivotTable, it will also change in the other. This is because they are based on the same source data and therefore share the same cache.

pivot table grouping

“HOW DO I GET IT TO WORK?” I hear you cry! You just need to do this:

  1. Highlight the area of cells containing your second PivotTable and cut it (Ctrl X).
  2. pivot table grouping

  3. Go to a new workbook and paste it in (Ctrl V).
  4. Change the grouping style on the second PivotTable that is now in a new workbook.
  5. pivot table grouping

  6. Cut this second PivotTable again from the new workbook and paste it into the original workbook.

Both PivotTables are based on separate caches now, which means you can change the grouping in one PivotTable without affecting the other.

pivot table grouping

NOTE: This will increase the file size as we have double the amount of data due to the separate caches. As always, there are several ways to get to an end result in Excel, I find this method is very quick and easy (when you know how!).

If you or your team would benefit from being briliant at PivotTables, please do get in touch.

previous post: How to get even more than training from your training!

next post: Using the Details View in Microsoft Project