Forum Discussion
Sort In Protected Worksheets
Why is there a protection option to allow sort on a protected worksheet - but it doesn't work?
I have found dozens of posts on work arounds, but why oh why do we need work arounds. Just make the built in options work!
I want users to be able to see all the data in a table but not edit any of it - that works.
I want them to be able to select locked or unlocked cells, to allow them to copy data - that works
I want them to be able to filter data by different columns - that works.
So why not Sort???
4 Replies
Quick Fix for Sorting on a Protected Sheet
Hi there,
The short reason it fails…
The "Sort" permission only enables the button; it does not unlock your cells. Because all cells are "Locked" by default, and sorting physically moves data around, Excel blocks it for safety.The 2-step fix:
- Unlock the data: Select the rows you want users to sort. Right-click > Format Cells > Protection tab > Uncheck "Locked".
- Reprotect the sheet: Go to Review > Protect Sheet > tick "Sort" (and "AutoFilter" if needed).
That’s it. Users can now sort freely, but they still cannot edit any values.
Pro tip: Ensure there are no merged cells in your sort range, or convert your data to a Table (Ctrl + T) for the most reliable experience.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
Nikolino,
Thank you for the comment, but it doesn't help. Unlocking those cells still means the users will be able to edit the data, and I don't want that. And it would have to be all the columns, not some of them. The users might well want to sort by the results of a summary column, that includes a funstion.
Guy
Hi, the tricky bit is that allowing Sort during protection is not enough by itself if Excel still sees locked cells in the sort range. In practice, the cells included in the sort usually need to be unlocked before protecting the sheet, and then you protect the sheet with Sort and AutoFilter allowed. If the table has formulas you do not want edited, one workaround is to protect formula columns separately and let users sort through filtered table headers.
Jamony,
I know it is tricky. What I don't understand is why is there a protection option to allow sort on a protected worksheet - but it doesn't work? If MIcrosoft can't make it work - remove the option. If they can make it work, make it work. But why leave an option that doesn't work?
All (at least most) the work arounds talk about protecting some cells, like formulas, and not others. That is fine - I know how to do that. In my case, I want all the data protected. A complex series of processes add data to a table in a spreadsheet. We want all staff to be able to see the data, but only a few select people to be able to edit. But we do want them to be able to explore the data by filtering (works) and sorting (doesn't work).
I can work around this - I spend my whole life working around Microsoft short comings. But wouldn't it be better if Microsoft just fixed the root problem for everyone!
Thank you for your comment.
Guy
