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.
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 attached to this article 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.
Next, type anything into the Macro Name field and the Create button will become visible. Click the Create button.
Replace the existing code with the contents of the attached document that contains the macro:
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 on the Macros button:
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.