Office Settings & Configuration

How to Disable Sorting While Keeping Filters Enabled in Excel

John Wilson Oct 10, 2026 869 views

Question details

The user needs to prevent users from sorting rows in a shared worksheet while still allowing them to use drop-down filters and input data into specific cells.

How to Disable Excel Sorting While Keeping Filters Enabled

Product: Microsoft Excel
Device & OS: not provided
Scenario: Sharing an Excel worksheet for collaborative data entry where the original row order must be preserved, but users still need the flexibility to filter the view.
Observed behavior: Standard worksheet protection often restricts users from entering data or using filters entirely unless specific cell locking and granular protection settings are configured beforehand.

Before you start

Before proceeding, ensure your filter drop-downs are already set up and configured, as you cannot add or remove auto-filters once the worksheet is protected.

Solution 1Recommended

Use Worksheet Protection to Allow Filtering and Block Sorting

Unlock the cells designated for user data entry, then apply worksheet protection that explicitly allows filtering but leaves sorting disabled.

By default, all cells in Excel are locked. To allow data entry while restricting structural changes like sorting, you must first unlock the input cells and then apply custom worksheet protection.

Unlock editable cells

Select the cells that users need to edit or input data into. Right-click the selection and choose 'Format Cells'. Go to the 'Protection' tab, uncheck the 'Locked' box, and click 'OK'.

Enable AutoFilter

Select your column headers. Navigate to the 'Data' tab on the ribbon and click 'Filter' to activate the drop-down arrows.

Protect the sheet with specific permissions

Navigate to the 'Review' tab and click 'Protect Sheet'. In the dialog box, enter an optional password.

Configure sorting and filtering rules

Scroll through the 'Allow all users of this worksheet to' list. Check the box for 'Use AutoFilter' and ensure the box for 'Sort' remains unchecked. Click 'OK' to apply the protection.

Use Worksheet Protection to Allow Filtering and Block Sorting

Test Your Protection: After clicking OK, try to sort a column. The sort options should be grayed out, while the filter options and data entry in unlocked cells remain functional.

Solution 2

Apply Protection Using a VBA Macro

Use a VBA script to precisely control worksheet protection parameters, enabling filters and data entry while strictly disabling sorting.

Manage Worksheet Protection in WPS Office

Easily Protect Sheets and Manage Filters in WPS Spreadsheet

WPS Spreadsheet provides a highly intuitive interface for managing worksheet protection. You can seamlessly unlock specific cells, enable auto-filters, and restrict sorting in shared documents just as you would in Microsoft Excel.

1. Apply Filters: Open your spreadsheet in WPS Office. Highlight your header row, navigate to the 'Data' tab, and click 'AutoFilter'.
2. Unlock data entry cells: Select the cells you want users to edit, right-click and select 'Format Cells'. In the Protection tab, uncheck 'Locked' and confirm.
3. Access Sheet Protection: Go to the 'Review' tab on the ribbon and click on 'Protect Sheet'.
4. Set permissions: In the protection settings, verify that 'Use AutoFilter' is checked and 'Sort' is unchecked, then click 'OK'.

Free and lightweight office suiteFully compatible with Microsoft Excel (.xlsx, .xls) formatsIntuitive Review tab for granular sheet protectionFamiliar user interface makes the transition seamless

microsoft office alternative - wps office

Frequently Asked Questions

Can I disable sorting without protecting the worksheet?

No, Excel does not have a built-in feature or VBA property to selectively disable sorting without applying overall worksheet protection. Protection must be enabled to restrict specific structural actions like sorting.

Why can't users type in the filtered data cells after I protected the sheet?

By default, all cells in an Excel worksheet have a 'Locked' status. When you protect the sheet, this locked status is enforced. You must specifically select the cells you want users to edit, go to Format Cells, and uncheck 'Locked' before you activate sheet protection.

Will this method work on Excel for the Web?

Excel for the Web has limited support for granular protection settings. Advanced protection configurations, especially those applied via VBA macros, might not function identically in the browser version. It is best to set up and manage these restrictions in the desktop application.

Can I add new filters after the sheet is protected?

No, you must set up your header filters before applying worksheet protection. Once the sheet is protected, users can interact with the existing drop-downs, but they cannot apply the AutoFilter feature to new ranges.

Office App Problems