This article illustrates how you can allow people to use filter in your protected Excel sheet. You may need to do this to create a user-friendly worksheet while needing to protect it from unnecessary changes by them. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that.
How to Use Filter in Protected Excel Sheet
Imagine you have a dataset containing a list of cheeses with categories. You want people to be able to filter the list but not to make any changes to the data. Then follow the steps below.
Steps to Allow Filter in Protected Excel Sheet
📌 STEP 1: Select the Dataset
- First, select the range of data (C4:C36) that you want to allow users to filter.
📌 STEP 2: Apply Filter
- Then, select Data >> Filter as shown in the following picture.
Read More: How to Apply Multiple Filters in Excel
📌 STEP 3: Notice the Filter Button
- After that, you will see a small downward arrow at the bottom-right corner of cell C4. This will allow your users to filter the dataset by category.
Read More: How to Hide Filter Buttons in Excel
📌 STEP 4: Protect the Worksheet
- Next, select Review >> Protect Sheet as shown below.
📌 STEP 5: Allow Users to Use Filter
- Then enter a password in the Protect Sheet window if you want to make it password-protected. Otherwise, you can leave it empty.
- Next, uncheck Select locked cells.
- Then, check Use AutoFilter.
- After that, select OK.
📌 STEP 6: Check If the Sheet Is Protected
- Now whenever the users will try to make any changes to your worksheet, they will see the following error.
📌 STEP 7: Use Filter in the Protected Sheet
- But, they can easily filter the data by category. You can add Filter or use a shortcut instead. Let’s say, one of the users wanted to filter the Cream Cheeses.
📌 STEP 8: See the Filtered Data
- Then, s/he will see the following result.👇
Read More: How to Filter Data in Excel using Formula
Things to Remember
- You must use a password to actually protect the sheet. Otherwise, the users can just select Unprotect Sheet and make changes to it.
- You can check Sort in the Protect Sheet window if you want to allow them to sort the dataset too.
Download Practice Workbook
You can download the practice workbook from the download button below.
Now you know how to allow people to use filter in a protected Excel sheet. Please let us know if this article has helped you to solve your problem. You can also use the comment section below for further queries or suggestions. Stay with us and keep learning.
- How to Filter in Excel with Merged Cells
- How to Filter Cells with Formulas in Excel
- How to Filter Email Addresses in Excel
- How to Filter by List in Another Sheet in Excel
- How to Remove Filter in Excel
- How to Filter Data Based on Cell Value in Excel
- How to Filter Column Based on Another Column in Excel