Remove Pivot Table Data

Remove Pivot Table data

Pivot tables are without doubt one of Microsoft Excel’s best tools. However there have been recent cases where due to lack of knowledge the users did not remove Pivot Table data and as a result, confidential information was exposed.

Pivot tables store the underlying data within the spreadsheet which is why it is so easy to manipulate the reports. However, with a simple double click you can see all this underlying data. For example the pivot report may only show the average salaries per department, but a double click will show the detailed salaries.

You can remove this capability from the pivot table however.

  • Right click on the pivot table and choose PivotTable options.
  • Click on the Data tab
  • Untick ‘Save source data with file’ (in order to make changes to the pivot table you will need to be connected to the database- also makes you file smaller)
  • Untick ‘Enable show details’ (a user won’t be allowed to double click)