Pivot Table Grouping Affects Another Pivot Table

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:

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.

________________