Forum Discussion
How to Enable Sorting while Protecting Formulae
I have a workbook which lists people on 4 matching tabs (let's say: Potential, Accepted, Not Accepted, Completed). There are some columns with formulae that I want protected from damage by less-knowledgeable users, who are responsible for updating these lists, but those users need to be able to sort the data as well.
The columns with formulae are within the range that needs to be sorted. Moving them to the edges (where the formulae would still function even if not part of the Sort) makes the data MUCH less useful, and not an option.
When I lock those columns/cells and protect the sheets, even though I have granted permission to Sort when I enabled protection, the users are prevented from sorting, I assume because the sort range contains locked cells:
Is there anyway around this?
3 Replies
Here a solution with out VBA but with some limitations.
Without VBA, Excel won't allow sorting a range that includes locked (protected) cells — even if you enable sorting in sheet protection.
Unlock All Cells First
Press Ctrl + A to select the whole sheet.
Right-click → Format Cells → Protection tab → Uncheck "Locked" → Click OK.
Lock Only Formula Cells
Select only the formula column or formula cells.
Right-click → Format Cells → Protection → Check "Locked" → Click OK.
Turn on Sheet Protection
Go to the Review tab → Click Protect Sheet.
Enter a password (optional).
Check these options:
“Sort”
“Use AutoFilter”
“Select unlocked cells”
Click OK.
Your formulas are safe and protected
Users can sort freely
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.
Thanks. Your first two sentences tell me what I want Excel to do - it won't do. Which is frustrating.
Not sure why you went on in detail describing what I've already done... LoL.
According to the message (in your first post), you should unlock the worksheet first before locking any cells and then lock the worksheet again.
A little more information, such as your Excel version, operating system, and storage medium, would be helpful in finding a suitable solution.
Thank you for your patience and understanding.
