Permission & Access Issues

How to Sort and Filter Locked Columns in a Protected Excel Worksheet

Aamir Naveed Akram Oct 10, 2026 870 views

Question details

The user needs to enable sorting and filtering capabilities on an Excel worksheet that is protected and contains locked columns or formula cells.

How to Sort and Filter Locked Columns in a Protected Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Managing data in a protected spreadsheet to prevent unauthorized edits while still allowing users to sort data alphabetically and filter specific rows.
Observed behavior: Worksheet protection blocks users from sorting or filtering data, throwing an error when attempting to sort alphabetically, even if some cells are configured as an editable range.

Before you start

Ensure you have the password to unprotect the worksheet, as you will need to temporarily remove the protection to adjust the specific sorting and filtering permissions.

Solution 1Recommended

Enable Sort and AutoFilter Permissions in Protect Sheet Settings

Adjust the worksheet protection settings to explicitly allow users to sort and use AutoFilter on both locked and unlocked cells.

By default, protecting a worksheet disables all sorting and filtering functions. You must explicitly grant these permissions during the protection process. Additionally, filters must be applied before the sheet is protected.

Unprotect the Worksheet

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

Apply Filters to Headers

Select your data headers, go to the 'Data' tab, and click 'Filter' so the drop-down arrows appear. (You cannot add new filters once the sheet is protected).

Configure Protection Permissions

Go back to the 'Review' tab and click 'Protect Sheet'. Scroll down the 'Allow all users of this worksheet to:' list.

Check Sort and Filter Options

Check the boxes for 'Sort' and 'Use AutoFilter'. Enter a protection password, click 'OK', and re-enter the password to confirm.

Enable Sort and AutoFilter Permissions in Protect Sheet Settings

Filter Prerequisite: The 'Use AutoFilter' permission only allows users to interact with existing filters. Always apply the data filters to your columns before protecting the worksheet.

Solution 2

Configure Specific Editable Ranges Before Protection

Define explicit editable ranges for specific users before locking the rest of the worksheet to prevent sorting errors on partially locked rows.

Seamless Spreadsheet Management

Sort and Protect Worksheets Effortlessly with WPS Spreadsheet

WPS Spreadsheet provides intuitive worksheet protection features, allowing you to easily lock formula cells while granting specific permissions like sorting and filtering to other users in a few clicks.

1. Open your file: Launch WPS Office and open your spreadsheet document.
2. Apply Filters: Select your header row, navigate to the 'Data' tab, and click 'Filter' to activate the drop-down menus.
3. Access Protection Settings: Navigate to the 'Review' tab and click 'Protect Sheet'.
4. Enable Permissions: Check the boxes for 'Sort' and 'Use AutoFilter', set your password, and click 'OK' to secure the document while maintaining data flexibility.

Fully compatible with Microsoft Excel (.xlsx) formatsGranular control over worksheet permissions and editable rangesEasily lock formula cells while allowing data filteringFree and lightweight alternative to heavy spreadsheet tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does sorting produce an error even when I checked the 'Sort' permission on a protected sheet?

Sorting on a protected sheet requires all cells within the active sort range to be unlocked. If locked formula cells are mixed into the rows you are trying to sort, the application will block the action to prevent rearranging locked references. You must unlock the specific range before sorting.

Can I add a new filter to a worksheet after it is protected?

No, you cannot. The 'Use AutoFilter' permission only allows users to interact with drop-down filters that were already applied before the worksheet was protected. You must apply the filters to your headers first, and then protect the sheet.

How do I lock only the formula cells and leave the rest of the sheet editable for sorting?

First, select the entire sheet, right-click, choose 'Format Cells', go to the 'Protection' tab, and uncheck 'Locked'. Next, use 'Find & Select' > 'Go To Special' > 'Formulas' to select only the formula cells. Format these specific cells to be 'Locked', and then protect the worksheet while checking the sort and filter permissions.

Document & File Issues