In some workbooks, you might want to allow users to make changes to a pivot table, but you need to protect formulas or data in other areas of the worksheet.
If you protect the worksheet and enable pivot table use, users will be able to modify the pivot table, but won’t have access to other locked parts of the worksheet.
Prepare the Worksheet
The first step is to unlock cells where changes can be made. Then, turn on the worksheet protection.
- Select any cells in which users are allowed to make changes. In this example, users can make changes to cell E2.
- On the Ribbon, click the Home tab.
- In the Cells group, click Format. If the Lock Cell command is enabled, click Lock Cell to unlock the selected cell.
Protect the Worksheet
Next, protect the worksheet:
- On the Ribbon, click the Review tab, and in the Changes section, click Protect Sheet.
- If desired, enter a password.
- Add a check mark to Use PivotTable reports, and check any other items you want enabled on the protected worksheet.
- Click OK and confirm the password, if you entered one.
Test the Worksheet
Because the worksheet is protected, and Use PivotTable Reports was selected, users will be able to make changes to the pivot table. For example, they will be able to move fields, add fields, and use the drop-down lists.
However, some pivot table features won’t be available while the worksheet is protected, including:
- Refresh
- Group and Ungroup
- Report Layout
- Calculated Field
Other pivot tables, based on the same Excel Table as the pivot tables on a protected sheet, will have some features disabled, such as Refresh.
Tip: To refresh a pivot table on a protected sheet, temporarily unprotect the worksheet, refresh the pivot table, and then protect the sheet.
Watch the Video
In this short video, you can see the step by step instructions for preparing and protecting the worksheet.
_____________________
The logic behind not allowing refreshing of pivot tables is not understood. Allowing users to see the results of inserting new data is an essential requirement. Having to unprotect the pivot table to achieve that goes against the whole concept of protection!
Thank you very much. this is very useful tips
Can you confirm the logic of this because once sheet will be protected & someone want to refresh data then why refresh is not allowed in that particular report…
This function is usually to protect a sheet once shared with additional people.
The update is done by yourself and you provide an updated copy
I do not want users to modify pivot table, but just to refresh when data are updated.
what do you suggest?
You can right click, go into “pivot table options” and set the data to refresh every time the workbook is opened under the “data” tab. Maybe that will help.
This was so simple and easy to follow
If you need to have a workaround: “hide” the pivot table on a different sheet and link the sheet you originally wanted the data to be seen as reference, use relational formatting to make it look nice. Then lock the whole spreadsheet (do not lock the sheet with the pivot on as for known reasons).
It’s far from pretty, but you get your result (and much better than removing the lock everytime you change your data input)
this was indeed the end solution – thank you, took a bit of fiddling around but just put the pivot on another sheet and referenced it in the sheet i needed it to display, with some conditional formatting to hide the 0’s