Saving Source Data with Pivot Table File

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 http://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 http://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

______________________

This entry was posted in Refresh. Bookmark the permalink.

7 Responses to Saving Source Data with Pivot Table File

  1. XLarium says:

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

  2. Brenda says:

    What if the (Save source data with file) option is grayed out? How do I make it available?

    • Debra says:

      Brenda, if you are using an OLAP data source, the setting isn’t available. Could that be the problem?

      • NickC says:

        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.

  3. Mihai says:

    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?

    • NickC says:

      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

  4. Pingback: Pivot Cache in Excel - What Is It and How to Best Use It

Leave a Reply

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