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
- meta data,
- or named ranges,
- or even hidden or very hidden worksheets).
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:
Follow these steps:
- Right-click on any cell inside your PivotTable and then on “PivotTable Options”.
- Go to the Data tab.
- Uncheck “Save source data with file”.
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).
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.
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:
- Go to the Professor Excel ribbon and click on Break Links.
- 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.
- Start by clicking “Start”.
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
