Excel Protect Sheet but Allow Filtering: How to Set It Up

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365 on Windows, turn on AutoFilter for the range first, then go to Review > Protect Sheet and select Use AutoFilter in the protection options. Users can then change filters on the existing range without being allowed to edit its locked cells.

What sheet protection does—and what it does not do

Worksheet protection controls which actions people can take on a sheet. It is useful when a workbook should remain readable and filterable but formulas, headings, or source data should not be changed accidentally. Filtering changes which rows are displayed; it does not delete the rows that fail the criteria. That makes it a practical way to let someone explore a list while preserving the underlying records.

Protection is separate from the filter itself. AutoFilter adds drop-down controls to column headings, while sheet protection restricts actions on the worksheet. To use those controls after protection is enabled, Excel needs the AutoFilter permission and an AutoFilter that was already applied. Checking the permission does not create filter arrows on its own.

Worksheet protection is not a substitute for securing confidential information. It is intended to limit editing, not to serve as strong access control or file encryption. If a workbook contains sensitive information, share it only with appropriate people and consider protecting the file with encryption as well.

Set up filtering before you protect the sheet

On Windows, prepare the list and enable its filter controls before turning on protection:

  1. Select a cell inside the data range. Make sure the range has a single header row and does not have blank rows or columns that split the list.
  2. Choose Data > Filter. Excel adds a drop-down arrow to each header in the detected range. If the range is an Excel table, its header row normally has filter arrows already.
  3. Choose Review > Protect Sheet.
  4. In the Protect Sheet dialog, leave Protect worksheet and contents of locked cells selected. Select Use AutoFilter in the list of allowed actions.
  5. Choose any other permissions the users need, such as selecting locked or unlocked cells. Enter a password if you want to require one to unprotect the sheet, then confirm the password if prompted.

The key is the order: create or enable the filter first, then protect the sheet with Use AutoFilter allowed. On Excel for Mac, the desktop ribbon also has a Review > Protect Sheet command; dialog wording and layout can vary by release. If you cannot find the option or the sheet was protected elsewhere, check the protection settings in the desktop app you are using.

What the Use AutoFilter permission allows

With an existing AutoFilter and the permission enabled, a user can open a column drop-down and choose which values or conditions to display. For example, a protected sales list can remain locked while a colleague filters the Region column to show only West, or filters a Date column to show records from a particular period. The filtered-out rows are hidden from view, not removed from the worksheet.

That permission is narrow. It does not generally let users add or remove the filter controls on a protected sheet. It also does not unlock cells, grant permission to change formulas, or permit every action available in an unprotected worksheet. To remove a filter or change the range to which it applies, the sheet owner may need to unprotect the sheet, make the change, and protect it again.

Filtering is also different from selecting a cell. A user may be able to change the displayed rows without being able to edit the locked values in those rows. The Select locked cells and Select unlocked cells options control what users can click or select on the sheet; they are not replacements for the Use AutoFilter permission. Leaving Select locked cells enabled can make a protected list easier to navigate, though it is not a grant to edit those cells.

Decide whether users also need to sort or edit

Filtering and sorting are separate permissions in the Protect Sheet dialog. Filtering hides rows that do not match chosen criteria. Sorting changes the order of records. If users should only narrow a list, enable Use AutoFilter and leave Sort unchecked. If they need sorting too, enable Sort, but be aware of an important limitation: Excel may not sort a protected range when the cells in the range are locked, even when Sort is allowed. The permission does not automatically make a locked data range sortable.

For a workflow that requires users to rearrange records, test it with the same protection and cell setup they will use. A common design is to keep formula cells locked while unlocking only specific input cells, but unlocking data cells also means users may edit their contents. Decide which matters more for the workbook: preserving every value, or permitting sorting on the protected list. Do not unlock an entire range merely to make sorting possible without considering the editing access that creates.

Other checkboxes can be useful for specific tasks, such as formatting cells or inserting rows, but each expands what users can change. Choose only the actions needed for the intended workflow. A sheet that is protected with many extra permissions may be harder to maintain and may not provide the safeguards its owner expects.

