If Excel is your main play tool, more often than not, you will find yourself analysing data by summarizing it in pivot tables.
It is important to make sure that all the pivot tables based on the same dataset also share the same pivot cache.
But why would I want to do that?
- You are far less likely to omit refreshing a pivot table
There’s nothing more frustrating than subsequently finding out that the numbers you initially provided did not quite add up because one or more of your pivot tables were not refreshed due to differences in caches.
- You save up on file size
True, this will not matter that much if you only have a 1000 rows and 10 columns worth of source data. Should you happen to work with huge datasets, however – i.e. 90+ columns, 30 000+ rows – making sure that all your pivot tables that are fed from the same dataset also use the same pivot cache, can reduce your file size by half.
Isn’t it always the case that if two pivot tables pull data from the same dataset they also have the same pivot cache?
- Every single time you create a pivot table from scratch, you also create a new pivot cache, even if you include the exact same rows and columns.
- If a different number of rows or columns are included in two different pivot tables, then Excel will create two different pivot caches
How do I ensure that all my pivot tables have the same cache then?
Copy, rather than create from scratch