Excel Filters: How to Remove or Clear Them in Excel

To get rid of a filter in Excel, choose whether you want to show all rows, hide the filter arrows, or remove a table’s filter buttons. To show rows hidden by filter criteria, select Data > Sort & Filter > Clear. To turn off filter arrows on a normal range, select a cell in the range and select Data > Filter. These actions change how the data is displayed; they do not delete the rows.

Before changing anything, check whether your data is a regular range or an Excel table. The steps differ slightly, and clearing criteria is not the same as removing filter controls. The instructions below are for Microsoft Excel for Microsoft 365 on Windows. The Mac and web notes explain the main differences.

What You’ll Need

An Excel workbook with the filtered range or table you want to change.
Permission to edit the sheet. If the worksheet is protected, you may need its password or the owner’s help.
A moment to identify whether you want to reveal filtered-out rows, remove the arrows, or convert a table to a regular range.

Step-by-Step

Open the correct worksheet. Select the sheet tab containing the data. If you are unsure which range is filtered, look for small drop-down arrows in the header row. A funnel symbol on an arrow usually indicates that a filter criterion is active. An arrow without a funnel can still be present even when no rows are hidden.
Decide what “get rid of the filter” means for your task. If some records are missing from view and you want every row visible, clear the filter criteria. If you want the arrows gone as well, turn off filtering. If the data is formatted as a table, you can hide its filter buttons while keeping the table. Do not delete rows to solve a display problem.
Reveal all rows by clearing an individual column’s filter. Select the drop-down arrow in the filtered column header. Choose Clear Filter From [Column Name], replacing the bracketed words with the actual column name. Excel removes that column’s condition and displays rows that match the remaining conditions. Repeat for other columns with active filters if you want to reveal every record.
Clear filter criteria across the range. Click a cell in the filtered data, open the Data tab, and in Sort & Filter, select Clear. This clears the active filtering so all records in the filtered range can be displayed. If your goal is only to remove one condition, use that column’s drop-down instead. The Clear command is not the same as switching the filter arrows off.
Turn off filter arrows on a regular range. Click any cell inside the filtered range, then select Data > Filter. The arrows disappear and the range’s AutoFilter is turned off. This does not erase the header row, table data, or formatting. If rows were hidden by a filter, Excel normally shows them again as the filter is removed; check that all expected rows are visible afterward.
Hide filter buttons on an Excel table. Click inside the table so the contextual Table Design tab appears. In Table Style Options, clear the Filter Button check box. This hides the header arrows but keeps the table structure, structured references, and table features. To restore the arrows later, return to Table Design and select Filter Button again.
Convert a table to a regular range only if you no longer need a table. Click inside the table and select Table Design > Convert to Range, then confirm the prompt. This removes table behavior, not just the filter buttons. The cell data and much of its formatting remain, but features such as automatic table expansion and structured references are no longer available. If you only want the arrows gone, use the Filter Button check box instead.
Check the result. Look down the row numbers for gaps, and compare the visible records with the expected list. If you chose to clear criteria, the arrows should remain and all matching rows should be visible. If you chose to turn filtering off or hide table buttons, the arrows should be gone. Save the workbook once the display is as intended.

Mac and Excel for the web: In Excel for Mac, use the Data tab’s Filter and Clear controls when working with a regular range; names or ribbon placement can vary slightly by release. For a table, select it and look for the Filter Button option on the Table Design tab. In Excel for the web, use the Data tab’s Filter and Clear controls or the header’s filter menu. If a command is not visible, widen the Excel window or open the ribbon’s overflow menu.

Common Mistakes

Confusing clearing with turning off. Clear removes filter conditions, so the arrows remain available. Data > Filter toggles filtering on or off for a regular range. Choose based on whether you want the controls to stay.
Clearing only one column. A row can remain hidden because another column still has a condition. Look for funnel icons in other headers and clear those conditions too, or use Data > Sort & Filter > Clear.
Assuming filtered-out records were deleted. A filter hides rows from view; it does not normally remove their contents. Clear the criteria before copying, counting, or judging whether data is missing.
Removing table features unnecessarily. Converting a table to a range is a broader change than hiding its filter arrows. It can affect formulas and features that rely on the table name or automatic expansion. Toggle Filter Button if the table itself is still useful.
Selecting an unrelated cell or range. The Filter toggle acts on the current data region. Click inside the intended range or table before using it, especially if the worksheet contains multiple lists.
Deleting the header row or visible rows. Headers are needed for meaningful filtering, and deleting visible rows can permanently remove records. Use the filter controls rather than the Delete command.