Using filters with an Excel table

Excel tables are often a good fit for lists that grow over time. A table has structured headers and normally displays filter arrows in its header row. If those arrows are present before you protect the worksheet, users can use them when Use AutoFilter is allowed. A table can also make it easier to add records consistently when the sheet is not protected, but protection settings still determine what users can do on the protected sheet.

Do not assume that permission to filter also permits users to expand, resize, or redesign the table. Those are structural changes, not just changes to the displayed filter criteria. If new records must be added regularly, test adding rows under protection and set the relevant permissions deliberately; depending on the sheet layout and protection settings, users may need the owner to add records or temporarily unprotect the sheet.

For a simple list that does not need table features, a normal range with Data > Filter is also suitable. The important requirement in either case is that the filter controls already exist before protection is applied.

Common problems and how to fix them

  • The filter arrows are missing. Unprotect the sheet, select a cell in the data, and choose Data > Filter. Then protect it again with Use AutoFilter selected. The permission alone does not create arrows.
  • The arrows appear, but the drop-down cannot change the results. Unprotect the sheet and check the Use AutoFilter option in Review > Protect Sheet. Confirm that the sheet was protected with that option enabled, not just with cell selection allowed.
  • The user cannot add a new filter. That is expected on a protected worksheet. Apply the filter before protection. Unprotect the sheet when you need to change or remove the AutoFilter setup.
  • Some actions work on one sheet but not another. Protection is set per worksheet. Each sheet has its own filter setup and allowed actions, so repeat the check on every relevant tab.
  • Filtering is confused with deleting data. A filter hides nonmatching rows from view. Clear the filter to display them again. If the concern is that users might overwrite the data, keep those cells locked and protect the sheet.
  • A password prompt blocks changes. The password is used to unprotect the sheet so its settings or protected content can be changed. Keep a record of passwords used in workbooks managed by a team; do not assume the filter permission removes the need for the password.

Protection, passwords, and workbook sharing

Protecting a sheet can reduce accidental edits, such as overwriting a formula while filtering a report. It should not be described as a way to hide data securely: users may still be able to see the workbook contents, and worksheet protection is not the same as encrypting a file. For information that must not be disclosed, do not distribute it in a workbook that recipients can open.

When a password is used, users who need to alter the protection settings must know it. A good maintenance practice is to keep an authorized copy of the password and an unprotected backup of the workbook in a controlled location. Before distributing a protected file, close and reopen it or otherwise check the sheet as a recipient would, then verify that filtering works and that locked cells cannot be changed.

Changes to protection do not replace sensible workbook design. Mark formula cells as locked, unlock only the cells intended for input, and use clear labels to tell users which actions are available. Protection options are applied to a worksheet, so a workbook with several protected tabs may need different settings on each one.

Frequently Asked Questions

Can users filter a protected Excel sheet without a password?

Yes, if the sheet was protected with Use AutoFilter enabled and the filter was applied before protection. They do not need the password just to use the permitted filter controls. They will generally need the password, if one was set, to unprotect the sheet and change its protection setup.

Can I enable filtering after I have protected the sheet?

Usually, unprotect the sheet first, apply Data > Filter to the range if needed, and protect it again with Use AutoFilter selected. The protected-sheet permission does not add a new AutoFilter to the worksheet.

Does filtering delete or change hidden rows?

No. Filtering changes which rows are visible; it does not delete the rows that do not match. Clearing the filter displays the records again. Protection can separately prevent users from editing locked cells.

Can users filter and sort a protected sheet at the same time?

They can use filtering when Use AutoFilter is enabled. Sorting requires the separate Sort permission, and Excel may still prevent sorting a protected range whose cells are locked. Test sorting with the actual range and protection settings before relying on it.

Will this work in Excel for the web?

Protection and filtering controls can differ between the desktop and web interfaces and may depend on the workbook and current Excel for the web features. Set up and verify the protection in Excel for Microsoft 365 desktop if the exact Use AutoFilter permission is required, then confirm the shared workbook behaves as intended in the web app.

Related

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.