Md. Shamim Reza
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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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.
đź“Ś 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.
đź“Ś 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. 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.
Conclusion
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. Explore our ExcelDemy blog to learn more about excel. Stay with us and keep learning.
