Excel slicer with password protection

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

Excel-slicer-with-password-protection

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.

Excel-slicer-with-password-protection

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

Related

Different levels of protecting cells and spreadsheets in Excel

Stop excel users from inserting or deleting rows

Excel asking for password when it is not protected

Advanced online Excel courses

Grouping items in pivot table slicers

Understand ‘to change the data source first disconnect the filter controls’