Hi ,

Before this week's blog post, a quick reminder that our 20% Off Sale on all our courses ends on May 18th.

Click the image below to see our course list.

If you are an existing or past student, you can renew or upgrade your courses for 20% off too.

Just login to your account and click the UPGRADE or RENEW buttons beside your courses.

Excel Pivot Cache

Note: This does not apply to Power Pivot aka Data Model 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, change a Slicer selection or move rows/columns around.

Watch the Video

This enables Excel to be very responsive to changes in the PivotTable but it also increases the size of your file. Don't panic though as the Pivot Cache is very good at compressing the data, so your file size shouldn't double. For example, below you can see 3 files and their respective sizes:

1. The first file only contains the data in the worksheet.
2. The second file has the data in the worksheet and loaded to the Pivot Cache.
3. The third file uses Power Query to get the data from an external file and load it to the Pivot Cache.

You can see that the Pivot Cache uses less than 700 KB to store the data vs nearly 3MB when it's stored in the worksheet.

Now, 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 accidentally 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

We have a few options to get around the limitations above.

New PivotTables

Option 1: You can create a new PivotTable using the PivotTable Wizard which will automatically create a new cache. To start the Wizard first select your source data > ALT + D + P and follow the prompts. At the warning you want to select ‘No’ so that Excel creates a new cache.

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.

Option 2: You can insert a new PivotTable by selecting the source data > Insert tab > PivotTable. This PivotTable will share the same cache but it won't automatically inherit the groupings of the previous PivotTables. However, if you later make grouping changes to any PivotTables sharing the same cache, this PivotTable will also become grouped in the same way. See the next section on 'Existing PivotTables' for how to fix them if this happens.

Note: keep in mind that dates are automatically grouped when you add a date field to a PivotTable. You can CTRL+Z to undo this grouping before adding any other fields. This will only undo the grouping for the new PivotTable.

Existing PivotTables

If you have an existing PivotTable that you no longer want grouped the same as the other PivotTables sharing the cache, you can temporarily create a new Pivot Cache by changing 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 > Analyze > increase the range by 1 row:

Note: This will result in ‘blank’ values in your PivotTable, but don't worry as they will go again when you change the source data range back.

Change the grouping to suit your needs, then edit the source data range to remove the extra row. The PivotTable will now share the cache again, AND it will retain the new grouping settings.

Note: if you subsequently change the groupings for any PivotTables sharing the cache, they will be applied to all PivotTables again, so you'll have to repeat the steps above to temporarily change the source data range.

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 of reasonableness when it comes to size.

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

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.

1. Put your source data in another Excel file: use Power Query to bring the data into the Pivot Cache without loading it to the worksheet first.

For extra points make sure you only import the data you need by filtering out unnecessary columns and rows.

• 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 you used Power Query to get your data or if your source data was in your Excel file and you've since removed it 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.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub