Excel Protect Sheet: Allow Users to Filter and Sort

Yes. In Excel for Microsoft 365 on Windows, turn on the filter before protecting the worksheet, then choose Review > Protect Sheet and allow both Sort and Use AutoFilter. There is an important limitation: Excel generally cannot sort a protected range if any cells in the range being sorted are locked, even when Sort is allowed.

What sheet protection allows—and what it does not

Worksheet protection is a way to restrict changes to cells and certain sheet features. It is useful when people should be able to view or work with a prepared worksheet without changing its formulas, labels, or layout. Protection is configured per worksheet, so protecting one sheet does not automatically protect every sheet in the workbook.

The checkboxes in the Protect Sheet dialog specify which actions are permitted while protection is on. Checking Use AutoFilter lets users operate filter controls that are already present. Checking Sort permits sorting where Excel’s other requirements are satisfied. Neither option creates filter arrows or unlocks cells on its own.

Protection also is not the same as encrypting a workbook or securing sensitive information. It is intended to prevent ordinary edits, not to serve as a strong security boundary. If confidentiality matters, use appropriate file encryption and access controls rather than relying on sheet protection alone.

Prepare the filter before protecting the worksheet

Set up the data and filter controls before turning on protection. In Excel for Microsoft 365 on Windows, select a cell in the data range and choose Data > Filter. Excel adds dropdown arrows to the header row. An Excel table created with Insert > Table also has filter controls by default, unless that option has been turned off.

Check that the data is organized as a rectangular range: use one header row, keep each record on one row, and avoid blank rows or columns within the list. A consistent range helps Excel identify which records belong together when filtering or sorting. If the worksheet has multiple separate lists, apply a filter to the intended list rather than assuming one filter will control all of them.

Decide which cells users may edit. By default, cells are marked as locked, although that setting does not take effect until the sheet is protected. To unlock an editable range, select it, open Home > Format > Format Cells, choose the Protection tab, clear Locked, and select OK. On Windows, Ctrl+1 opens Format Cells; on Mac, use Command+1. Keep formulas and fixed labels locked if users should not change them—but consider the sorting limitation below before locking cells inside a range users need to sort.

Protect the sheet and permit filtering and sorting

Make sure the filter is already applied to the intended data range or table.
Choose Review > Protect Sheet.
Enter a password if you want to require one to unprotect the sheet. A password is optional; leaving it blank still enables the selected protection settings.
In the list of allowed actions, check Sort and Use AutoFilter. Leave other actions unchecked unless users need them.
Select OK. If you entered a password, Excel asks you to confirm it.
Test the sheet as a user would: open a filter dropdown, filter a column, and try sorting the intended range.

On a protected worksheet, users can use the existing filter arrows when Use AutoFilter is selected. They cannot add or remove the AutoFilter while the sheet remains protected, so make those changes before protection or temporarily unprotect the sheet. The exact dialog layout can vary slightly across Excel for Windows and Mac, but the relevant permissions are the Sort and AutoFilter options in the sheet-protection settings.

The key sorting limitation: locked cells in the sort range

The Sort checkbox grants permission to sort; it does not override cell locking. Excel’s protection rules can prevent a sort when the range being sorted contains locked cells. This is a frequent surprise when a sheet has protected formulas alongside records that users expect to rearrange. The permission may be checked correctly and the sort can still be unavailable or produce a protection-related message.

For example, imagine a sales list with columns for Region, Representative, Sales, and Commission. If users need to sort complete records by Sales, the sort range includes every column in each affected row. If the Commission cells are locked because they contain formulas, Excel may block sorting that range on a protected sheet. Unlocking only the Sales column is not necessarily sufficient: sorting records means moving rows across the whole range, not just changing the order of one column.

If users must sort protected-sheet data, plan for the entire range that may move. A common arrangement is to make the relevant data cells—including formula cells, if those formulas should travel with their records—unlocked, while protecting other parts of the sheet. This means users may also be able to edit those unlocked formula cells, so it is a trade-off rather than a perfect way to protect formulas and permit arbitrary sorting.

Test with the actual sort operation and range. Sorting only a single unlocked column can separate values from their associated records, so it is usually not a safe workaround for a multi-column list. If formula integrity is essential, consider keeping the source data protected and offering a separate, refreshable view or report that users can sort instead.

What users can do with AutoFilter on a protected sheet

