Excel 2007 : Two pivots tables acting the same

**mcmuney** · 11-30-2010, 03:16 AM

I have two identical pivots on two separate sheets, with unique names; however, when I group one, the other automatically changes as well. Any idea why? I need to group one by days and one by month.

**arthurbr** · 11-30-2010, 03:24 AM

f you create two pivot tables based on the same Excel Table in Excel 2007, when you change the grouping in one pivot table, the same grouping appears in the other pivot table.

Because you created the two pivot tables from the same source data, by default they use the same pivot cache, the grouped items are the same in both tables.

To use different grouping in each pivot table, you'll need to create a separate pivot cache for each pivot table.

Use the following 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.

(from http://www.contextures.com/xlPivot07.html#Unique)

**DonkeyOte** · 11-30-2010, 03:25 AM

edit: in addition to above...

Quick Fix - create a Named Range (for the source data).
Go to the 2nd pivot and modify the Source such that it uses the new Named Range.
The two should now group independently (different caches - file size will increase)

Originally Posted by arthurbr

If you create two pivot tables based on the same Excel Table in Excel 2007, when you change the grouping in one pivot table, the same grouping appears in the other pivot table.

In terms of avoiding this in XL2007 (and above)
If when you create the pivots you use the old Pivot Table Wizard (ALT + D -> P or add to QAT) as opposed to using Insert -> Pivot Table route you will find you will be prompted as to whether or not you want to share or create separate cache(s) [as appropriate]

**DonkeyOte** · 11-30-2010, 03:35 AM

FWIW, another possible solution [depending on data fields] and certainly less preferable IMO to independent caches...

Initially group both Pivots by Months and Days
Remove Months from the Day based Pivot and Days from the Month based Pivot

You should find that would work also - using only one cache.

(this approach would not work for any non date-orientated grouping - if the Date Field is also used in the Data Field it will not work either)