Filter in a protected xlsx sheet.

[Ramandeep Singh]

I have created a protected sheet where there is a table and several columns have a read only data. The user can only fill data in a few of columns. I want to enable filtering in this sheet but that option is greyed out in the protected sheet. 

I am using Office 2016. 

[#2 · Accepted solution]

Hi,

Please try to enable the filter before you protect the worksheet.

And when you go to protect it, check the Filter check box to allow users to use it.

It works in Excel for Windows, and I think it will work on Mac as well.

Good luck

[#3]

Hello, You are right. It is correct.
I have the same question for sorting too. If you can help me regarding that too then I would really appreciate it.

[#4]

Hi,

To allow users to sort the data, you have to unlock cells in your table and turn on the filter if you wish before you apply the protection.

All cells in the Excel worksheet are locked by default, to learn how to unlock a specific range of cells, please follow this link.

After you unlock the cells and turn on the filter you can go ahead and apply the protection.
But don't forget to check the Filter checkbox, and Sort checkbox as well.

I hope this helps you

Regards

[#5]

@Haytham Amairah 

Hi,

I have the same issue but I understand how to unlock cells and choose for sorting and filtering before I protect the sheet. It still doesn't allow me to sort the data. I read that the cells must all be unlocked but is there a way to filter even when some of the cells are locked? I have formulas and am afraid those using it will accidently delete the formulas without protection.

[#6]

@wlmexcelfun

The filter, sort, and delete are types of editing, so you can't restrict one without the other!

If you want to prevent the users from deleting the content, you will prevent them from sorting and filtering as well.

[#7]

@wlmexcelfun

I just discover a fairly good solution for this.

• keep all cells in the locked state which is the default
• Enable the AutoFilter
• Before you enable the protection, go to Review >> Protect >> Allow Edit Ranges
• Add the range that you want as the below screenshot
• Press the Protect Sheet button in the same dialog

• In the Protect Sheet, deselect the first two options and select only: Sort and Use AutoFilter
• Hit OK

This will prevent the users from accidentally delete any cell's content by preventing them from even select the cells but they will still be allowed to use the Sort and Filter!

Hope that helps

[#8]

@Haytham Amairah 

Thank you!!! This worked perfectly!