Protect Sheet: Allow Filtering in Excel

Yes. In Excel for Microsoft 365 on Windows, turn on filtering before you protect the worksheet, then select Use AutoFilter in the Protect Sheet permissions. People can then change the filter criteria using the existing drop-down arrows, while locked cells remain protected from editing.

What “allow filtering” means

Excel’s worksheet protection is controlled by permissions. When you protect a sheet, you can allow certain actions that would otherwise be restricted, such as selecting cells, formatting, inserting rows, or using an existing AutoFilter. The relevant permission is called Use AutoFilter in the Protect Sheet dialog.

It does not mean that users can freely change the worksheet or create any filter they want. They can use filter arrows that are already present to show or hide rows according to criteria. For example, a user could filter a protected sales list to show only orders from a particular region, or only records marked “Open.” Filtering changes which rows are visible; it does not delete the rows or change the values in them.

The key distinction is that the filter must already be in place when the sheet is protected. The permission allows users to operate that filter, not add or remove the AutoFilter feature after protection.

Set up the filter before protecting the sheet

First make sure the data has a clear header row, with one label per column and no blank header cells. Click a cell in the data range, then choose Data > Filter. Excel adds a drop-down arrow to each header. If the data is an Excel table, its header row normally has filter arrows already.

On Windows, Ctrl+Shift+L toggles AutoFilter on or off for a selected data range. Because shortcuts can differ by keyboard and Excel for Mac version, use Data > Filter on a Mac if you are unsure of the shortcut. Check that the arrows appear and that the range includes the records you want people to filter. A filter applied to only part of a list can produce confusing results.

Before protecting, consider which cells should remain editable. By default, worksheet cells are marked as locked, but that setting does not take effect until sheet protection is turned on. If users should enter information in specific cells, select those cells, open Format Cells, choose the Protection tab, and clear Locked. Protecting the sheet afterward will leave those cells editable while keeping other locked cells protected.

Allow filtering when you protect the sheet

On the Review tab, choose Protect Sheet.
In the dialog, optionally enter a password. A password is not required to turn on protection.
Under the list of allowed actions, select Use AutoFilter. Leave other permissions cleared unless users need them.
Choose OK. If you entered a password, Excel asks you to confirm it.
Test a filter arrow. Select a value or criteria, apply the filter, and confirm that the view changes while locked cells remain uneditable.

The wording and layout can vary slightly between Excel editions, but the setting is in the Protect Sheet permissions. In Excel for Mac, use the Review tab and the sheet-protection command, then allow AutoFilter in the protection options. Excel for the web has a separate protection interface, and its available controls can differ. If the exact permission matters, configure and verify the sheet in the desktop app.

What users can and cannot do

With the sheet protected and Use AutoFilter allowed, users can generally open the existing header arrows and change filter selections. They can filter by values, use available text or number filters, and clear a filter to show the records again. The exact filter choices depend on the data and the type of column.

They cannot use this permission to edit locked cells, add a new filter to a different range, or remove the existing AutoFilter. Nor does it grant permission to make unrelated changes such as formatting cells or inserting columns. Those actions have separate checkboxes in the protection dialog.

Filtering and sorting are separate permissions. A sheet can allow filtering without allowing sorting. If you also enable Sort, Excel can still prevent a sort when the requested sort range contains protected cells. In practice, sorting a protected list may require the relevant cells to be unlocked; test the exact range and operation before distributing the workbook.

Also distinguish filtering from selecting cells. The permissions to select locked or unlocked cells control where users can move the selection; they are not a substitute for Use AutoFilter. If a user can click a filter arrow but cannot make a desired change to the data itself, that is expected: the filter permission changes the view, not the underlying records.

A practical example

Suppose a worksheet contains a project register with columns for Project, Owner, Due Date, and Status. You want colleagues to find overdue or completed projects without changing the register. Add or confirm the filter arrows on the header row, unlock only any input cells that colleagues are meant to update, and then protect the sheet with Use AutoFilter selected.

A colleague can open the Status arrow and choose “In progress,” for example. Excel hides rows that do not meet that criterion. Clearing the filter makes those rows visible again. The hidden rows are still in the worksheet, and a filter does not remove their contents. A colleague who tries to overwrite a locked project name should be blocked, while an intentionally unlocked input cell remains available for entry.

This arrangement is useful for shared trackers, inventory lists, schedules, and review sheets where readers need to narrow a list but should not alter its structure. It is not a way to give different people different data access: anyone who can use the sheet may be able to clear a filter and view other rows.

Common problems and fixes

The arrows are missing after protection. The AutoFilter may not have been applied before you protected the sheet. Unprotect it, add the filter with Data > Filter, and protect it again with Use AutoFilter selected.
The arrows appear, but users cannot change the criteria. Check that the sheet was protected with Use AutoFilter enabled. If you changed protection settings, unprotect and re-protect the sheet with the corrected permission.
Users can filter but cannot sort. That is normal if sorting was not allowed. Enable the separate Sort permission if appropriate, and test it; locked cells in the sort range can still prevent sorting.
A filter seems to hide missing records. The rows may simply not match the active criteria. Clear the filter from the relevant column, or use the command to clear filters on the Data tab, to restore the full view.
Someone can edit cells you expected to protect. Check whether those cells were unlocked before protection, whether the sheet is still protected, and whether the person is editing a different sheet. Protection applies per worksheet.

If the workbook is shared with other people, test it using the same Excel app and permissions they will use. Desktop Excel, Excel for Mac, and Excel for the web can present protection controls differently, and features such as coauthoring can affect the broader editing experience.

Protection is not the same as security

Protect Sheet is intended to prevent accidental edits and limit worksheet actions. It is not a reliable way to keep confidential information secret. Filtering does not hide data securely: users with access to the workbook may be able to clear filters, copy information, or otherwise inspect content they can access. Hidden rows and columns are not a confidentiality boundary either.

If the goal is to stop accidental changes, worksheet protection and carefully unlocked input cells can be useful. If the goal is to restrict who can open or read the workbook, use appropriate file access controls or workbook encryption instead. Do not treat a sheet-protection password as a replacement for access control, and keep a separate copy of important data before changing protection settings.

A forgotten sheet-protection password can prevent routine changes to that worksheet. Microsoft does not provide a way to recover a forgotten worksheet-protection password, so store it securely if you choose to use one. This is distinct from encrypting a workbook to control access to the file itself.

Frequently Asked Questions

Can I turn on filtering after protecting a sheet?
Not with the Use AutoFilter permission alone. Unprotect the sheet, apply the filter, and then protect it again with that permission selected.

Can users clear a filter on a protected sheet?
They can generally change or clear criteria in an existing AutoFilter when Use AutoFilter is allowed. Clearing the criteria reveals matching rows again; it does not delete data.

Does “Allow users to filter” let people edit cells?
No. It permits use of existing filter controls. Cell editing is governed by whether cells are locked and whether sheet protection is active.

Can I allow filtering but prevent sorting?
Yes. Select Use AutoFilter and leave the separate Sort permission unselected. Filtering and sorting are distinct actions.

Can a filter hide rows from someone who has the workbook?
It can hide rows from the current view, but it does not secure or remove them. A person with access to the worksheet may be able to clear the filter and see the records.

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.