In 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.
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.
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:
- Cut the second pivot table, and paste it into a new workbook.
- Change the grouping of the second pivot table.
- 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.
________________
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.
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
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
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
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
Thank you so much Teylyn. It works in Office 2016.
Great solution – thanks for sharing!
Thanks teylyn, the grouping problem can be avoided if you remember to set up the second pivot table that way.
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.
thank you for this solved a yet another excel problem for me!
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… 🙁
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?
This is microsoft’s reply
https://support.office.com/en-us/article/Unshare-a-data-cache-between-PivotTable-reports-87188806-0c24-4d17-b2f7-9e3a4a05542b
You can also work around the issue by creating a dummy column in your data and adding it to your data selection for the 2nd pivot table so that the data sets don’t match.
Thank you Jessica!