Protecting a Pivot Table

Overview

In some circumstances an organization will want to restrict users from being able to change a pivot table based report.  This will include preventing things such as adding additional filters, slicers, fields, or changing the order of certain things in the report. They do, however, still want their users to be able to refresh the report and use existing filters or slicers.

Process

In general, Excel is meant to allow users full control over the pivot tables so that they can make any changes that they would like to make.  In order to override this, a macro is used to disable some of the settings that exist in the background of Excel.  The document Restrict_Pivot_Table_Macro.txt contains the macro that will disable the ability for users to modify pivot tables.  The macro would then need to be run on each sheet in the Excel file that should be restricted.

Please note: Once the macro has been run the sheet will not be able to be modified. It is strongly recommended to keep an original or master copy in a safe place for future editing and save a new copy as the one to be restricted.

1. First, the macro must be added to the Excel file. To do this navigate to the View ribbon and then click the Macros button. This step may vary depending on the version of Excel.

   

2. Next, type anything into the Macro Name field and the Create button will become visible. Click the Create button.

   

3. Replace the existing code with the contents of the attached document that contains the macro:

   

4. Close out of the windows to get back to the Excel file. If it prompts you to save as a Macro enabled workbook you do not need to do so as we will not need to save the macro in Excel.

   Once back in Excel, navigate back to the View ribbon and click the Macros button.

   

5. Click the "RestrictPivotTable" macro and click the Run button.

   

The PivotTable ribbon is no longer visible to the user when a cell within the pivot table is selected.