Excel Protect Sheet: Can Users Clear Filters?

Yes. In Excel for Microsoft 365 on Windows, you can protect a worksheet and still let users clear or change filters, provided the AutoFilter is already in place and you select Use AutoFilter in the Protect Sheet options. That permission lets users change filter criteria; it does not let them turn AutoFilter on or off while the sheet is protected.

What the AutoFilter permission allows

Worksheet protection normally restricts changes to cells and other worksheet features. The Use AutoFilter option is an exception that allows people to work with filtering controls on the protected sheet. They can open the drop-down arrow in a filtered column, choose different criteria, and clear that column’s filter so its previously hidden rows appear again.

The important distinction is between using an existing filter and adding or removing the filter controls. Users can adjust the criteria of an AutoFilter that was applied before protection. They cannot switch the AutoFilter feature on or off while protection is active. If you want people to filter the data, set up the filter first, then protect the sheet with the permission enabled.

For example, suppose a list has a Department column filtered to show only Sales. A protected-sheet user can open that column’s menu and clear the Sales condition to show all departments. They can also replace it with a different condition, such as showing only Support. The worksheet remains protected against edits that are not allowed by its other protection settings.

Set up the filter before protecting the sheet

On Windows, prepare the filter and protection in this order:

Select a cell in the data range, or select the range if it is not a structured table.
Choose Data > Filter. Confirm that the column headings show filter arrows.
Choose Review > Protect Sheet.
In the Protect Sheet dialog, leave Protect worksheet and contents of locked cells selected. Add a password if appropriate, and select Use AutoFilter in the list of allowed actions.
Choose OK, then confirm the password if you entered one.

Names and placement of controls can vary slightly between Excel releases, but in Microsoft 365 for Windows the relevant ribbon paths are Data > Filter and Review > Protect Sheet. Excel for Mac also has the Filter command on the Data tab and sheet protection on the Review tab; the dialog layout may differ. The underlying rule is the same: make the filter before protecting the sheet and allow AutoFilter use in the protection settings.

How to clear a filter on a protected sheet

Once the sheet is protected, use the filter menu in the column that is hiding rows. Click its drop-down arrow and choose Clear Filter From [column name], where the column name is the heading in your workbook. The wording may be shortened or displayed differently depending on the Excel build. The result should be that the filter condition for that column is removed and its rows are shown again.

If several columns have criteria, clearing one column does not necessarily remove the criteria in the others. Clear each relevant column from its own menu, or use Excel’s Data tab Clear command to clear the current filter criteria for the filtered range. If that command is unavailable or does not work in a particular workbook, clear the conditions individually from the drop-down menus. A useful test is to apply two filters before protection and check that the intended users can remove each one afterward.

Clearing a filter is not the same as deleting rows. Filtering hides rows that do not match the criteria; it does not erase their contents. When the criteria are cleared, those rows become visible again. This distinction matters when users think a record has disappeared permanently or when a filtered list is copied for reporting.

What the permission does not allow

Use AutoFilter is a narrow permission, not a general authorization to edit the worksheet. It does not unlock protected cells, grant permission to change values, or let users alter the filter’s source range. It also does not let them create a new AutoFilter, remove the filter arrows, or toggle the existing AutoFilter off while the sheet remains protected.

Other protection options are separate. For instance, Sort allows sorting in situations Excel permits on a protected sheet, but sorting can still be restricted when the range includes locked cells. Use PivotTable reports concerns PivotTable interaction, not ordinary AutoFilter menus. Select only the permissions users actually need; enabling one does not automatically enable the others.

Cell selection choices are separate as well. A user may need to select a cell in the filtered data to reach its column menu, so consider whether the allowed selection settings fit the layout. If the sheet is difficult to use after protection, check the permissions and test the actual filter arrows rather than assuming that a locked cell or a hidden row means the filter permission failed.

Filtering a table versus a regular range

Excel tables usually display filter arrows in their header row automatically. A regular range needs AutoFilter applied with Data > Filter. Either arrangement can be used with sheet protection, but the key requirement remains that the filter controls already exist before protection is turned on.

