Excel Row Filter: How to Show Only Matching Data Rows

To filter rows in Excel for Microsoft 365 on Windows, select a cell in your data and choose Data > Filter, or press Ctrl+Shift+L. Use the arrows in the header row to choose which records to show; Excel hides rows that do not match, without deleting them.

What an Excel row filter does

An Excel row filter displays only the records that meet criteria you select. For example, a sales list can be narrowed to show orders from one region, dates in a particular month, or amounts above a threshold. The rows that do not match are hidden temporarily, so clearing the filter brings them back.

Filtering is useful when you want to inspect a subset of a larger list while keeping the original data together. It does not rearrange the underlying records in the way sorting does, and it does not remove excluded rows. A filtered worksheet may therefore have visible row numbers that skip—for instance, rows 2, 3, and 8—because rows 4 through 7 are hidden.

A filter works best when the data is arranged as a simple table: one header row, one record per row, and one type of information per column. Avoid blank rows or columns inside the list, and use clear, unique column headings such as Date, Region, and Sales. These details help Excel identify the full range and provide relevant filter options.

Turn on the filter

For an ordinary range in Excel for Microsoft 365 on Windows:

Click any cell in the data range. If the range contains blank rows or columns that could split it, select the complete range instead.
Choose Data > Filter. Excel adds a drop-down arrow to each header cell.
Click a header arrow, select or enter a criterion, and confirm it. The worksheet now shows records matching that column’s filter.

The Windows shortcut Ctrl+Shift+L toggles AutoFilter on or off. On a Mac, choose Data > Filter; keyboard shortcuts can vary with the keyboard and Excel settings. In Excel for the web, the Filter command is also on the Data tab, though the ribbon layout may differ slightly.

If Excel uses the first row of data as the headers, undo the filter, add a proper header row, and apply it again. When a range is selected before using the command, check that the selection includes every intended column and record. A filter applied to only part of a list can make it appear as though related values have disappeared or are not being filtered together.

Choose values and filter by conditions

Click a column’s arrow to see the values currently present in that column, along with a search box and options appropriate to the data type. Uncheck Select All, choose the value or values you want, and select OK. For example, in a Region column, choosing West displays only records whose Region value is West. Selecting West and East displays records matching either value in that column.

For more flexible rules, use the menu’s Text Filters, Number Filters, or Date Filters options. A number column can be filtered to show values greater than a threshold; a date column can be narrowed to a period; and a text column can be limited to entries that begin with, contain, or do not contain specified text. The options depend on the kind of values Excel recognizes in the column.

Excel may show a search box when you open the value list. Use it to find a particular item in a long list, then check the matching result. Search helps select values; it is not the same as applying a permanent rule to future entries. If new records are added later, open the filter menu again and check whether the new value is selected.

To remove a criterion from one column, open its arrow and choose Clear Filter From [column name], or select Clear on the Data tab to clear filters from the current range. Clearing a filter shows hidden records again; it does not erase a sort or delete any data. To remove the filter arrows themselves, choose Data > Filter again or use the Windows shortcut to toggle them off.

Combine filters across columns

You can apply filters to more than one column at once. Excel combines criteria from different columns: a record must meet the active condition in each filtered column to remain visible. Within one column, selecting several listed values normally means any selected value can match.

For example, suppose a list has Region, Department, and Sales columns. Selecting West in Region and Sales in Department shows records that are both in the West region and assigned to Sales. If you also select Support in the Department value list, Excel shows West-region records whose department is either Sales or Support. This distinction—OR among selected values in a column, AND between filtered columns—helps explain why a combined filter may return fewer rows than expected.

For conditions such as “greater than 100 and less than 500,” open the relevant Number Filters or Text Filters menu and choose a custom filter option when available. Its dialog lets you combine two conditions with And or Or. Read the selected operator carefully: “greater than 100 OR less than 500” matches almost every number, while “greater than 100 AND less than 500” limits the results to the intended interval.

Filter an Excel table

