[ad_1]
If you have spent a significant amount of time on a spreadsheet solution, possibly a dashboard, got everything just as you want it, then you definitely do not want your users accidentally (or in deed deliberately) move, delete, replace or change data which could affect your analysis and data presentation on you spread sheet.
You do however want users to use your work book and interact with it, especially in such spreadsheet solutions as interactive dashboards. One of the main ways to do this is via Pivot slicers to give control of the dashboard to users so they explore and use it to identify trends and make business decisions.
This article will take you step by step setting up your work sheet protection but allow access to Pivot Table slicers to maintain the integrity of your work sheet data and functionality. There are a few stages to this process.
This assumes that you have already created a Pivot Table and a corresponding slicer control.
Creating a slicer is easy for your Pivot Table
- Click anywhere in your Pivot Table
- In Pivot table Tools – Options – and Design tab will appear
- In the Options Tab- in Sport And Filter- Group hit Insert Slicer
- In the dialog box that appear select the fields you want use to filter your Pivot Table.
- Hit OK- you now have a slicer
Stage 1 is to ensure that the Pivot Table Slicer is ‘unlocked’.
- Firstly Right click on your slicer and select Size and Properties Options
- Drill down into Properties
- Unlock the slicer by un ticking or de selecting the Locked option
- Hit Close to finish the process
- This unlocks the slicer
- Repeat for as many slicers as you require for your work sheet
Stage 2. We now turn our attention to the work sheet piece of the solution
- Hit the Review tab on the Excel Ribbon ( Excel 2010)
- In the Changes Group select to Protect Work Sheet
- In the Allow All Users Of This Worksheet To,un tick all options apart from Select Unlocked Cells and Use Pivot Table Reports
- Enter a password if you want to to add further protection to your work sheet. This is definitely recommended as you have made an effort to lock down the spreadsheet and just allow users to manipulate the pivots via slicers.
The users will only have access to the Pivot Table Slicers therefore ensuring your work sheet is not altered or tampered with in any way, in particular your data and structures.
[ad_2]
