Excel Protect Sheet: Allow Filtering and Sorting Safely

Yes. In Excel for Microsoft 365 on Windows, turn on the filter for your data before protecting the sheet, then choose Review > Protect Sheet and allow Sort and Use AutoFilter. Filtering can then work on the existing filter, but sorting has an important limitation: the cells in the range being sorted must be unlocked.

What sheet protection changes

Worksheet protection is designed to prevent accidental changes to cells, formatting, and worksheet structure. It does not make a workbook confidential or securely encrypt its contents. A protected sheet can still let people carry out selected tasks, such as filtering, sorting, selecting cells, or entering data in unlocked cells. The checkboxes in the Protect Sheet dialog define which actions remain available.

By default, Excel marks cells as locked, but that setting has no effect until you protect the worksheet. When protection is enabled, locked cells are protected against editing, while unlocked cells remain editable. The permissions to use AutoFilter and sort are separate from this cell-locking behavior, so enabling a permission does not automatically make every related action possible.

Protect Sheet can use a password, but a password is optional. Without one, a user can remove protection through the worksheet command. A password helps discourage changes to the sheet’s layout; it should not be treated as a way to secure sensitive information. Use file-level encryption and appropriate access controls when confidentiality matters.

Set up filtering before protection

Excel lets people use an AutoFilter that is already in place on a protected sheet, but they cannot turn AutoFilter on or off while the sheet is protected. Set it up first:

Arrange the records in a consistent list with one header row. Give each column a distinct, nonblank heading, and avoid blank rows or columns splitting the list.
Select a cell in the data range. On the ribbon, choose Data > Filter. Excel adds filter arrows to the header row. For a regular range, you can also use the Windows shortcut Ctrl+Shift+L.
Check that the arrows appear on the columns people need to filter. Set any starting filter criteria before protection if you want the worksheet to open with a particular subset displayed.
Choose Review > Protect Sheet, select Use AutoFilter in the permissions list, and confirm the other permissions you want. Add a password only if appropriate, then confirm it if Excel asks.

When the sheet is protected, users can open the existing dropdowns and change criteria, such as showing only the East region or dates in a selected period. They cannot add a filter to a different range, remove the arrows, or change the filter’s underlying range until protection is removed. If the filter arrows are missing after protection, unprotect the sheet, apply the filter, and protect it again with the AutoFilter permission selected.

Allow sorting and understand its limitation

In the Protect Sheet dialog, select Sort to permit sorting. That permission is necessary, but it may not be sufficient: Excel does not sort a protected range when the cells in the range being sorted are locked. This is the point that often surprises people. A user can have permission to sort and still see a message that the protected cells would be changed.

To prepare a range for sorting, select the cells that will be included in the sort and open Format Cells with Ctrl+1 on Windows. On the Protection tab, clear Locked, then protect the sheet with Sort selected. Include the complete data range that users will sort, and test the sort you expect them to use. If the sort range includes locked cells, Excel can refuse the operation even though Sort is checked.

Unlocking those cells means users can edit them while the sheet is protected, so it involves a trade-off. You can limit which cells users may select using the selection options in the Protect Sheet dialog, but selection settings do not make unlocked cells read-only. If people must be able to reorder records but must never change their values, normal worksheet protection may not satisfy both requirements at once. Consider a controlled macro, a separate working copy, or another workflow, and test it on a copy before relying on it.

Filtering and sorting are different actions

Filtering hides rows that do not match chosen criteria; it does not rearrange or delete those records. Sorting changes the order of records according to one or more columns. For example, filtering a sales list to show only the West region leaves other rows hidden, while sorting the visible list by sales amount changes the displayed order. A user may need both permissions, but checking one does not check the other.

A filter can often be used on a protected sheet even when the filtered cells are locked, provided the AutoFilter was created beforehand and Use AutoFilter is allowed. Sorting has the extra unlocked-cell restriction. This explains why a protected list can offer working filter arrows while its sort command fails. It is not evidence that the AutoFilter permission is broken; the two operations have different requirements.

Also distinguish worksheet sorting from sorting a separate result. A person may be able to sort data in an unprotected copy or use a formula-driven output elsewhere, while the protected source stays unchanged. That may be preferable when the source data must remain fixed, although a formula-based approach depends on the Excel version and the workbook’s design.

Choose permissions for the worksheet’s purpose

Before protecting a sheet, decide what people should be able to do rather than checking every permission by default. A simple read-and-filter sheet needs fewer permissions than a shared data-entry sheet. In the Protect Sheet dialog, permissions can include selecting locked or unlocked cells, formatting cells, inserting or deleting rows, sorting, and using AutoFilter. Availability of some actions may depend on the workbook’s contents and Excel version.

