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.
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.
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.
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)
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.
- On the 'Format Slicer' window in 'Position and Layout', turn on the option 'Disable resizing and moving'
- On the 'Format Slicer' window of the 'Properties' item, select 'Do not move or size with cells'
- On the 'Format Slicer' window, in 'Properties', turn off the option 'Locked'
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.
- Select unlocked cells
- Use PivotTable & PivotChart
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.