Protected Excel Sheets Allows Filtering but not Sorting

Question

I have created a table in an excel sheet which is 40ish columns in total, 10 columns are manually entered by the person with the rest being mathematical equations or vlookups to other sheets/files. This table will need to be sorted and filtered so I just did the Autofilter for it. To protect the equations from accidental override or deletion I want to lock the sheet. So I went through made sure that all the columns that required manual entry were unlocked and everything else was locked.

When I protect the sheet, I make sure to check the box for both "Sorting" and "Use AutoFilter". Filtering works just fine while the sheet is protected but anytime I try to sort a column it pops up an error saying "The cell or chart you're trying to change is on a protected sheet......" and does nothing. If I unlock the sheet, everything works fine and I can filter/sort any column I want. I tried protecting the sheet with different setting a few time but they wall were the same. Filter worked fine when it was selected but it always errors out when I try to sort.

I have seen other posts noting similar issues and several suggest using the "Allow Edit Ranges" options as a fix for this issues, but I have followed those steps and it does not work for me.

Answer 1

Hi, Mikael_E

Thank you for using Microsoft products and posting them to the community.

You can try my scenario:

Allow the edit range first - then don't set a password - click confirm.

Next select Protect Worksheet - uncheck the box to select locked/unlocked cells and then select Sort By:.

Then just sort through that:

But it should be noted that sorting is for the whole, for a single column cannot be done, if you want to do the sorting of a single column, then it will involve your cells can be edited (that is, allow editors to set a password, and then protect the worksheet check box to choose to lock/unlock the cell of the situation). As well as if you're filtering you need to do it in advance:

I hope the above information can help you. Feel free to send a message if you need further help.

Best wishes

Aiden.C - MSFT |Microsoft Community Support Specialist

Answer 2

I have the exact same problem. Whilst Allow Edit Ranges works, it will unprotect the cells in the range allowing the user to edit formulas which you wish to protect.

The question is, in a protected sheet, it it possible to sort if some data is locked ?

Answer 3

The question is, in a protected sheet, it it possible to sort if some data is locked ?

No.

You are correct, Edit Ranges unlocks the cells and in fact if you want to sort in a protected sheet, all data that should be sorted must be located in unlocked cells. Edit Ranges is just a different way to unlock cells for certain users.

Simple scenario:

B2: =A2*2
D2: =A2*2
B9: =[@Nr]*2
D9: =Table1[@Nr]*2

If we unlock the cells in A:B, protect the sheet and allow sorting we can sort the sheet (A1:B5) as we like and also the table (A8:B12) as we like.

But if we lock the cells in column B we can not sort anymore. This is a known issue for decades.

The only workaround, if you have formulas ONLY in the other columns, is to separate them from the data to be sorted, as I did in column D. The empty column C separate the formulas from the data.

Andreas.

Answer 4

Thanks for the information. Since filtering worked I assumed sorting should too but after thinking about it over the weekend filtering just hides rows and doesn't manipulate the actual data. I am not able to group the unlocked cells together, so I will just need to try and think of a work around to get by without sorting.