How to Prevent Users to Restore PivotTable Source Data

In a “normal” PivotTable (without changing any of the default settings), other Excel users can easily restore the source data. Also, when you copy and paste the PivotTable into a new Excel file without the data. But what, if you want to prevent other users to see all the data? Here is how to do that!

Disclaimer

Before we start, some words of caution. In this article you will learn different methods of how to prevent people to restore source data of a PivotTable.

Please be careful and test you files well if you want to share PivotTables containing confidential data!

There might still be data in your file, for example

And also please keep in mind: Copy and paste the PivotTable to a new file. Or make at least sure that the real source data is not included in your file!

(I don’t take any responsibility…!)

Method 1: Don’t save the data with the file to prevent others seeing all raw data

The first method is relying on standard PivotTable functionality: Within the PivotTable options you can disable that the data is saved in the Excel file. Only the current view of the PivotTable is visible, dragging / dropping or drilling down is not possible. When a user tries to change the PivotTable, the following message appears:

Error message when you try to change a PivotTable that is not saved with the underlying source data.
Error message when you try to change a PivotTable that is not saved with the underlying source data.

Follow these steps:

  1. Right-click on any cell inside your PivotTable and then on “PivotTable Options”.
  2. Go to the Data tab.
  3. Uncheck “Save source data with file”.
Prevent restoring the raw Pivot data by unchecking “Save source data with file” within the PivotTable Options.

Please keep the following comments in mind:

  • Make sure that your source file is not saved on a public (also internal) location so that the refresh still works.
  • Some parts of the data can still be seen, for example entries of a filter.

Technical background

If you are interested in the technical background: If you uncheck the option to save source data with the file, the underlying XML file within your Excel file is missing (here is more information about the XML file structure of an Excel file).

Data included: The Excel file contains a XML file with all data entries.
Data included: The Excel file contains a XML file with all data entries.
If you uncheck the option "Save source data with file", the XML file is missing and users cannot restore the source data.
If you uncheck the option “Save source data with file”, the XML file is missing and users cannot restore the source data.


Do you want to boost your productivity in Excel?

Get the Professor Excel ribbon!

Add more than 120 great features to Excel!


Method 2: Protect sheets to prevent other users to see detailed data

This method I have listed because it works – but actually is quite unsafe. Unsafe, because the sheet protection can easily be removed (even if a password is set). Also, the data is still available within the Excel XML source.

Go to the Review ribbon and click on Protect Sheet. Set a password and click on OK. Make sure that “Use PivotTable and PivotChart” is not checked.

Prevent users from restoring PivotTable source data by protecting the worksheet.
Prevent users from restoring PivotTable source data by protecting the worksheet.

Method 3: Be really sure and only save hard-copied values

The safest way to prevent users from restoring the PivotTable source data is to remove the Pivot functionality. You convert the PivotTable to normal cells.

Our Excel add-in Professor Excel Tools has such functionality. When you share a PivotTable with someone else, use the built-in break link functionality:

Convert PivotTable to data only so that all raw data cannot be restored.
Convert PivotTable to data only so that all raw data cannot be restored.
  1. Go to the Professor Excel ribbon and click on Break Links.
  2. If your file contains one (or multiple) PivotTables with source data in another Excel file, select the option to break the link. This will convert the PivotTable to a normal cell range.
  3. Start by clicking “Start”.

Professor Excel Tools Box

This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


You don’t want to use an add-in? In this case just copy the PivotTable by pressing Ctrl + C on the keyboard. Next, paste it using the Paste Special function. Therefore, press Ctrl + Alt + V and check “Values”. Click on OK.

Image by Erich Westendarp from Pixabay

Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

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