Using Slicers on Protected Worksheets

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.

Click to enlarge

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.

Right Click Menu

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!

4 comments

Your email address will not be published. Required fields are marked *

    • This is such a huge drawback when designing a report to be used by others.

      The excel people should really fix this asap.

  • 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.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter