If you’ve spent a significant amount of time on a spreadsheet solution, possibly a dashboard, got everything just the way you wanted it, then you definitely don’t want your users to accidentally (or deliberately) move, delete, replace, or change data that might affect your analysis and presentation of data in your spreadsheet.
However, you want users to use and interact with your workbook, especially in spreadsheet solutions like interactive dashboards. One of the main ways to do this is through Pivot Slicers to give users control of the dashboard to explore and use to identify trends and make trading decisions.
This article will guide you step-by-step to set up protection for your worksheet, but will allow access to PivotTable slicers to maintain the data integrity and functionality of your worksheet. There are a few stages in this process.
This assumes that you have already created a pivot table and corresponding slicer control.
Creating a slicer is easy for your pivot table
- Click anywhere on your pivot table
- In PivotTable Tools – Options – and the Design tab will appear
- On the Options tab, under Sport & Filter, press Insert Group Cutter.
- In the dialog box that appears, select the fields you want to use to filter your pivot table.
- Press OK, now you have a slicer
Level 1 is to make sure Pivot Table Slicer is ‘unlocked’.
- First, right-click on your slicer and select Size Options and Properties
- Delve into Properties
- Unlock the slicer by checking or selecting the Locked option
- Press Close to finish the process
- This unlocks the slicer
- Repeat for as many slicers as you need for your worksheet
Stage 2. We now turn our attention to the solution worksheet.
- Press the Review tab on the Excel ribbon (Excel 2010)
- In the Change Group, select Protect Worksheet
- Under Allow all users of this worksheet, uncheck all options except Select Unlocked Cells and Use PivotTable Reports
- Enter a password if you want to add more protection to your worksheet. This is definitely recommended as you have made an effort to lock down the spreadsheet and only allow users to manipulate pivots via slicers.
Users will only have access to Pivot Table Slicers, thus ensuring that their worksheet is not altered or manipulated in any way, particularly its data and structures.