Who doesn’t love slicers? What an awesome way to sort and filter data. We have talked about how, in addition to being a time saver, slicers can make it easier to share your data with others who are not as comfortable working in Excel. Not everyone understands, “filter the data,” but we all can understand “push this button!”
At the same time, in the advanced Excel sessions, we talk about how you often want to protect a workbook from accidental changes, particularly after you have spent a lot of time creating elaborate formulas. Unfortunately, protection also renders your slicers useless… Here is a great compromise: you can protect a document and leave your slicers fully functional by incorporating a very simple method. Let’s take a look.
Before we do, big thank you to Erin for having this question in last week’s Excel Essentials session and inspiring this byte!
Exercise File
If you would like to follow along, you can download the exercise file here:
This is a list of fictional students in a fictional class with their fictional grades. (This is fictional data, friends). In this scenario, it looks like you decided to turn this data into a table, because of course you did! And you added some slicers for ease of use so you can quickly filter and only view students with A’s or Freshmen with A’s, etc.
By the way, if you are not fully comfortable with tables or slicers in Excel, please come attend one of my Excel Essentials sessions! I would love to see you.
Disclaimer
- With protection options, be very careful when applying passwords. If you lose the password, unfortunately, we cannot get it back for you!
- You can also lock a document without applying a password, which is handy to know if you are worried less about dishonesty and worried more about accidental typing. This is the route we are going to take today.
Unlock Slicers
1. Right click on a slicer and select Size and Properties.
2. On the right side of the screen a Format Slicer menu will appear. Click the triangle next to Properties.
3. Uncheck the box next to Locked.
4. Follow the same steps for the second slicer.
Protect Sheet
- In the ribbon, go to the Review tab, Protect group and select Protect Sheet.
2. This is where you could set up a password, but for today, let’s leave that area blank.
3. Scroll down in the list and check Use AutoFilter. Click OK.
Now… notice that you cannot make a change to a specific cell (error message below), but the slicers should be fully functional allowing users to filter the data.
Unprotect Sheet
Don’t forget, when you want to edit, you can Unprotect the same place you Protect a sheet.
In our case, this was on the Review tab, Protect group. It can also be found in Backstage View.
If we had created a password, this is also where we would have been prompted to enter it to unlock the sheet.
Thoughts?
I would love to hear your thoughts on this! Have you been frustrated by not being able to protect a worksheet and user slicers? Will this save you any trouble?
Congratulations, Power Users!
Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser