Excel automatically makes a copy of the source data and saves it in the Pivot Cache when you build a PivotTable.
It is a part of the workbook and is linked to the Pivot Table, even though you can’t see it. When you make adjustments to the Pivot Table, it uses the Pivot Cache rather than the data source. Excel stores the Pivot Cache in its memory. When you create a PivotTable, adjust a Slicer selection, or switch rows/columns around, the PivotTable refers to that data in the Pivot cache.
It allows Excel to react quickly to changes in the PivotTable, but it also doubles the file size. Even if you have thousands of rows of data, a pivot table will summarize it quickly. Drag and drop things into the rows/columns etc. to change the results instantly. To show any changes in the data collection, you must refresh the pivot table.
Drawback: The Pivot Cache is simply a copy of your source data, so it’s natural that your file size would double.
Sharing the Pivot Cache :
When you create PivotTables referencing the same source data, PivotTables in Excel 2007 and later have become more effective by reusing the same Pivot Cache. We can say that, for the pivot tables using the same source data, the pivot cache is shared automatically.
This is advantageous because it prevents pivot cache replication, resulting in decreased memory consumption and hence the file size.
Disadvantages of shared Pivot Cache :
- Field grouping is also shared when pivot tables share the same pivot cache. For example: , if you group the products by their price range, this change is reflected in all the pivot tables as well.
- When you refresh one pivot table, it refreshes all the pivot tables that are connected to the same cache.
- If in any one Pivot table, you insert a calculated field, it appears in all the other Pivot Tables that share the pivot cache.
Using Pivot Cache:
For Using the Same Data Source, Creating Separate Caches for PivotTables Using Excel Table follow the below steps :
- Click anywhere in your data source & then use the shortcut key CTRL + T to convert the data to an Excel table( before starting to build a PivotTable). Another way can be, on the Insert tab of the Ribbon, click on the Table button.
- Create Table dialog box will pop up. Click on OK to confirm the data for the table.
- When you build an Excel table, a Design tab appears on the Ribbon. A Table Name box on the far left of the Design tab shows the table name, which is by default Table1.
- Now, on the Ribbon’s Design tab, press the Summarize with PivotTable button to begin the process of developing your PivotTable. As soon as you click on Summarize with PivotTable button, a dialog box for Create Pivot Table will pop up.
Choose the location for the Pivot Table & then click on OK.
- You must now convert your table to a normal range. To do so, firstly select any cell in the data range & then go to the Ribbon’s Design tab and click the Convert to Range button.
- Now you can again newly create a table from the same data source and but it must have a different name – Excel will give it a different name by default, which is most likely Table2. Create the next PivotTable using this new Excel table in the same manner as before.
Excel can consider the Excel table as a separate data source and build a separate cache after you renamed it.
How to get Count of total Pivot Table Caches in a Workbook ?
Follow the below steps to get count of total Pivot Table Caches in a Workbook:
- To begin, press ALT+F11 to launch the Visual Basic Editor. The Immediate window is located in the lower right corner of the screen. Use the shortcut CTRL+G to open it if it isn’t already open.
- In that immediate window, enter the following code :
Count of ActiveWorkbook.PivotCaches
- Then press the ENTER key to get count of total pivot table caches in the workbook.
Reducing the File Size (Increased due to pivot cache) :
Since a PivotTable saves a cached copy of your data, you can remove the original data source and the PivotTable will continue to work. This would greatly reduce the file size.
You can then also recreate your data source using your PivotTable. To do so, in the Values area of the Pivot Table, place a field & leave the other areas empty.
The cached version of the database will be built on a separate sheet if you double-click on the value that this calculates in the PivotTable. (Example: Double-click on Sum of Price/kg in our example)
You will get the sum of all the products even when you deleted the data source.