Tips

Use the filter menu to inspect the condition. Open the column arrow and review its checked values or search box. You can clear just that column’s filter without disturbing a useful condition in another column.
Keep the table when you can. Tables make sorting, filtering, and adding new records easier. If the only annoyance is the arrows, hide Filter Button instead of converting the table.
Check for other reasons rows may be hidden. Clearing a filter will not reveal rows hidden manually, grouped rows that are collapsed, or rows excluded by a different view. If gaps remain in the row numbers, investigate those separately.
Use Undo promptly if you change the wrong setting. Ctrl+Z on Windows, or Command+Z on Mac, can undo many recent workbook actions. Undo is most useful immediately; it may not restore every state after closing or making unrelated changes.
Consider collaborators. In a shared workbook, a filter change may affect what other people see, depending on how the workbook is shared and which view they use. If you only need a personal view, check whether Sheet View is available in your version before changing a shared filter.
Save a copy before structural changes. A normal filter toggle is low risk, but converting a table or changing a workbook you did not create can affect formulas and workflows. A copy gives you a simple way to compare or restore the original structure.

Troubleshooting

The Clear command is unavailable or does not change anything. There may be no active filter condition in the selected range, or the selected cell may be outside the filtered list. Click inside the data and inspect each header for a funnel icon. If none is active, all rows may already be displayed.

Some rows are still missing after clearing. Check every column for an active filter, then look for manually hidden rows or collapsed outline groups. Select the relevant row numbers, right-click, and choose Unhide if rows were hidden manually. For grouped rows, use the outline controls beside the row numbers to expand the group.

The filter arrows will not disappear. First determine whether the data is a table. If it is, Data > Filter may not be the right control for hiding table header buttons; use Table Design > Filter Button. If it is a regular range, click inside the range and use Data > Filter. Check that the worksheet is not protected.

Excel says the sheet is protected. Protection can block filter changes unless the owner allowed filtering. Ask the workbook owner to unprotect the sheet or permit the required action. If you know the password and are authorized, use Review > Unprotect Sheet, make the change, and reapply protection if needed.

The Filter option is missing from the ribbon. The ribbon may be collapsed, the Excel window may be narrow, or the command may be in an overflow menu. Expand the ribbon or maximize the window, then check the Data tab. Selecting a table also reveals the contextual Table Design tab.

A filter seems to return after reopening the workbook. Check that you saved the workbook after changing it and that you are reopening the same file. In a shared workbook, another person or a saved view may affect what you see. Reopen the file and verify whether the arrows, criteria, or both returned; those are separate settings.

Frequently Asked Questions

Does removing a filter delete hidden rows?
No. Clearing filter criteria or turning off filtering changes which rows are displayed; it does not delete the rows. Verify the full list before removing data manually.

How do I remove a filter from just one column?
Open that column’s header arrow and choose Clear Filter From [Column Name]. Filters in other columns remain active, so some rows may still be hidden.

How do I remove all the filter arrows but keep the data?
For a regular range, click inside it and select Data > Filter. For a table, clear Table Design > Filter Button. Both keep the data; the table method also keeps the table structure.

Can I remove a filter without changing the sort order?
Yes. Clear a specific column’s filter from its header menu, or toggle filtering off for the range. Avoid using a broader sort or clear operation if you want to preserve the current order, and check the order afterward.

Why do I still see gaps in row numbers after clearing a filter?
Rows may have been hidden manually or placed in a collapsed outline group. Use Unhide for manually hidden rows, or expand the relevant group with the outline controls; clearing a filter does not reverse those separate actions.

Can I restore a filter after turning it off?
Usually, yes. Select a cell in the range and choose Data > Filter again. For a table, select Table Design > Filter Button. You may need to reapply criteria because turning filtering off can remove the active filter conditions.

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.