Why should I align my Pivot Caches?

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?

No.

  1. 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.
  1. 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

Happy Pivotting!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.