For a list you expect to expand, converting the range to an Excel table can make filtering and ongoing data entry easier. Select a cell in the data and choose Insert > Table, or press Ctrl+T on Windows. Confirm the range and indicate whether the table has headers. Tables normally include filter arrows in their header row, and new rows added directly below a table are generally incorporated into it.

Tables also make the data easier to identify and format, but they do not change the basic meaning of a filter: unmatched records are hidden, not deleted. If you do not want a table, AutoFilter works on a normal range as well. Use one approach or the other according to how you maintain the list; there is no need to convert every small range into a table just to filter it.

When a table has a total row, Excel can calculate a total for visible records using the table’s total-row controls. For a separate summary, the SUBTOTAL function can ignore rows hidden by filtering. This is useful because a plain SUM often includes values from filtered-out rows. Make sure the summary range covers the intended data, and avoid placing the summary inside the filtered list where it could itself be hidden.

AutoFilter, Advanced Filter, or a formula?

AutoFilter—the standard Data > Filter feature—is usually the simplest choice for interactively narrowing a list. It lets a reader change criteria from the header menus and inspect the original records in place. Use it for one-off checks, recurring manual review, and lists where hiding rows is the desired result.

Advanced Filter is a separate Excel feature for criteria ranges and more specialized filtering, including copying matching records to another location. It is available from Data > Advanced. It can be useful when several conditions need to be laid out in a criteria range or when the filtered output should be copied, but its setup is less immediate than choosing values from AutoFilter menus.

In Microsoft 365 or Excel 2021 and later, the FILTER function can return matching records in a separate, dynamically updated area. For example, with headers in A1:C1 and records in A2:C6, where column B contains Region and the rows are East/120, West/250, West/180, East/90, and West/300, place this formula outside the source data:

=FILTER(A2:C6,B2:B6="West","No matching rows")

It returns the three West records, with sales values 250, 180, and 300. Unlike AutoFilter, this formula does not hide rows in the source list; it spills a matching copy into the formula area. Keep the spill area clear, and do not put the formula inside an Excel table. FILTER is useful for a live report or a separate extract, while the header filter is preferable when you want to inspect the original records directly.

Common filter problems and fixes

Some rows are not included. Check for a completely blank row or column that divides the data, or select the whole range before applying the filter. Excel may treat separate blocks as separate lists.
A value seems to be missing. Clear other column filters first, then reopen the relevant list. A value can be present in the source but hidden because another column’s condition excludes its row.
Numbers or dates appear as text. Values stored inconsistently may not be grouped or filtered as expected. Check the cell format and the underlying values; changing a display format alone does not always convert text into a number or date.
The filter arrows are gone. Click a cell in the list and choose Data > Filter. If the list is part of a table, check that the header row is still present and that table filtering has not been disabled.
New entries do not appear in the list. Confirm that the new rows are inside the filtered range or table, then reopen the value menu. A normal filtered range may not automatically include data entered far below or outside its original boundaries.

Also remember that hiding rows manually and filtering rows are not identical in every situation. Clearing a filter restores records excluded by that filter, but it will not necessarily unhide rows that were separately hidden by a user. If expected rows remain absent, check the row headings for hidden rows and use the row unhide command where appropriate.

Frequently Asked Questions

Does filtering delete rows in Excel?
No. A filter hides rows that do not match the criteria. Clear the filter to show them again. To remove records permanently, you would need to delete them deliberately; filtering alone does not do that.

How do I filter multiple columns at once?
Apply a condition from each header arrow. Excel keeps rows that satisfy the conditions across all filtered columns. For example, filtering Region to West and Department to Sales shows records that meet both conditions.

Why does my filter show only some of my data?
Look for blank rows or columns separating the list, an incomplete selected range, or a second active filter in another column. Also check whether some rows are manually hidden or whether new entries sit outside the filtered range.

Can I filter rows by color in Excel?
Yes, when the filtered column contains cell colors, font colors, or applicable icon formatting, its filter menu can offer Filter by Color. The available choices depend on the formatting present in that column.

How do I show all rows again?
Open the active column’s filter menu and choose Clear Filter From that column, or use Data > Clear to clear filters across the current range. The filter arrows can remain in place so you can apply new criteria later.

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.