Grouping a field in one pivot tables groups the same field in another table

Question

I've got a data sheet, which basically contains an amount and a date.

I've created several pivot tables, each on one sheet. I have given names to those tables, such as "tab_days", "tab_weeks", "tab_months", etc. Each table groups the data by the date. The days table groups the amount for every day; the weeks table groups for 7 days; the months groups every month, etc.

I have learned that giving names to pivot tables ensures that their cache and calculations are made independent of every other pivot table in the spreadsheet.

However, one day, I have no idea why, the days table and the weeks tables started sharing the same grouping parameters for the date. That means that if I change the date grouping on the "tab_days" pivot table to every 1 day, the weeks table gets grouped by 1 day. Then I change the "tab_weeks" to group the data every 7 days; back on the "tab_days" data is grouped by 7 days.

How can I clear the link between those two tables? I'd like to have the two tables independent as they were a few weeks ago.

Answer 1

By default, Pivot tables share the same pivot cache. If you want two pivot tables with different groupings, you need to make sure that you create two different pivot caches. These will take up more memory, which is why Excel defaults to using one pivot cache.

In Excel 2003 pivot tables were created with the Pivot Table Wizard. This Wizard contains a question in the first step that only appears when you create more than one pivot table. If you keep that option unselected, Excel will create a new pivot cache after issuing a warning.

In Excel 2007 or later, the pivot table wizard is not normally used, so this option is not readily available.

But here's the good news: You can open the Pivot Table Wizard with the keyboard shortcut Alt - D - P. Alternatively, you can add it to a custom group on the ribbon or put it in the QAT. It's in the "Commands not in the ribbon" list.

The keyboard shortcut comes from the Excel 2003 menu bars, Alt - D to open the Data menu, then P to select the Pivot chart wizard:

Answer 2

In Excel 2010 there is a much much better way, that doesn't make your file bigger by creating so many more caches:

1. Highlight the field in the pivot table that you do not want to change when other pivot tables are changed (or to change others)
2. Go to pivot tools
3. Under the "active field" heading it will tell you which field you are about to change, and if you want the whole pivot table to react or not, choose the highest level field (usually the column names you are using)
4. Click on Field settings in the tools ribbon (it is just under the active field heading)
5. Check or uncheck the "include new items in manual filter" radio button
6. Click OK