Saving Source Data with Pivot Table File

Save Source Data Setting for pivot table https://www.pivot-table.com/

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

Save Source Data Setting for pivot table https://www.pivot-table.com/

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.

savesourcedata01

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.

Save Source Data With File for pivot table https://www.pivot-table.com/

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.

savesourcedata05

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.”

savesourcedata04

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

______________________

9 thoughts on “Saving Source Data with Pivot Table File”

  1. 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.)

      1. 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.

  2. 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?

    1. 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

  3. 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

  4. 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….

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.