Pivot Table Grouping Affects Another Pivot Table

pivotgroupcache00In Excel 2007, when you create a second pivot table from the same source data, you don’t get an option to base the new pivot table on an existing pivot table, the way you can in Excel 2003.

In Step 1 of the Excel 2003 Wizard, you can select the first option, to create an independent pivot table. Or, select the fourth option, for pivot tables that share the same pivot cache.

pivotgroupcache02

In Excel 2007, if you create two pivot tables from the same source data, they automatically use the same pivot cache of the source data. You don’t have an option to create an independent pivot table.

Grouping Problems

Because the pivot tables share the same cache, this can cause problems if you group the data in the pivot fields.

When you change the grouping in one pivot table, the same grouping appears in the other pivot table. For example, change the date grouping in the first pivot table to Months, and the dates in the second pivot table automatically group in Months.

Because you created the two pivot tables from the same source data, by default they use the same pivot cache, which is where the grouping is stored.

However, you might want different grouping in the two pivot tables. For example, you’d like Month grouping in one pivot table, and Quarter grouping in the other.

pivotgroupcache01

Create a Second Pivot Cache

To use different grouping in each pivot table, you’ll need to create a separate pivot cache for each pivot table. Use the following easy method, suggested in the Excel newsgroups by Dave Peterson.

To create a separate pivot cache for the second pivot table:

  1. Cut the second pivot table, and paste it into a new workbook.
  2. Change the grouping of the second pivot table.
  3. Cut the second pivot table from the new workbook, and paste it back into the original workbook.

Now there are two pivot caches in the original workbook, and each pivot table can be grouped independently.

Watch the Video

To see the steps for creating a second pivot cache so you can create separate grouping in the pivot tables, please watch this Excel video tutorial.

________________

This entry was posted in Group and Total. Bookmark the permalink.

12 Responses to Pivot Table Grouping Affects Another Pivot Table

  1. Jeff Weir says:

    One downside worth mentioning is that you have just doubled the memory requirement of this particular set of data. In this case, it might make sense to deselect the “save source data with file” option, provided that either the source data in the workbook, or on a network drive that excel can access using the “external data” functionality. That way, both pivottables will be recreated from scratch, rather than stored with their duplicate pivot caches.

  2. Gaetan says:

    Hi,
    In 2007/2010, is there a way to have the same results (i.e. independent pivotable) using VBA, but without using groups?
    Thanks
    Gaetan

  3. Gaetan says:

    It seems this solution works, but if you have a better solution (i.e. without refreshing), I would appreciate :

    For Each pt In ActiveSheet.PivotTables
    pt.SaveData = False
    pt.PivotCache.Refresh
    pt.SaveData = True
    Next

    oddly enough, if I manually change the options (savedata), it works, but if I use the recorded macro, it does not. That’s why i had to add the refresh…

    Thanks

    Gaetan

  4. teylyn says:

    Why not just use the Pivot Table Wizard? Although it’s not in the ribbon, it can be added to the QAT or brought up with Alt – D – P and by stepping through it, the user will be asked whether or not the new pivot table should be based on the same dataset. No VBA, no copy and paste, just a few clicks.

    cheers, teylyn

    • Ondrej says:

      Copy out and back in really does good job while I use reference to Excel Table and need unique pivot table groups.

      Teylyn, but seems doesn’t work for new excel Tables. Only 2003’s ranges. Therefor useless for me.
      –MS Excel 2010

    • Prakash Thirumeni says:

      Thank you so much Teylyn. It works in Office 2016.

  5. Thanks teylyn, the grouping problem can be avoided if you remember to set up the second pivot table that way.

  6. Rich says:

    A simpler solution to the one provided is to define a unique range name for each pivot table to use. I typically define a named range for each pivot table even though they all use the exact same data range. This forces each pivot table to have their own cache eliminating the problem of a grouping in one pivot table affecting another.

  7. aukseo says:

    thank you for this solved a yet another excel problem for me!

  8. Tamara says:

    I thought you were on to something! I used to do this in 2010. It’s not working in office 365 / 2016. Anyone have any luck? I group and ungroup frequently and ALL my pivots are following. The “create new data cache” instructions for new pivot tables also do not work, and are for 2007… 🙁

  9. Judy says:

    Hi
    I’ve got the same problem as I copied an existing pivot table. I need to have two different groups, but both were updating once I changed the other. I need to make sure they are linked together for my slicer to work, so I cannot change the source data.
    Is there a way to not update the other pivot table?

Leave a Reply

Your email address will not be published. Required fields are marked *