Blue Flower

Use Slicers on a locked worksheet

In this Excel tutorial I will show you how to use Pivot Table and Slicers to filter data on a locked worksheet. The situation of this task is shown in the picture below.

Data on the worksheet for which we need to create a PivotTable

Creating Pivot Table

Based on the data on the worksheet we need to create a Pivot Table. Of course if you do not know how to make a PivotTable, see the tutorial on the link. Pivot tables can be filtered using the buttons on it. However, this is a tutorial how to use data filtering using Slicers and let's go further.

Created PivotTable in Excel

Creating Slicers based on PivotTable data

The next step is to create Slicers based on Pivot Table data. If you do not know how to make Slicers see the relevant link. Notice in the picture below, I have created two slicers for 'Names' and 'Data' because I want to filter the data by the respective titles.

Created Slicers, on a worksheet

Set the settings to use Slicers on a locked worksheet

After creating a Pivot Table and adding Slicers to the same worksheet, you need to change the settings for Slicers. To change some of the settings that will allow us to use Slicers on a locked worksheet, do the following.

Right-click mouse on the title of the first Slicer. From the drop-down menu choose 'Size and Properties ...' (see image below)

Launch Slicers settings in Excel

In the right part of the Excel window, the 'Format Slicer' box appears, where we can change the basic settings. Our goal is to change several settings.

  1. On the 'Format Slicer' window in 'Position and Layout', turn on the option 'Disable resizing and moving'
  2. On the 'Format Slicer' window of the 'Properties' item, select 'Do not move or size with cells'
  3. On the 'Format Slicer' window, in 'Properties', turn off the option 'Locked'

Basic Slicers settings in Excel 

This setup action must be done for each Slicer on the worksheet.

Locking a Worksheet in Excel and set options for filtering data using Slicers

After setting all the above settings, you must lock the worksheet. If you do not know how to lock the worksheet, see the instructions on the relevant link. In this case, you have to choose two options from the dialog window "Protect Sheet".

In the image below, notice that only two options are included.

  1. Select unlocked cells
  2. Use PivotTable & PivotChart

 Settings for locking a worksheet when you use slicers in Excel

And that's it, you can now use Slicers on a locked worksheet. Slicers you can not delete, also Slicers will not be able to move. Of course, if you need to include some other options, you can do it, but then you must be aware that you risk certain actions you want to prevent (for example, do not include 'Edit objects' or the like).

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.