Excel Pivot Cache Explained | MyExcelOnline

Whenever you make a change to your dataset (updating data, adding more rows or columns), you have to manually refresh your Pivot Table to have your changes reflected. Why is that?

I’m going to explain to you the concept of the Pivot Cache. Join me in understanding this concept with some quick illustrative examples below!

 

Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK

 

What is Pivot Cache?

STEP 1: Why is there a need to refresh the Pivot Table? Let us look at this sequence of events below:

  • You invoke the creation of the Pivot Table
  • Then a snapshot of that data in that point in time is stored in the Cache
  • The Cache content is the basis of how the Pivot Table is created

You do not see the Pivot Cache as this runs in the background.

pivot cache

STEP 2: Now what happens when you update the source data?

  • Your source data is updated
  • You invoke the refreshing of the Pivot Table
  • Then a snapshot of that data in that point in time is stored in the Cache
  • The Pivot Table is updated based on the Cache

Excel Pivot Cache Explained | MyExcelOnline

When you create Pivot Table in Excel, a Pivot Cache is created automatically. It is a replica of the original source data but it is not visible to us. When you make changes to the Pivot Table, it uses Pivot Cache and not the original source data.

Even though this makes Excel very responsive and quick to change, it increases the file size to almost double.

 

Sharing a Pivot Cache

When you create multiple Pivot Tables using the same source data in a workbook, Excel creates a single Pivot Cache and re-uses it for all Pivot Tables.

The advantage of sharing a Pivot cache is that it prevents duplication but it creates several disadvantages as well:

  • When you refresh one Pivot Table, all other Pivot Tables get refreshed.
  • When you group one field in Pivot Table, it gets grouped in other Pivot Tables.
  • When you insert a calculated field in Pivot Table, it appears on all other Pivot Tables.

If these disadvantages create a hindrance for you, you can simply create multiple Pivot caches for multiple Pivot Tables. To do so, follow the steps below:

STEP 1: Click anywhere on the data source and press Ctrl + T to create a table. Click OK.

Excel Pivot Cache Explained | MyExcelOnline

STEP 2: Go to the Table Design tab, you will see the Table Name box displaying the name (Table1) assigned to the table.

Excel Pivot Cache Explained | MyExcelOnline

STEP 3: Select Summarize with PivotTable.

Excel Pivot Cache Explained | MyExcelOnline

STEP 4: In the PivotTable dialog box, click OK.

Excel Pivot Cache Explained | MyExcelOnline

Pivot Table and its cache will be created. Before you create another PivotTable, you need to covert the table back to range.

Excel Pivot Cache Explained | MyExcelOnline

STEP 5: Go to Table Design Tab > Convert to Range.

Excel Pivot Cache Explained | MyExcelOnline

STEP 6: Again convert the range back to a table.

Excel Pivot Cache Explained | MyExcelOnline

Excel will assign a different name to the table – Table2.

Excel Pivot Cache Explained | MyExcelOnline

Now you can create another Pivot Table using the data from Table2.

Since the tables have different names, Excel will create separate caches for them!

 

Count the Pivot Cache in Workbook

How to do how many pivot caches are present in your workbook? Excel can count them for you using a simple VBA code.

STEP 1: Press Alt + F11 to open the Visual Basic window.

Excel Pivot Cache Explained | MyExcelOnline

STEP 2: Press Ctrl + G to open the Immediate window.Excel Pivot Cache Explained | MyExcelOnline

STEP 3: Enter the following code:

?ActiveWorkbook.PivotCaches.Count

Excel Pivot Cache Explained | MyExcelOnline

STEP 4: Press Enter.

The result will be displayed below the code!

Excel Pivot Cache Explained | MyExcelOnline

 

Reduce Excel File Size

The only issue that you face while using Pivot cache is that it doubles the size of the Excel file. This problem can easily be solved by deleting the original source data. This will reduce the file size significantly!

The Pivot Table will remain intact and you can easily use it.

If you want to get the source data back, double click on the grand total value in the unfiltered Pivot Table.

Excel Pivot Cache Explained | MyExcelOnline

This will paste the original source data into a new worksheet for you.

Excel Pivot Cache Explained | MyExcelOnline

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 101 Best Excel Tips & Tricks:

Excel Pivot Cache Explained | MyExcelOnline

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn