If you’re building any kind of Excel based dashboard or interactive report it’s probably going to include Pivot Tables and you’re probably going to need to apply filters to those Pivot Tables. That’s where Slicers come in useful.

But at the same time, you’re probably going to want to protect the sheet containing the dashboard to prevent accidental changes – or deliberate sabotage!

The problem is that when worksheet protection is enabled, the Slicer buttons stop working. There is however a simple fix. Before turning on Sheet Protection…

• Right click the Slicer
• Select Size and Properties
• Open the Properties section
• Untick Locked
  

When you turn on Sheet Protection make sure to tick “Use Pivot Table and Chart” otherwise when you click a button on the Slicer, you’ll get a message saying that the Pivot Table that the Slicer is attached to can’t be changed as it’s protected.

If you want to see this in action, watch this video. Plus as a bonus, I’ll show you how to prevent a Slicer being moved or resized.

Note there’s still no easy way that I’m aware of to prevent the Slicer being deleted even when Sheet Protection is on. You can prevent users selecting the Slicer and pressing the Delete key but without editing the Slicer’s right click menu you can’t prevent them using Cut or Remove Slicer commands.

If you want a copy of the file that I used in the video, it’s available in the YouTube description.