When you create a pivot table in your workbook, the source data records are saved in a special memory area – a pivot cache. Then, when you close the file, Excel can save the source data in this pivot cache, or clear that memory.
Keep reading, to see how you can change this setting, and learn the advantages and disadvantages of either choice.
Note: If you want to re-create the source data, see: Re-create the Source Data
Advantages and Disadvantages
There are advantages and disadvantages to both options:
If you save the Source Data:
- File will be larger
- File may open more quickly
If you do not save the Source Data:
- Fill will be smaller
- File may open slowly while the pivot cache is rebuilt
- Pivot Table must be refreshed after opening the file
How To Change the Setting
To turn the Save the Source Data setting on or off:
- Right-click a cell in the pivot table, and click PivotTable Options.
- On the Data tab, in the PivotTable Data section, add or remove the check mark from Save Source Data with File
- Click OK.
Test the Results
With a small file, you probably won’t notice much difference, whether this setting is turned on or off. I tested with a 14.3 MB file, to see what would happen.
After turning off the Save Source Data setting, I saved the file again, and the size was reduced to 11.7 MB – about 18% smaller.
Refresh Data When Opening
If you choose to turn off the Save Source Data with File option, you should turn on the Refresh Data When Opening the File option.
That setting is just below the Save Source Data check box.
Otherwise, you’ll see a message when you try to filter the data, or make any other layout changes.
“The PivotTable report was saved without the underlying data. Use the Refresh Data command to update the report.”
If you see that message, click OK, then manually refresh the pivot table.
And to avoid the annoying message, turn on the Refresh Data When Opening the File option.
More Articles on Pivot Table Source Data
- Re-create the Source Data
- Find the Source Data:
- Pivot Chart – Change Source Data (Excel 2010)
- Saving Source Data with Pivot Table File
- Protect the Source Data
- Normalize Source Data For Better Results
- Change Field Names in Source Data
- Filter the Source Data
- Copy Source Number Formatting
- Number of Records in Source