I recently received this question from our Elevate Excel training program:
“How can I lock everything on a sheet except for the slicers?
It's a good question.
You may have a dashboard like this one, where you don't want users to make changes to any of the charts on the left, but you do want them to be able to interact with the slicers on the right.
Video Tutorial
Watch on YouTube & Subscribe to our Channel
Downloads
It's possible to lock an entire sheet except for certain components. Here's how.
First, right-click on the slicer and select Size and Properties from the right-click menu. Or you can use the keyboard shortcut Ctrl + 1. That will open up the Format Slicer pane. Uncheck the option that says Locked.
You can do this for multiple slicers or one at a time.
After you've unlocked the slicers, you can protect the sheet. To do that, right-click on the sheet's tab, and select Protect Sheet.
That will open a menu where you can adjust the settings and create a password for unlocking the sheet.
Once you've adjusted the protection settings to be how you like, just hit OK, and your sheet will be unalterable except for the slicers that you unlocked.
The charts will continue to update as users select different options on the slicers, but the charts themselves cannot be selected or manipulated directly.
If you'd like to know more about building dashboards, I recommend you check out my Free Training for Pivot Tables & Dashboards.
Leave a comment if this short post has been helpful for you!
Unfortunately, unlocking a slicer not only allows the user to select items on the slicer, it also lets the user move the slicer around.
Additionally we need to keep in mind that if the data is sitting on a pretected worksheet wo won’t be able to make changes even though the slicers themselves are not protected. The data would still be, hence no changes allowed.
Very informative, easy to understand, easy to apply. Definitely worth knowing.