Forum Discussion
How to Enable Sorting while Protecting Formulae
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.