After you set up an Excel pivot table, use macros to allow or block pivot table features and commands. That should discourage other people from making layout changes, which could affect other parts of your Excel workbook.
Category: Protection
Protect Pivot Table Source Data
If you have a pivot table that contains sales data for several regions, can you send each manager a copy of the pivot table with their region selected in the Report Filter, and prevent them from seeing data for other regions?
The short answer is , “No!”
Allow Excel Pivot Table Use on Protected Sheet
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.
_____________________
Block Excel Pivot Table on Protected Sheet
In some workbooks, you might want to prevent users from making any changes to a pivot table. You want them to see the pivot table, but not change it. However, users might need to make changes to data in other areas of the worksheet.
If you protect the worksheet without enabling pivot table use, users won’t be able to modify the pivot table, but will have access to other unlocked 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.
- Remove the check mark for 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 not selected, users won’t be able to make changes to the pivot table, and the field list will be hidden.
NOTE: 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.
_____________________