For a view-only list: allow selection as needed and Use AutoFilter if users need to narrow the displayed records. Leave Sort off if changing row order is not useful or if the required range cannot be unlocked.
For a sortable list: allow Sort and prepare the complete sortable range by unlocking its cells. Decide whether the resulting ability to edit those cells is acceptable.
For data entry: unlock only the input cells, keep formula cells locked, and allow selection of unlocked cells. Filtering may still be useful, but test how it affects the entry workflow.
For a report: use a separate, unprotected analysis area or a PivotTable when users need more freedom than the protected source can safely provide.

Excel’s cell-locking setting is per cell, while many protection permissions apply to the worksheet as a whole. That distinction matters: unlocking one range does not grant users permission to format every cell, but it does allow editing in that unlocked range when the sheet is protected.

Common problems and practical fixes

The filter arrows disappear. Confirm that AutoFilter was applied before protection. Unprotect the sheet, select the list, choose Data > Filter, and protect it again with Use AutoFilter selected. If a filter was applied to only part of the list, correct the range while the sheet is unprotected.

The filter arrows are visible, but the criteria cannot be changed. Check that Use AutoFilter was selected when protection was applied. If the wrong options were chosen, use Review > Unprotect Sheet, then protect the sheet again with the desired permissions. Excel may ask for the password used to protect it.

Sort is checked, but sorting is blocked. Inspect the entire range Excel is being asked to sort. Locked cells within that range can prevent the sort. Unprotect the sheet, unlock the intended range through Format Cells, and protect it again. Also verify that the selection includes complete rows of records; sorting just one column can separate values from their associated records.

Some records move without their related details. Select a cell in the full list and use the sort controls on the intended column, rather than sorting a single column by itself. Keep each record in one row, use consistent headers, and review Excel’s sort-range prompt carefully if it appears. A sort should reorder complete records together.

The password is unknown. A password-protected sheet cannot be unprotected through the ordinary command without the password. Contact the workbook owner or use an authorized copy. Do not assume worksheet protection is a recovery or security system; keep appropriate source copies and manage access to the file.

Excel for Mac and Excel for the web

In Excel for Mac, the worksheet protection command is also on the Review tab, and the protection dialog provides worksheet permissions. The cell-format dialog is opened with Command+1 rather than Windows Ctrl+1. Ribbon arrangement and dialog wording can vary slightly between releases, so look for Protect Sheet and the Sort and AutoFilter permissions.

Excel for the web also supports sheet protection, but its interface and available controls can differ from the Windows desktop dialog. If a particular permission is missing or the range does not behave as expected, open the workbook in the desktop app and configure protection there. For any platform, test the finished sheet using a non-owner account or a copy of the workbook; the owner may be able to access controls that ordinary users cannot.

Alternatives when protected sorting is too restrictive

If sorting must be available without making source cells editable, keep the protected source separate from the area users manipulate. One option is to provide an unprotected copy of the records for analysis while retaining a protected original. Another is to build a report or PivotTable from the source and let users sort or filter the report instead of changing the source list. These approaches add maintenance considerations: a copied list can become stale, and reports may need refreshing when source data changes.

In Microsoft 365, dynamic array functions such as SORT and FILTER can return a rearranged or filtered view in another area of a workbook. They require a suitable formula layout and may not fit every workbook, particularly if the source range changes or users need to edit the results. They are alternatives for displaying data, not switches that remove the limitations of worksheet protection. Keep the source protected and test the output in the Excel versions your audience uses.

Frequently Asked Questions

Can I allow filtering but stop users from sorting?
Yes. Apply AutoFilter before protecting the sheet, select Use AutoFilter, and leave Sort unchecked. Users can change existing filter criteria but cannot use the worksheet’s permitted sort action.

Can users filter a protected sheet if the cells are locked?
Usually, yes, if the AutoFilter already exists and Use AutoFilter is allowed. Locked cells are a particular obstacle for sorting; they do not by themselves prevent users from changing criteria in an existing filter.

Why does Excel say I cannot sort a protected sheet?
Check both conditions: Sort must be enabled in the protection permissions, and the cells in the range being sorted must be unlocked. If either condition is unmet, Excel can block the operation.

Can I add filter arrows after protecting the worksheet?
No. Set up AutoFilter while the sheet is unprotected. Afterward, users can use its existing arrows if Use AutoFilter is permitted, but they cannot turn the feature on or off while protection remains active.

Does protecting a sheet protect the workbook’s data from being viewed?
No. Worksheet protection restricts selected editing actions; it is not encryption and does not hide the workbook’s contents from someone who can open the file. Use file encryption and access controls for confidential information.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.