Excel Pivot Cache

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

Note: This does not apply to Power Pivot PivotTables.

When you create a PivotTable Excel takes a copy of the source data and stores it in the Pivot Cache. The Pivot Cache is held in Excel’s memory. You can’t see it but that’s the data the PivotTable references when you build your PivotTable, or change a Slicer selection or move rows/columns around.

This enables Excel to be very responsive to changes in the PivotTable but it can also double the size of your file. After all, the Pivot Cache is just a duplicate of your source data so it makes sense that your file size will (potentially) double.

While PivotTables in Excel 2007 onwards have become more efficient at re-using the same Pivot Cache when you build PivotTables referencing the same source data, you can still accidently create multiple Pivot Caches which can quickly blowout your file size.

Avoid Duplicating Pivot Caches

Although it’s more difficult to accidentally create duplicate Pivot Caches nowadays, I still like to create additional PivotTables (that reference the same data source) using the Copy & Paste technique.

That is you copy and paste an existing PivotTable and then edit the pasted one according to your needs, as opposed to selecting the source data and inserting a new PivotTable.

Tip: Preferably paste your new PivotTable on a different sheet to avoid them running over one another when the PivotTables expand.

Detecting the Number of Pivot Caches

To see how many Pivot Caches there are in a workbook open the VB Editor (ALT+F11), then open the Immediate window of the VB Editor; View Menu > Immediate Window (or CTRL+G):

Then type the following in the Immediate window:

?ActiveWorkbook.PivotCaches.Count

Press Enter to see the count result:

If you only have one data source and you have more than one Pivot Cache then your file size will be bigger than necessary.

Another side effect is you won’t be able to control both PivotTables with the same Slicers. Slicers can only control PivotTables which share the same Pivot Cache.

Notes:

1. The Pivot Cache for a PivotTable connected to an Online Analytical Processing (OLAP) data source cannot be shared because it is used in a different way than non-OLAP data sources.
2. To be absolutely clear, if you have two separate data sources then they cannot share a cache.

Removing Duplicate Pivot Caches

So, you’ve found you have duplicate caches and now you want to remove them. One way is to delete the offending PivotTable and build it again using the Copy and Paste method above.

Alternatively, you can use this macro to eliminate them. It comes with warnings though, so be careful!

Excel Pivot Cache Limitations

Ideally, we want to have as few caches in our file as possible to keep the file size small, however this means putting up with the following restrictions:

1. Any Grouping applied to dates or other fields is applied in all PivotTables which share the cache
2. Calculated fields are available in all PivotTables that share the cache
3. Refreshing one PivotTable refreshes all PivotTables which share the cache

Intentionally Duplicating Pivot Caches

If you don’t like the limitations above then you will want multiple Pivot Caches even though you’re using the same source data.

The easiest way to create a new Pivot Cache is to temporarily change the source data range so that it’s just one row bigger than the actual range you want. To change the data source simply select a cell in the PivotTable > PivotTable Tools tab: Options (Excel 2007/2010), or Analyze (Excel 2013/2016):

You can then change it back to the correct range and you will still have two separate caches.

Alternatively, you can create a PivotTable using the PivotTable Wizard as this will automatically create a new cache. To start the Wizard first select your source data > ALT + D + P and follow the prompts.

Note: If your source data is referencing an external data source then you will need to create a unique data connection for each PivotTable to ensure they don’t share a cache.

Reducing the Pivot Cache Overhead

PivotTables were designed to summarise huge amounts of data so sometimes the duplication of data in the Pivot Cache can tip your file over the edge or reasonableness when it comes to size.

If so, there are a few things you can do:

1. Delete the Source Data: If your source data is static, i.e. it’s not changing or being updated then you can delete it. Yes, you can delete the source data that occupies a worksheet in your file. It’s already in the Pivot cache so you don’t also need it in the workbook.

   You can still filter and edit the PivotTable because it will use the data in the Pivot cache. You just can’t refresh it as the link to the source is broken once you delete it.

   If you want to get the source data back then all you need to do is double click on the Grand Total cell in your (unfiltered) PivotTable and it will dump the source data in another sheet.

   

2. Put your source data in another Excel file: use the RefEdit icon in the ‘Create PivotTable’ dialog box (image below ²) to navigate to your external workbook and select the range.



3. Reference an external database: if your source data is in an external database like Access or SQL then you can use the ‘Use an external data source’ option in the Create PivotTable dialog box (see image below³) to navigate to your query. This way you're not also housing your source data in a worksheet in your Excel file:

   

   For extra points make sure you only import the data you need by writing a query that filters out unnecessary columns etc.

4. Uncheck 'Save source data when opening the file': if your data is in an external file or database then unchecking this option will not save any data to the Pivot Cache. Make sure you also check the 'Refresh data when opening the file' option so you always have up to date data displayed in your PivotTable.

   

   Caution: don't use this if your source data was in your Excel file and you've since removed is as suggested in tip 1 above. You must retain a connection to the original source of the data, otherwise your PivotTable becomes nothing more than a static report. All ability to update or pivot it further will be lost.

Please Share

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.