How to Allow Sorting While Protecting Excel Formula Cells
Question details
The user needs to sort data in a protected worksheet without exposing formula cells to unintended modifications.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Securing a worksheet layout while maintaining data sorting functionality for end-users.
- Observed behavior
- Excel's default protection settings restrict sorting if the selected range includes locked cells, preventing standard data manipulation.
Before protecting your worksheet, ensure your dataset is organized continuously without blank rows or columns to prevent sorting errors.
Configure Cell Locking and Sheet Protection
Selectively unlock data entry cells and lock only the formula cells before applying sheet protection with sorting enabled.
Excel typically cannot sort a protected range that contains locked cells. By unlocking input cells and locking only formulas, then explicitly permitting sorting during sheet protection, you can maintain functionality while securing calculations.
Select your entire dataset, right-click, choose 'Format Cells', go to the 'Protection' tab, and uncheck the 'Locked' box. Click OK.
Select only the columns or cells containing formulas. Right-click, choose 'Format Cells', go back to the 'Protection' tab, and check the 'Locked' box.
Go to the 'Review' tab on the ribbon and click 'Protect Sheet'.
In the 'Protect Sheet' dialog, ensure a password is set (optional but recommended). Under 'Allow all users of this worksheet to:', check the boxes for 'Select unlocked cells', 'Sort', and 'Use AutoFilter'. Click OK.

Use a VBA Macro to Temporarily Unprotect and Sort
Create a macro that unprotects the sheet, performs the sort operation, and reprotects the sheet automatically.
Protect and Sort Your Worksheets Easily in WPS Spreadsheet
WPS Office provides an intuitive interface for managing cell locking and sheet protection. You can easily secure your formulas while allowing team members to sort and filter data without breaking calculations.
- 1. Open Your File in WPS: Launch WPS Spreadsheet and open your existing .xlsx workbook.
- 2. Adjust Cell Protection: Select your data, right-click, and choose 'Format Cells'. Uncheck 'Locked' for data entry cells and check it only for formula cells.
- 3. Protect and Allow Sorting: Navigate to the 'Review' tab, click 'Protect Sheet', and check 'Sort' and 'Use AutoFilter' before confirming the protection password.

Frequently Asked Questions
Why do I get an error when trying to sort a protected sheet even though 'Sort' is checked?
If the range you are trying to sort contains locked cells (like formulas), Excel will block the sorting action by default, even if 'Sort' is allowed in the protection settings. You must ensure the cells being sorted are unlocked, or use a VBA macro.
How do I quickly select all cells with formulas to lock them?
Press Ctrl + G to open the 'Go To' dialog, click 'Special...', select 'Formulas', and click OK. Once highlighted, you can right-click any of them, go to 'Format Cells', and check the 'Locked' box.
Can I allow users to use AutoFilter without allowing them to edit the data?
Yes. When you protect the sheet, simply leave the cells locked but check the 'Use AutoFilter' box in the Protect Sheet dialog. Users will be able to filter the data using existing dropdowns, but they won't be able to edit the cell contents.
Does WPS Office handle sheet protection differently than Excel?
No, WPS Office handles sheet protection and cell locking in a highly compatible manner with Microsoft Excel. Workbooks protected in Excel will maintain their settings in WPS, and vice versa.