When the sheet is protected with Use AutoFilter enabled, users can use the dropdowns that already exist to show or hide records based on a column’s values. For example, in a staff list they can filter the Department column to show only “Support,” then clear that filter to see all departments again. Filtering changes which rows are displayed; it does not edit the underlying cell values.

Users can apply filters to multiple columns, subject to the usual AutoFilter behavior. For instance, a date filter and a department filter can narrow a list together. They can also clear a filter using the column dropdown or the available clear-filter command. They cannot use sheet protection permissions to add a new filter range, remove the filter arrows, or change the worksheet’s structure while the sheet remains protected.

Filtering and sorting are separate actions. A user may be able to filter successfully but be unable to sort because locked cells are in the sort range. Conversely, a permitted sort does not automatically enable the filter dropdowns. Select both permissions when both tasks are needed, and test each one independently.

Choose a layout that fits the way people will use the data

There is no single protection setup that both makes every formula untouchable and lets users freely reorder any protected data range. Choose based on the task users actually need to perform:

Users only need to filter: Apply the filter first, protect the sheet, and allow Use AutoFilter. Lock formula cells if appropriate; sorting does not need to be enabled.
Users need to sort and edit the list: Allow Sort and make sure the cells in the sortable range are unlocked. Explain which cells they may edit, since unlocking them also removes that cell-level restriction.
Users need sorting but formulas must remain protected: Keep the formula-bearing source range protected and provide a separate output area or report for sorting. Verify that the design updates correctly when the source changes.
Only certain people should make structural changes: Keep the sheet protected for ordinary users and have an authorized editor unprotect it to change the filter range, layout, or protection settings.

For a shared workbook, label editable input cells clearly and explain how to clear filters. Otherwise, a user may mistake a filtered list for missing data or overwrite an unlocked formula cell. A small note above the table—such as “Use the header arrows to filter; clear filters to show all records”—can prevent confusion without granting extra permissions.

Common problems and how to diagnose them

The filter arrows are missing: Unprotect the sheet and apply Data > Filter or create the table first. Then protect the sheet again with Use AutoFilter checked.
The arrows appear, but clicking them does not work: Unprotect and check that Use AutoFilter is selected in the protection options. Also confirm that the user is clicking an existing filter control, not trying to create one.
Filtering works, but sorting is blocked: Check the Sort permission, then inspect the full range that Excel needs to move. Locked cells anywhere in that range may prevent sorting.
Sorting changes the wrong cells or separates related values: The selected sort range may not include all columns in the records. Sort the complete data range, with the header row identified correctly, rather than sorting one field independently.
Users can edit something they should not: Unprotect the sheet, select the cell, and review its Locked setting under Format Cells > Protection. Remember that locking has an effect only while sheet protection is active.
The protection password is unavailable: Do not assume that a forgotten password can be recovered through the protection dialog. Keep passwords documented and workbook copies managed according to your organization’s process.

When changing settings, unprotect the worksheet from Review > Unprotect Sheet, adjust the cells or filter, and protect it again with the intended options. If a password was set, Excel requires it to unprotect the sheet. Before making broad changes to a business-critical workbook, save a separate copy so the original layout and data remain available.

Frequently Asked Questions

Can I let users filter a protected sheet but not sort it?
Yes. Apply the filter before protection, select Use AutoFilter, and leave Sort unchecked. Users can use the existing dropdowns to filter the list, but they will not have permission to sort through the sheet-protection settings.

Why does Excel still block sorting when Sort is checked?
The permission does not make locked cells sortable. If the range Excel must reorder contains locked cells, the sort can be blocked. Review the full record range, not just the column used as the sort key, and test whether the intended sort works after protection is applied.

Can users add filter arrows after I protect the sheet?
No. Set up the AutoFilter or table before protecting the worksheet. The Use AutoFilter option lets users operate existing filter controls; it does not let them create or remove the filter while protection is active.

Does protecting a sheet protect the whole workbook?
No. Sheet protection applies to one worksheet’s cells and permitted actions. Workbook-structure protection is a separate feature, and neither should be treated as a substitute for encrypting a file that contains confidential information.

Can I protect formulas and still let people sort the rows?
Sometimes the practical solution is to keep the source protected and provide a separate sortable view. Sorting a range that contains locked formula cells may be blocked, while unlocking those cells can allow users to edit them. Choose and test a layout that balances formula integrity with the sorting task.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.