When you create a PivotTable, Excel makes a copy of your source data and stores it in memory – it creates a cached copy. The PivotTable is then linked to the copy rather than the original data. This duplication of data will inevitably increase the size of your file.
If you have multiple PivotTables in your workbook, all based on the same data source, then they can all share the same cache. In fact, from Excel 2007 and onwards this happens by default.
In earlier versions of Excel, which used the PivotTable wizard, Excel gave the option to share or not to share the cache. This option is not presented to the user in Excel 2007 and later.
So the big benefit of sharing a cache is that it keeps your file size down. The possible downsides, however, are as follows:
- grouping that you apply in one PivotTable (for example on a date field) are automatically applied to other PivotTables. Undo the grouping, and all the PivotTables sharing the cache are affected;
- calculated fields and items are made available to all PivotTables;
- when you refresh the cache all PivotTables are refreshed.
Fortunately, it is still possible to create separate caches (for PivotTables using the same data source) in the newer versions of Excel. Below are two methods which you can use to achieve this.
Using the PivotTable and PivotChart Wizard
The first method is achieved by invoking the legacy PivotTable and PivotChart Wizard rather than using the PivotTable button on the Ribbon. The easiest way to do this is to click anywhere in your data and then use the shortcut ALT+D+P (alternatively, the PivotTable and PivotChart Wizard button still exists and can be added to the Quick Access Toolbar – look under ‘Commands not in the ribbon’).
Click Next on the first step of The PivotTable and PivotChart Wizard.
Confirm your data range by clicking Next in step 2 of the wizard.
What happens next depends very much on whether your data source is in an Excel Table or not (an Excel table is created by using the Insert | Table command). If your data is not in an Excel table and the data source is already cached by an existing PivotTable, you will be given the option to either share or not share.
Click No to create a separate cache for your new PivotTable.
If your data is in an Excel table – this dialog is not shown and option No is assumed.
In the final step, select a location for your PivotTable and then click Finish to confirm.
Using Excel Tables
Another way of creating separate caches is to use Excel tables.
Before you start creating a PivotTable, click anywhere in your data source and then use the shortcut key CTRL T to convert the data to an Excel table. Alternatively, click the Table button on the Ribbon’s Insert tab.
The Create Table dialog will confirm your data source. Click on OK to confirm.
A Design tab appears on the Ribbon when you create an Excel table. On the far left of the Design tab a Table Name box displays the table name, by default this will be Table1. You don’t need to change table name at this stage but do understand that the table has been named.
Now start the process of creating your PivotTable by clicking on the Summarize with PivotTable button on the Ribbon’s Design tab.
In the Create PivotTable dialog you will see that the PivotTable is using the Excel table as its data source. Choose where you want the PivotTable to be located and then click OK to confirm.
Now you need to convert your table back to a normal range. To do this click anywhere in your data source and click the Convert to Range button on the Ribbon’s Design tab.
Next convert your data source back to a table and make sure the table has a different name – Excel will give it a different name by default, probably Table2. Create your next PivotTable based on this new Excel table in exactly the same way as shown above.
As you have renamed the Excel table, Excel will treat it as a separate data source and therefore create a separate cache.
You can create a separate cache for a PivotTable that is currently sharing its cache. To do this you need to make a slight change the range of cells the PivotTable is based on: this creates the separate cache. You can then revert back to the original data range.
Follow these steps:
- Click into a single cell in the PivotTable you want to create a separate cache for
- Invoke the legacy PivotTable wizard with ALT+D+P
- Click Back to take you from Step 3 to 2 and change the range so it includes one fewer rows eg $A$1:$G$17860 becomes $A$1:$G$17859
- Click Next to take you forward to Step 3 and ensure the PivotTable location is the same as before. Click Finish to confirm.
- Now repeat 2 to 4 but this time revert to the original range.
If you are unsure how many caches exist within a workbook you can get Excel to count them for you. You need a small bit of VBA which you input into the Immediate window of the Visual Basic editor.
Firstly, open the Visual Basic Editor with the shortcut ALT F11. The Immediate window sits at the bottom right of the screen. If it is not open, use the shortcut CTRL G to open it.
Enter the following code ?ActiveWorkbook.PivotCaches.Count
Then press ENTER. The result will appear below the code as shown here.
Keep File Size to a Minimum
Delete Data Source to Reduce File Size
As a PivotTable creates a cached copy of your data, you can actually delete the original data source and the PivotTable will still function. This will reduce the file size significantly.
You can even use your PivotTable to recreate your data source. Simply place a field in the Values area of the PivotTable but leave the other areas empty.
Now double click on the value that this calculates in the PivotTable: the cached version of the database will be created on a separate sheet.
Dump Cache on Close
You can ask Excel to dump the cached copy when you close your workbook. The setting is in PivotTable Options. Click into your PivotTable and then click on the Ribbon’s Analyze tab (Options tab – Excel 2010 & 2007) and then click the Options button.
In the PivotTable Options dialog, click the Data tab and then uncheck the option Save data source with file. Click on OK to confirm.
When you reopen your PivotTable you will need to refresh it before you can make any changes.
Right-click in the PivotTable and select Refresh from the menu.
You can get the PivotTable to automatically refresh on open by checking the Refresh data when opening the file option back in the PivotTable Options dialog.