What is Pivot Cache?
Pivot Cache is something that automatically gets generated when you create a Pivot Table. It is an object that holds a replica of the data source. While you can’t see it, it is a part of the workbook and is connected to the Pivot Table. When you make any changes in the Pivot Table, it does not use the data source, rather it uses the Pivot Cache. The reason a pivot cache gets generated is to optimize the pivot table functioning. Even when you have thousands of rows of data, a pivot table is super fast in summarizing it. You can drag and drop items in the rows/columns/values/filters boxes and it will instantly update the results. Pivot Cache enables this fast functioning of a pivot table. While you think that you are directly linked to the source data, in reality, you access the pivot cache (and not the source data) when you make changes in the pivot table. This is also the reason you need to refresh the pivot table to reflect any changes made in the data set.
Pivot Cache Side Effects
One downside of pivot cache is that it increases the size of your workbook. Since it’s a replica of the source data, when you create a pivot table, a copy of that data gets stored in the Pivot Cache. When you use large data sets to create a pivot table, the workbook file size increases significantly.
Sharing the Pivot Cache
From Excel 2007 onwards, if you already have a pivot table, and you create an additional pivot table using the same source data, Excel automatically shares the pivot cache (which means that both the pivot tables use the same pivot cache). This is helpful as it avoids pivot cache duplication and in turn, results in less memory usage and reduced file size. Limitations of Shared Pivot Cache While a shared pivot cache improves pivot table functioning and memory usage, it suffers from the following limitations:
When you refresh one pivot table, all the pivot tables linked to the same cache gets refreshed. When you group fields in one of the pivot tables, it is applied to all the pivot tables using the same pivot cache. For example, if you group dates by months, this change will be reflected in all the pivot tables. When you insert a calculated field/item in one of the Pivot Table, it shows up in all the pivot tables that are sharing the pivot cache.
The way around these limitations is to force Excel to create separate pivot cache for different pivot tables (while using the same data source). Note: If you are using different data sources for different pivot tables, Excel would automatically generate separate Pivot Caches for it.
Creating Duplicate Pivot Cache (with the same Data Source)
Here are 3 ways to create duplicate pivot cache while creating pivot tables from the same data source: #1 Using Different Table Names
Click anywhere in the data source and go to Insert –> Table (or you can use the keyboard shortcut – Control + T). In the Create Table dialogue box, click OK. It will create a Table with the name Table1. With any cell selected in the table, Go to Insert –> Pivot Table. In the Create Pivot Table dialogue box, you would notice that in the Table/Range field has the name of the table. Click OK. This will create the first pivot table. Go to the data source (table), select any cell and Go to Table Tools Design –> Tools –> Convert to Range. It will show a prompt asking if you want to convert Table to Normal Range. Click on Yes. This will convert the table into regular tabular data.
Now repeat the steps above, and just change the Table Name (from Table1 to Table2 or whatever you want). You can change it by entering the name in the field below Table Name in the Table Tools Design tab.
Although both the tables (Table1 and Table2) refer to the same data source, this method ensures that two separate pivot caches are generated for each table. #2 Using Old Pivot Table Wizard Use these steps when you want to create an additional pivot table with a separate pivot cache while using the same data source.
Select any cell in the data and press ALT + D + P. This will open the Pivot Table and Pivot Chart Wizard. In Step 1 of 3, click on Next. In Step 2 of 3, make sure that the data range is correct and click on Next. Excel shows a prompt that essentially says click on Yes to create a shared pivot cache and No to create a separate pivot cache. Click No. In Step 3 of the Wizard, select if you want the Pivot table in a new worksheet or the same worksheet and then click on Finish.
Note: Make sure the data is not an Excel table.
Count the Number of Pivot Caches
You may want to count the number of pivot caches just to avoid multiple pivot caches from the same data source. Here is a quick way to count it:
Press ALT + F11 to open the VB Editor (or go to Developer tab –> Visual Basic). In the Visual Basic Editor Menu, click on View and select Immediate Window (or press Control + G). This will make the Immediate Window visible. In the Immediate Window, paste the following code and press Enter: ?ActiveWorkbook.PivotCaches.Count
It will instantly show the number of Pivot Caches in the workbook.
Improving Performance while Working with Pivot Tables
There are a couple of things you can do to improve the performance of workbooks (file size and memory usage) while you work with Pivot Tables: #1 Delete the Source Data You can delete the source data and use the Pivot Cache only. You will still be able to do everything using the pivot cache as it holds a snapshot of the original data. But since you have deleted the source data, your workbook file size would reduce.
In case you want to get back the source data, simply double-click on the intersection of Grand Totals for that pivot table. It will create a new worksheet and show all the data used to create that pivot table.
#2 Don’t Save the Data in Pivot Cache When you save a file with a pivot table and source data, it also saves the pivot cache that has a copy of the source data. This means that you are saving the source data in two places: in the worksheet that has the data and in the pivot cache. There is an option to not save the data in the cache and close it. This will lead a lower file size. To do this:
Select any cell in the Pivot Table. Go to Analyze –> Pivot Table –> Options. In the Pivot Table Options dialogue box, go to the Data Tab. Uncheck the Option – Save Source Data with File. Check the option – Refresh Data when opening the file. If you do not check this option, when you open the Excel Workbook, it will not refresh the data and you will not be able to use the Pivot Table functionalities. To make it work, you will have to manually refresh the pivot table.
When you do this, Excel will not save the data in the pivot cache, but it will refresh it when you open the Excel workbook the next time. Your data can be in the same workbook, some other workbook, or an external database. When you open the file, it refreshes the data and Pivot Cache is recreated. While this may lead to lower file size, it can take a bit longer to open the file (as Excel recreates the cache). Note: If you use this option, make sure you have the data source intact. If you delete the source data (from the workbook or any external data source), then you will not be able to recreate the pivot cache. #3 Sharing the Pivot Cache for better performance If by accident (or intentionally) you end up in a situation when you have duplicate pivot cache and you want to delete the duplicate and share the pivot cache, here are the steps to do it:
Delete one of the Pivot Tables for which you want to delete the cache. To do this, Select the pivot table and go to Home –> Clear –> Clear All. Now simply copy the Pivot Table that you want to duplicate and paste it (either in the same worksheet or in a separate worksheet). It is recommended to paste it in separate worksheets so that it does not overlap with the other pivot table when you expand it. Although, I sometimes copy it side by side to compare different views. This copy pasting of the pivot table makes sure that the pivot cache is shared.
Related Readings:
Microsoft Help – Unshare a data cache between PivotTable reports.
Other Pivot Table Tutorials You May Like:
Preparing Source Data For Pivot Table. How to Group Dates in Pivot Tables in Excel. How to Group Numbers in Pivot Table in Excel. How to Refresh Pivot Table in Excel. Using Slicers in Excel Pivot Table. How to Add and Use an Excel Pivot Table Calculated Field. How to Apply Conditional Formatting in a Pivot Table in Excel.
