Excel Protect Sheet: Allow Filtering

Yes. In Excel for Microsoft 365 on Windows, turn on AutoFilter before protecting the worksheet, then allow Use AutoFilter in the Protect Sheet options. People can then change filter criteria on the existing filter without being allowed to edit protected cells.

What filtering on a protected sheet means

Worksheet protection controls what users may change; it does not automatically disable every way of viewing or organizing data. If an existing AutoFilter is available and the protection setting permits its use, someone can open a column’s filter menu and narrow the visible rows. For example, a protected sales list can let a colleague display only orders marked Pending while preventing changes to the order amounts or customer names.

The key word is existing. The filter should be switched on before you protect the sheet. The permission does not give users the ability to add or remove AutoFilter buttons after protection is on. It allows interaction with the filter already applied to the data range. Filtering hides rows from view; it does not delete those rows or change the underlying values.

This is useful for shared reports, trackers, and reference lists where readers need to inspect a subset of records but should not alter the source data. It is also distinct from protecting a workbook’s structure, which controls actions such as adding, deleting, or moving worksheets.

Set up the filter before protecting the sheet

In desktop Excel for Microsoft 365 on Windows, prepare the data and filter first, then choose the permissions when you protect the worksheet:

Make sure the data has a single header row and consistent columns. Select a cell in the list, or select the intended range.
On the ribbon, choose Data > Filter. Filter arrows should appear in the header row. If your data is an Excel table, it normally has filter buttons already; check that they are visible and working.
Open Review > Protect Sheet.
In the Protect Sheet dialog, choose whether to set a password. Under the actions users are allowed to perform, select Use AutoFilter.
Leave or adjust the other permissions deliberately. For example, you can allow selecting unlocked cells while leaving locked cells protected.
Select OK, confirm the password if Excel asks, and test the filter as a user would.

Protection is applied to the active worksheet, not automatically to every sheet in the workbook. Repeat the setup for each sheet that needs the same behavior. If the data range changes later, check that the filter still covers the intended columns and rows; a protected sheet may prevent users from changing the filter setup themselves.

What users can and cannot do

With the right permission, a user can open an existing filter dropdown and select or clear criteria, such as showing one department or a date range. The user can usually clear the current filter to display all the rows again. These actions change which rows are displayed, not the protected cell contents.

That permission is narrower than general editing access. It does not, by itself, allow users to:

Change values in locked cells or formulas in locked cells.
Add or remove AutoFilter controls while the sheet is protected.
Insert new data rows, delete records, or change the worksheet layout unless separate permissions and cell settings allow those actions.
Make every kind of sort work. Sorting has its own permission and can be constrained by locked cells.

Users may still be able to select cells depending on the Select locked cells and Select unlocked cells options. Selecting a cell is not the same as being allowed to edit it. If users should interact only with filter menus, consider limiting selection thoughtfully, but test the actual workflow because selection behavior can affect usability.

Filtering is not the same as sorting

Filtering and sorting are often confused because both are accessed from the Data tab and both change how a list appears. Filtering shows records that meet a condition and hides the others temporarily. Sorting rearranges the order of records. On a protected sheet, Use AutoFilter and Sort are separate permissions in the Protect Sheet dialog.

Enabling Use AutoFilter does not mean sorting is allowed. If users need to sort, select Sort as well, then test it with the actual protected range. Excel can block a sort when cells in the range being sorted are locked, even if the Sort permission was selected. A common design is to unlock the relevant input or data range before protecting the sheet, while keeping formulas and headings locked. However, unlocking cells lets users edit them, so it may not suit a report where all data must remain unchanged.

For a read-only report where users only need to see categories or periods, filtering may be enough. If users must reorder records, test sorting on a copy of the workbook with the exact cell-locking arrangement you plan to use.

Locked cells and safe worksheet design

Excel marks cells as locked by default, but that setting does not prevent editing until sheet protection is turned on. This means you can choose which cells remain protected before applying protection. For example, a form might keep labels and formulas locked while allowing users to enter information in designated input cells.

To unlock selected cells in desktop Excel, select them, open Home > Format > Format Cells, go to the Protection tab, clear Locked, and select OK. Then protect the sheet. This is appropriate when users need to type into those cells; it is not necessary just to let them filter the list. If preserving every record is important, keep the data cells locked and grant only the filter permission needed.

Be careful with formulas and calculated columns. If a formula cell is unlocked, sheet protection will not prevent a user from changing or replacing that formula. Check the Locked setting for sensitive cells before protecting the sheet. Hidden formulas are a separate display option and should not be treated as a substitute for protection.

