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

Get FREE Advanced Excel Exercises with Solutions!

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


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.

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.

Download Practice Workbook

You can download the practice workbook from the download button below.


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. Stay with us and keep learning.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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.

2 Comments
  1. This was exactly the solution I needed, and you explained it very clearly. Thank you!

    • Dear NancyM,

      Thanks for your appreciation. To get solutions with detailed explanations, follow ExcelDemy.

      Regards
      Shamima Sultana
      Project Manager | ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo