Permission & Access Issues

How to Sort Data in a Protected Excel Sheet with Locked Cells

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to sort a range of data within a protected Excel worksheet, but the presence of locked formula cells prevents the sorting operation.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Attempting to arrange or sort data within a protected worksheet where certain cells in the sort range are locked to prevent users from editing formulas.
Observed behavior: Excel allows data filtering on the protected worksheet but disables or blocks sorting, as rearranging the data would modify the locked formula cells.

Before you start

Before proceeding, ensure you have the password to unprotect the worksheet, as modifying the locked status of cells or structurally rearranging your data columns requires temporary full access.

Solution 1Recommended

Separate Formula Columns from Sortable Data

The safest and most permanent way to maintain protection on formulas while allowing users to sort inputs is to visually and structurally separate them.

Because sorting physically rearranges data, every cell included in the sort range must be unlocked. By separating the formulas from the input data, you can apply protection to the formulas while leaving the input data free to be sorted.

Unprotect the worksheet

Navigate to the Review tab on the ribbon and click 'Unprotect Sheet'. Enter your password if prompted.

Rearrange your columns

Move all input data (which needs sorting) to adjacent columns. Place all formula columns outside of this sortable range.

Unlock the input cells

Select only the input data cells that users will need to sort. Right-click, choose 'Format Cells', go to the 'Protection' tab, and uncheck the 'Locked' box.

Protect the sheet and enable sorting

Go back to the Review tab and click 'Protect Sheet'. In the permissions list, make sure to check 'Sort' before applying your password.

Why not use Allow Edit Ranges?: Using 'Allow Edit Ranges' is not a safe workaround for sorting locked formulas. While it allows sorting, it achieves this by unlocking the cells for specific users, which effectively grants them full permission to overwrite and break your formulas.

Solution 2

Use an Unprotected Helper Sheet

If you cannot change the layout of your current worksheet, use a separate unprotected sheet for data entry and sorting.

Solution 3

Temporarily Unprotect the Sheet to Sort

If sorting is an infrequent administrative task rather than a regular user action, simply remove the protection to perform the sort.

Manage Spreadsheet Protection with WPS Office

Easily Manage Cell Protection and Sorting in WPS Spreadsheet

WPS Spreadsheet offers intuitive tools to protect your sensitive formulas while easily granting users the permissions they need to sort and filter unlocked data ranges.

1. Open your spreadsheet: Launch WPS Spreadsheet and open the document you wish to configure.
2. Unlock the sortable data: Highlight the data range you want users to sort. Right-click, select 'Format Cells', switch to the 'Protection' tab, and uncheck 'Locked'.
3. Initiate sheet protection: Navigate to the 'Review' tab on the top ribbon and click the 'Protect Sheet' button.
4. Set sorting permissions: In the dialog box, scroll down the permissions list, check the box next to 'Sort', set your desired password, and click OK.

100% compatibility with Microsoft Excel (.xlsx) file formatsIntuitive cell formatting and sheet protection interfacesFree and lightweight alternative for complex data managementAdvanced sorting, filtering, and data validation capabilities

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel allow filtering but not sorting on locked cells?

Filtering only hides rows from view without changing the underlying data structure. Sorting physically rearranges the cell contents, which constitutes editing. Because locked cells are protected from being edited, Excel blocks the sorting action to prevent unauthorized modifications to your data and formulas.

Can I use 'Allow Edit Ranges' to fix the sorting issue?

No, 'Allow Edit Ranges' is not recommended for this scenario. While it successfully unlocks the cells to allow sorting for authorized users, it simultaneously grants those users full permission to edit, modify, and overwrite your protected formulas.

Is there a macro to sort protected sheets automatically?

Yes, you can use a VBA macro that triggers on a specific action (like clicking a button). The macro can temporarily unprotect the sheet using your password, sort the specified data range, and then immediately re-protect the sheet before the user makes any other changes.

Document & File Issues