Common problems and how to fix them

The filter arrows are missing. Unprotect the sheet, select a cell in the data, and use Data > Filter. Protect it again with Use AutoFilter selected. If the data is a table, check its filter button setting as well.
The dropdown appears, but its options cannot be changed. Unprotect the worksheet and review the Protect Sheet permissions. Confirm Use AutoFilter is selected, then apply protection again. Make sure you are changing criteria in the existing dropdown rather than trying to add a new filter.
The permission was selected, but nothing seems to happen. Test with a filter that should visibly exclude records, such as selecting one status from a column containing several statuses. Check for another active filter, a blank or unusual header row, or a range that does not include all the records.
New records are not included. The filter may cover only the original range. If possible, use an Excel table, which is designed to expand as data is added, and verify the filter range after changes. You may need to unprotect the sheet to adjust the data structure.
Sorting is blocked even though filtering works. Select the separate Sort permission and check whether locked cells in the sort range prevent rearrangement. Filtering permission does not override those restrictions.
You cannot remember the protection password. Excel does not provide a normal password-recovery route for protected sheets. Keep a secure record of passwords and retain an unprotected backup when appropriate.

When diagnosing a problem, unprotect the sheet first if you are authorized to do so, inspect the filter and range, then protect it again and retest. Changing the data layout while protected can be restricted, and repeatedly toggling protection without checking the options can leave the sheet in a different state than intended.

Protection limits and security context

Protect Sheet is a control for preventing accidental or ordinary edits to worksheet contents and layout. It is not a strong confidentiality or access-control boundary for sensitive information. People who can open a workbook may be able to copy visible data, and worksheet protection should not be mistaken for file encryption or a secure way to conceal records.

Use file-level protection and appropriate storage permissions when the goal is to restrict who can open a workbook. In Microsoft 365 desktop Excel, file encryption is available through File > Info > Protect Workbook > Encrypt with Password; availability and organizational policies can vary. A file password has different consequences from a sheet password, so store it safely. Do not put confidential records in a sheet merely because it is protected or because a filter currently hides them.

Also remember that sheet protection is configured per worksheet. Protecting the sheet that contains a report does not protect other sheets or the workbook’s structure. Use the corresponding workbook-structure protection option separately if preventing worksheet additions, deletions, or rearrangement is part of the requirement.

Excel for Mac and Excel for the web

In Excel for Mac, the general approach is the same: create or enable the filter, then use the Review tab’s sheet-protection command and allow AutoFilter if that option is shown. Dialog wording and placement can differ between Mac releases, so look for the specific AutoFilter permission rather than assuming that enabling sheet protection automatically permits filtering.

Excel for the web has a different interface and may not expose every protection option in the same way as desktop Excel. If you cannot find a setting to allow filtering, configure the sheet in the desktop application, then reopen it in the web app and verify the behavior. Collaborators’ ability to edit can also depend on sharing permissions, which are separate from the worksheet’s protection settings.

Frequently Asked Questions

Can users filter a protected Excel sheet without a password?
Yes, if the sheet is protected with Use AutoFilter allowed. A password is optional when applying worksheet protection, though it can help prevent others from changing the protection settings. Filtering itself does not require users to know the protection password.

Do I have to unlock the cells to let people filter?
No. Keep data cells locked if users should not edit them. Enable the existing AutoFilter before protection and select Use AutoFilter in the protection options. Unlock cells only when users need to enter or change their contents.

Can users turn off the filter on a protected sheet?
They can change or clear criteria in an existing AutoFilter when the permission is enabled. They generally cannot remove or create the filter controls while the worksheet remains protected; unprotect the sheet to change the filter setup.

Why does filtering work but sorting does not?
AutoFilter and Sort are separate permissions. Sorting can also be blocked by locked cells in the range. Allow Sort if needed, then test the exact layout; enabling it does not guarantee every protected range can be sorted.

Does filtering delete the hidden rows?
No. A filter hides rows that do not match the chosen criteria; clearing the filter displays them again. It does not erase the records or make them confidential, so do not use filtering as a way to secure sensitive data.

A reliable setup to remember

For the usual case, enable Data > Filter before protection, choose Review > Protect Sheet, and allow Use AutoFilter. Keep cells locked unless users need to edit them, add Sort only if the workflow requires it, and test with a realistic filter after protection is applied. That combination lets people explore a protected list without confusing display controls with permission to change its contents.

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.