Excel slicer with password protection

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.

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.

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.