Slicers are one of the best new tools in the recent versions of Excel. However there are some areas you may need help with to make it work properly. One area is where you are building a reporting tool and want users to use the slicers to choose what the report will look like, but you want to protect the sheets so that errors are not accidentally made and in line with recent protection of personal information acts (e.g. POPI and GDPR). So how does the Excel slicer work with password protection?
Switch on password but unprotect slicer
If you have slicers set up and want to password protect the sheets, you first need to tell the slicers that they must not be locked when the sheet is protected. As shown below, you need to right click on each slicer and choose Size and Properties. You then untick the ‘Locked’ box.
This is not the end of the story though.
Protecting the sheet but allowing Pivot changes
When you protect the sheet you also need to allow certain actions that the slicer drives. In the example above we are using a Pivot Table so we need to allow changes to Pivot Tables. In other cases you may need to allow the AutoFilter to work (if the slicer is driving a table).
To protect a sheet you go to the Review ribbon and click Protect Sheet (not Protect Workbook). Depending on what the table is doing you need to allow the use of the Autofilter and/or Pivot Tables.
Now your sheet will be protected but the users will be able to use the slicers to customise what they are seeing.
Have a look at the other options in the Protect sheet dialogue box. Depending on the level of protection you want, some of these options will need to be switched on (or off).