For a table, click in the table and confirm that its header drop-downs are present before protecting the worksheet. For a plain range, check that the filter applies to the complete data block, including its headers, and does not accidentally include unrelated cells. A range with blank rows or columns inside it may not behave as one continuous list, so verify the actual filtered area before distributing the workbook.

Table filters can make a shared worksheet easier to maintain because the table expands as records are added in an unprotected workflow. However, protection can restrict adding or editing records. If users need both to filter and to enter new data, plan the cell unlocking and protection settings separately, then test those tasks together. Allowing AutoFilter alone does not make the table editable.

Common problems and how to diagnose them

The arrows are missing. The filter may not have been applied before protection, or the range may not include the headings. Unprotect the sheet, apply Data > Filter or confirm the table headers, then protect it again with Use AutoFilter selected.
The menu opens, but a filter cannot be cleared. Confirm that the correct worksheet is protected with the AutoFilter permission enabled. Check whether another column still has an active condition; clearing one column only removes its own criterion.
The filter arrows cannot be changed. That is expected if the user is trying to add or remove AutoFilter itself. Unprotect the sheet, change the filter setup, and then protect it again.
Some rows remain hidden after clearing. Another column may still be filtered, or rows may be hidden manually. Clear the remaining filter criteria and check the row headings for manually hidden rows.
The password is unknown. The user cannot change protection settings without unprotecting the sheet. Ask the workbook owner or administrator for authorized access; do not assume the AutoFilter permission can bypass protection.

When troubleshooting, distinguish worksheet protection from workbook-structure protection. Protecting the workbook structure controls actions such as adding, deleting, moving, or renaming sheets. It is not the setting that grants permission to use a worksheet’s filter arrows. The relevant setting is in the Protect Sheet dialog for the specific worksheet.

Protection is not data security

Excel worksheet protection is designed mainly to prevent accidental changes and guide normal editing. It is not a strong way to keep sensitive information secret. A person who can open the workbook may still be able to see data that is merely hidden by a filter, and worksheet protection should not be treated as access control for confidential records.

If users should not receive certain rows or fields at all, provide them with a separate workbook or a properly controlled data source containing only the information they are authorized to see. Hiding rows with a filter, protecting the sheet, or hiding a worksheet does not make the underlying data an appropriate substitute for access controls. Use a password thoughtfully, share it only with authorized users, and retain an owner-controlled copy so the workbook can be maintained.

Frequently Asked Questions

Can users clear a filter without unprotecting the Excel sheet?
Yes, if AutoFilter was applied before protection and Use AutoFilter was selected in the Protect Sheet dialog. Users can then change or clear filter criteria without unprotecting the sheet.

Can users turn filter arrows on while a sheet is protected?
No. The permission allows users to operate an existing AutoFilter, not enable or disable AutoFilter. Unprotect the sheet to add or remove the filter controls, then protect it again.

Does clearing a filter delete the hidden rows?
No. A filter hides rows that do not match its criteria. Clearing the condition shows those rows again; it does not delete their contents.

Why is Use AutoFilter unavailable or not working?
First apply the filter while the sheet is unprotected. Then open Review > Protect Sheet and select Use AutoFilter. If the sheet is already protected, you must unprotect it before changing these options.

Can users filter but not edit cells?
Yes. Use AutoFilter is independent of whether cells are locked. Keep the relevant cells locked and allow AutoFilter in the protection settings; test the resulting workflow with the same protection options users will have.

A practical way to verify the setup

Before sharing the workbook, save a copy and test the exact actions that matter: open a filter menu, apply a different criterion, clear it, and confirm that the displayed rows change as expected. Then try editing a locked cell to confirm it remains protected. If users must also enter information, test those unlocked cells separately. This simple check catches a common setup error: protecting the worksheet before applying the filter or forgetting to select Use AutoFilter.

The short answer is that a protected Excel sheet can allow users to clear filters, but only when an existing AutoFilter is permitted in the sheet-protection options. Set up the filter first, enable Use AutoFilter when protecting the sheet, and remember that filtering permission does not unlock cells or allow users to rebuild the filter itself.

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.