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.

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.

enter image description here

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:

enter image description here

  • Thanks for your reply. The pivot tables did have separate caches for several months, but some day the days and weeks tables started sharing their cache. I tried to launch the wizard using your keyboard shortcut (I have Office 2010), but unfortunately my locale is set to French, so the keyboard shortcut doesn't work. I'll try and find the correct shortcut. – Benoit Duffez May 7 '13 at 8:29
  • I added a screenshot of where the command is in the English Excel 2003 menu. Maybe that'll help. – teylyn May 7 '13 at 8:35
  • Try Alt - D - R since in French Excel the menu command is Donnees > Rapport de tableau croisee dynamique. – teylyn May 7 '13 at 8:54

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.