Permission & Access Issues

How to Allow Filtering but Prevent Sorting in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to allow others to filter an Excel table without granting them the ability to sort the data.

Product: Excel
Device & OS: not provided
Scenario: Sharing an Excel file with trainers where data integrity must be maintained, such as ensuring swimmers' times remain assigned to the correct rows.
Observed behavior: Unrestricted independent column sorting can misalign rows and assign incorrect data, so sorting must be restricted while filtering remains accessible.

Before you start

Make sure you apply the AutoFilter to your column headers before turning on worksheet protection, as you cannot add new filter dropdowns once the sheet is locked.

Solution 1Recommended

Use the Protect Worksheet Feature

By locking the worksheet with specific permissions, you can allow users to use existing filters while disabling their ability to sort data.

Excel's sheet protection allows you to granularly control what actions users can perform. By allowing the 'Use AutoFilter' option and leaving the 'Sort' option unchecked, you safeguard your row integrity.

Apply AutoFilter

Select your data headers, go to the 'Data' tab on the ribbon, and click 'Filter' so the dropdown arrows appear on your columns.

Open Protection Settings

Navigate to the 'Review' tab on the ribbon and click on 'Protect Sheet'.

Configure Permissions

In the Protect Sheet dialog box, scroll down the list of permissions. Check the box for 'Use AutoFilter' and ensure the box for 'Sort' remains unchecked.

Set Password and Confirm

Enter a password to prevent unauthorized users from removing the protection, click 'OK', re-enter the password to confirm, and click 'OK' again.

Testing Permissions: It is highly recommended to test the protected sheet yourself or using a non-owner account to verify that sorting is disabled while filtering works perfectly.

Secure Your Spreadsheets with WPS

Protect and Manage Spreadsheet Permissions in WPS Office

You can easily set up customized permissions, including allowing filtering while restricting sorting, using WPS Spreadsheet. It provides a robust, user-friendly interface to protect your sensitive data without compromising collaboration.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing the data you want to protect.
2. Add Filters: Select your column headers, go to the 'Data' tab, and click 'AutoFilter'.
3. Protect the Sheet: Go to the 'Review' tab and click 'Protect Sheet' to open the permissions dialog.
4. Set Custom Permissions: Check 'Use AutoFilter', uncheck 'Sort', enter your desired password, and click 'OK' to secure the document.

Easily allow filtering while restricting sorting with granular sheet protection.Fully compatible with Microsoft Excel (.xlsx) file formats and permission structures.Lightweight software that runs smoothly on various devices.Free and comprehensive Office suite for all your daily document needs.

Frequently Asked Questions

Why is the AutoFilter option grayed out or missing after protecting the sheet?

If you did not apply the filter to the data headers before protecting the worksheet, the filter dropdowns will not appear. You must apply the filter from the Data tab before enabling sheet protection.

Can I allow sorting for only specific columns in Excel?

No, Excel's worksheet protection applies the sorting restriction to the entire protected sheet. You cannot allow sorting on one column while restricting it on another within the same locked sheet.

How do I remove the sorting restriction to update the data later?

To remove the restriction, go to the 'Review' tab and click 'Unprotect Sheet'. You will need to enter the password if one was set during the initial protection setup.

Document & File Issues