Pivot Table Issues

Fix Excel Cannot Save PivotTable Source Data with Workbook

Camila Milosovich Oct 10, 2026 869 views

Question details

The user is unable to check the option to save source data with the file within the PivotTable Options.

How to Fix Excel Cannot Save PivotTable Source Data with the Workbook

Product: Microsoft Excel
Device & OS: not provided
Scenario: Configuring PivotTable data options to ensure the source data cache is saved along with the workbook.
Observed behavior: The 'Save source data with file' checkbox is greyed out and cannot be selected.

Before you start

Check if your PivotTable was created using the Excel Data Model, as this changes how data is internally stored and manages cache settings automatically.

Solution 1Recommended

Recreate the PivotTable Without the Data Model

Identify if the PivotTable uses the Data Model and recreate it as a standard PivotTable to regain manual control over the source data caching options.

When a PivotTable is based on the Excel Data Model (Power Pivot), the 'Save source data with file' option is intentionally disabled by default. This occurs because the Data Model already stores and manages the dataset inside the workbook's internal memory, making a separate PivotTable cache setting redundant.

Verify Data Model usage

Look at the PivotTable Fields pane on the right side of your screen. If you see multiple tables listed or a small orange cylinder (database) icon next to your table name, your PivotTable is using the Data Model.

Delete the existing PivotTable

Highlight the entire existing PivotTable and press the Delete key to remove it, as you cannot convert a Data Model PivotTable back to a standard one directly.

Insert a new standard PivotTable

Select your source data range, navigate to the 'Insert' tab on the ribbon, and click 'PivotTable'.

Uncheck the Data Model option

In the 'Create PivotTable' dialog box, look at the very bottom and ensure the checkbox for 'Add this data to the Data Model' is strictly unchecked, then click 'OK'.

Enable the save source data option

Right-click anywhere inside your newly created PivotTable, select 'PivotTable Options', navigate to the 'Data' tab, and you will now be able to check the 'Save source data with file' option.

Recreate the PivotTable Without the Data Model

Data Model Advantages: Using the Data Model is highly recommended for handling millions of rows or establishing relationships between multiple tables, even though the manual save source data option is disabled.

Create PivotTables Easily in WPS Office

Create and Manage PivotTables Seamlessly with WPS Office

WPS Spreadsheet offers a straightforward way to create PivotTables and manage your data sources without the confusion of disabled settings or forced data models. Here is how you can easily create a PivotTable and control your data cache.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and select the range of cells containing the data you want to analyze.
2. Insert a PivotTable: Navigate to the 'Insert' tab on the top ribbon and click on the 'PivotTable' button.
3. Choose placement options: In the creation dialog, confirm your data range, select whether to place the PivotTable in a New Worksheet or Existing Worksheet, and click 'OK'.
4. Manage data options: Right-click the generated PivotTable, select 'PivotTable Options', and click the 'Data' tab to easily manage your source data retention settings.

Fully compatible with Microsoft Excel (.xlsx) PivotTable formatsClear and intuitive data cache settings without forced restrictionsLightweight application that processes large datasets smoothlyFree alternative to Microsoft Office with a familiar interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is the 'Save source data with file' option greyed out in Excel?

This option is disabled when your PivotTable is connected to the Excel Data Model (Power Pivot). The Data Model automatically compresses and stores the data inside the workbook, making the traditional PivotTable cache setting unnecessary.

How do I remove an existing PivotTable from the Data Model?

You cannot directly remove an existing PivotTable from the Data Model once it is created. You must delete the PivotTable and insert a new one, making sure to leave the 'Add this data to the Data Model' box unchecked during creation.

Does adding data to the Data Model increase my Excel file size?

Yes, adding data to the Data Model can increase your file size because Excel stores a highly compressed copy of the data inside the workbook's internal structure. This is precisely why the separate option to save source data is disabled to prevent duplicate data storage.

Excel & Data Problems