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.
Best Answer
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: