How to Lock a Filter in Excel Without Losing Control

To keep an Excel filter in place while protecting the worksheet, set up the filter first, then choose Review > Protect Sheet and allow or disallow Use AutoFilter depending on whether people should change the criteria. Allowing AutoFilter lets users filter the protected sheet; it does not freeze the current criteria. To keep the current filtered view unchanged, protect the sheet without allowing AutoFilter. These options work in Excel for Microsoft 365 on Windows and solve two different meanings of “lock a filter.”

What You’ll Need

An Excel workbook with the data arranged in a list: one header row, one record per row, and no blank rows or columns splitting the list.
An existing filter, or permission to add one before protecting the sheet. You can add one with Data > Filter or convert the range to a table with Insert > Table.
Permission to protect the worksheet. If other people need to edit certain cells, decide which cells they should be able to change before turning on protection.
Optionally, a password you can store safely. A forgotten worksheet-protection password may be difficult or impossible to recover, so do not use a password you cannot retrieve.

Step-by-Step

Choose what “lock” should mean. If colleagues should be able to change filter selections but not edit protected data, use sheet protection with Use AutoFilter enabled. If they must see only the filtered results you have set and must not change the filter criteria, leave Use AutoFilter disabled. Excel does not provide a built-in setting that both lets users operate the filter and prevents them from changing its criteria. Decide this before protecting the sheet, because the two outcomes are different.
Prepare the data and turn on the filter. Click a cell inside the data range. On Windows, select Data > Filter. Excel adds dropdown arrows to the header row. For a table, those arrows are normally already present after you create it using Insert > Table. Check that each column has a clear, unique header and that the selected filter covers all intended records. If you want a particular view to open by default, use the dropdowns now to set the criteria and confirm that the visible rows are correct.
Decide whether any cells should stay editable. Worksheet protection locks cells by default once protection is enabled, but the Locked setting has no effect until you protect the sheet. If users should enter information in particular cells, select those cells before protecting the sheet. Press Ctrl+1, open the Protection tab, clear Locked, and choose OK. On a Mac, Command+1 opens Format Cells. Do not unlock the header or data cells if users should not change them. This step is optional when the sheet is intended to be read-only.
Open the sheet-protection controls. Select Review > Protect Sheet. In the Protect Sheet dialog, you can enter a password, or leave the password field blank if you do not need one. A password discourages casual changes but is not a substitute for storing a protected copy securely. Choose the actions users may perform in the permissions list. The most important permission for this task is Use AutoFilter. You can also allow selecting locked or unlocked cells, depending on how people should navigate the sheet.
Allow filtering while protecting the worksheet, if needed. To let people change filter selections, check Use AutoFilter before you confirm protection. The filter must already be applied to the range; protection does not let users add a new AutoFilter or remove the existing one. Leave options such as Format cells, Insert rows, or Delete rows unchecked unless users specifically need those abilities. Select OK, re-enter the password if Excel asks, and test a filter dropdown. Users should be able to narrow or clear the displayed results while locked cells remain protected.
Freeze the current filter criteria, if that is your goal. To stop users changing the current selection, do not check Use AutoFilter in the Protect Sheet dialog. Confirm protection and test the dropdown. The current criteria and visible results should remain in place, but users will not be able to operate the filter controls. This protects the displayed state rather than making a functioning filter whose criteria cannot be changed. If users need to see a different view, an authorized person must unprotect the sheet, change the criteria, and protect it again.
Verify the result and save a copy. Try the exact actions an ordinary user will take: edit a locked cell, edit an unlocked input cell if you made one, and use the filter dropdown if you allowed AutoFilter. Confirm the sheet behaves as intended, then save the workbook. If you are distributing it, close and reopen the saved file to check that the protected state and chosen filter view persist. Keep an unprotected working copy in a safe location if you expect to revise the layout or permissions later.

In Excel for the web, sheet-protection controls and available permissions can differ from the Windows desktop dialog. If you cannot find the same Use AutoFilter option or need to configure protection precisely, open the workbook in the Microsoft 365 desktop app and set up protection there. Excel for Mac has worksheet protection and filter controls too, though dialog layout and shortcuts may differ; use Review and the Protect Sheet command, and check the permission choices shown in your version.

Common Mistakes

Expecting Use AutoFilter to freeze criteria. This permission allows people to change filter selections on a protected sheet. It does not lock the selected criteria. Leave it unchecked if the view must not be changed.
Protecting the sheet before adding the filter. A protected sheet generally will not let users create or remove an AutoFilter. Add the filter or table first, then protect the worksheet.
Confusing sheet protection with workbook protection. Review > Protect Sheet controls editing and actions on one worksheet. Protect Workbook is for workbook-level structure, such as adding, moving, or deleting sheets; it does not set the filter permission for a particular sheet.
Forgetting that protection locks more than the filter. If you protect a sheet without unlocking input cells or permitting specific actions, users may be unable to enter data, format cells, sort, or insert rows. Grant only the permissions they need and test them.
Assuming sorting is included in filtering. Sort and Use AutoFilter are separate permissions. Sorting a protected range may also be restricted when cells in the range are protected. If sorting matters, test it with your exact layout and protection settings rather than assuming the filter option covers it.
Sharing a password you cannot recover. Worksheet protection is mainly a way to prevent accidental or routine edits, not strong data security. Do not rely on it to protect confidential information from someone who has access to the workbook.

Tips

Use an Excel table when the list will grow. Table filter controls normally extend to new rows added to the table, reducing the chance that later records are left outside the filter range. Create it with Insert > Table and confirm that My table has headers is selected when appropriate.
Keep the header row visible while scrolling by selecting the row below the headers and choosing View > Freeze Panes > Freeze Panes. This is separate from filter protection, but it makes dropdowns easier to locate in a long list.
For a shared workbook, explain the behavior to users in a note: either they may change filters but not the data, or the displayed filter view is fixed. This avoids a common misunderstanding when dropdown arrows are disabled.
Use a separate worksheet or saved copy for a different audience’s filter view. A single worksheet protection setting cannot give different users different filter permissions based on identity.
When you need to change protection settings, select Review > Unprotect Sheet, enter the password if prompted, make the changes, and protect the sheet again. Save after confirming the new permissions.
Protect only the sheet that needs it. If several sheets have different audiences or edit requirements, configure and test each sheet individually instead of applying one assumption across the whole workbook.

Troubleshooting

The filter arrows are missing. Unprotect the sheet first using Review > Unprotect Sheet. Then click in the data and select Data > Filter, or check that the range is still an Excel table. Protect the sheet again only after the arrows and range are correct.
The arrows appear, but clicking them does nothing. The sheet may be protected without Use AutoFilter enabled. If users should change criteria, unprotect the sheet, open Review > Protect Sheet, check Use AutoFilter, and confirm protection. If the view should stay fixed, this behavior is expected.
Users can filter but can also edit data. Check whether the cells were unlocked before protection, or whether the sheet is currently unprotected. Unprotect the sheet, select cells that should not be editable, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and select Locked on the Protection tab. Protect the sheet again. Remember that Locked takes effect only while sheet protection is on.
Users cannot enter information in intended input cells. Unprotect the sheet, select the input range, clear Locked in Format Cells > Protection, and protect the sheet again. Make sure the option to select unlocked cells is enabled if users need to click those cells directly.
New rows are not included in the filter. The filter may cover a fixed range rather than the new records. Unprotect the sheet, extend the range or convert the list to a table, verify the new rows are inside it, then protect the sheet again. Avoid blank rows within the list, since they can cause Excel to detect only part of the data.
You cannot change or remove the protection. Use Review > Unprotect Sheet and enter the password that was set. If it is unknown, contact the workbook owner or look for an authorized unprotected backup. Do not assume that changing workbook structure protection will remove worksheet protection; they are separate controls.

Frequently Asked Questions

Can I lock the filter dropdown but still let people edit cells?
Yes. Protect the sheet without enabling Use AutoFilter, then unlock only the cells people may edit before protection. The filter criteria stay unavailable to them, while unlocked cells can remain editable. Be sure to test both behaviors after enabling protection.

Can users change the filter on a protected sheet?
Yes, if the filter already exists and you select Use AutoFilter in the Protect Sheet dialog. They can change filter criteria, but that setting does not let them create or remove the AutoFilter. Other edits remain governed by the sheet’s cell locks and permission choices.

Can I let someone use the filter but stop them from changing its criteria?
Not with the standard worksheet-protection permission. Use AutoFilter allows the user to change criteria; turning it off prevents filter operation. To keep a specific view, disable that permission. For different views, provide separate copies or sheets configured for each audience.

Does protecting a sheet hide rows that the filter excludes?
The current filter can leave matching rows visible and hide nonmatching rows. With protection on and Use AutoFilter disabled, users cannot change the filter selection through the dropdown, though sheet protection is not a security boundary for sensitive data. Share only information the recipient is allowed to access.

Is Protect Sheet the same as locking cells?
No. Cells are marked Locked or Unlocked in Format Cells, but that setting has no effect until you protect the worksheet. Use cell locking to decide what can be edited, then use Protect Sheet to enforce those choices and set filter permissions.

Will these steps work in Excel for Mac?
The general approach is the same: set up the filter, configure locked cells if needed, and protect the worksheet with the appropriate permissions. The menus and dialog layout can vary by version. On Mac, Command+1 opens Format Cells; on Windows, use Ctrl+1. If a protection option is missing, configure the workbook in Excel for Microsoft 365 on Windows.

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.