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
______________________
I would be nice if Microsoft extends the message with a few more options:
[ ] Refresh now.
[ ] Refresh now and remember the setting. (Automatically checks the option ‘Refresh data when opening the file’.)
[ ] Refresh later. (User will refresh manually.)
What if the (Save source data with file) option is grayed out? How do I make it available?
Brenda, if you are using an OLAP data source, the setting isn’t available. Could that be the problem?
Hi Debra, I’ve got the same question, however my source data is not OLAP, but is internal to the workbook as the pivot tables are contained, just in a different worksheet. I have several pivot tables, of which only one has the ‘Save source data with file’ option greyed out.
I’m having a problem with the source data – when using the “save as” feature, the new workbook has the pivot data linked to the old one. Can it be fixed?
H Debra, I am experiencing this problem also, to some extent, but it appears also to be related to the problem I described above relating to the greyed out ‘Save source data with file’ option. As described there my source data is not external or OLAP, but is within the same workbook in which the pivot tables are contained, just in a separate worksheet (in a defined table named ‘Transactions’). When the workbook is ‘saved as’ with a new filename (filename_v2.xlsx) it can be reopened okay, but when looking at the data sources for the pivot tables, only for the PT that has the ‘Save source data with file’ option greyed out has the source data been automatically updated to the source data table in the new worksheet (i.e. it is showing the data source as filename_v2.xlsx!Transactions). However, the data source for the other PTs – those for which the ‘Save source data with file’ option is available (i.e. not greyed out) – still points to the table in the earlier version of the worksheet, i.e. filename_v1.xlsx!Transactions. If I delete the earlier version of the worksheet (or just rename the (updated) v1 worksheet as v2 instead of saving a new separate v2), then I get an error message upon reopening say that the v1 file – to which it has internal links – can’t be found.
I’ve searched a lot for a solution to this to no avail, so your help in addressing this would be much appreciated, please!
regards, Nick
Hi Debra,
maybe a little off-topic, but a painful pivot issue…
I have an external SQL source, from which I want to create a PT. So far, no problem.
then, I wanted to pass parameters to filter the results on SQL. Then again, no problem.
but when I try to set the cell to automatically refresh when changing, then Excel Crashes. EVERYTIME.
tried to do the same in 2003. pain, lots of it.
back to 2013/2016. connected the SQL to a table, not a PT.
pass parameters to refresh the table.OK.
create a PT from the table. OK
BUT… cannot update the PT after table is refreshed.
any suggestions????
Thanks in advance.
Martín
Is there any way to auto refresh the pivot while i pasting the data. If i open the file it will automaticly refreshing the pivot. If i pasting the report similalrly pivot need to auto refresh.
Can anyone clarify this….