How to Use Filter in Protected Excel Sheet (With Easy Steps)

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


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.

Use Filter in Protected Excel Sheet


πŸ“Œ 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.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo