Filter Excel Table by Criteria: A Practical Excel Guide

Formulas & functions · Updated November 20, 2026

To filter an Excel table by criteria, open the drop-down arrow in the column header, choose a value or a filter rule, and apply it; Excel hides rows that do not match. For example, use a Region filter to show only West, or a Number Filters rule to show sales greater than or equal to 1,000.

Filter a table with the header drop-downs

Excel tables include filter controls in their header row. If your data is not yet a table, select a cell in the data and choose Insert > Table, or press Ctrl+T in Windows. Confirm that the range is correct and that My table has headers is selected if the first row contains column names. When you create a table, Excel adds filter arrows to its headers automatically.

  1. Click the arrow beside the column whose values you want to filter.
  2. Use the search box or check boxes to select the value or values to keep. Clear Select All first if you want to choose only a few items.
  3. Click OK. Excel hides the rows that do not meet the selection.

For example, in a table with columns Order Date, Region, and Sales, opening the Region arrow and selecting West displays only West-region orders. The table’s other columns remain together with their rows, so order dates and sales stay correctly associated. A filter does not delete excluded data; it temporarily hides it from view.

To remove one column’s filter, open that column’s arrow and choose Clear Filter From [column name]. To remove filters across the current table or range, use Data > Clear. You can also use the header menus to change selections without clearing every other column’s filter.

Choose the right kind of criteria

The menu adapts to the type of data in a column. For text, options commonly include Text Filters such as Equals, Does Not Equal, Begins With, Ends With, and Contains. These help when a column contains many different labels or names. For instance, Contains can find product descriptions that include “cable” even when the full descriptions are not identical.

For numbers, use Number Filters to set rules such as Greater Than, Less Than, Between, or Top 10. The Top 10 option can be adjusted to show a chosen number or percentage of items; it is not limited to ten rows. For dates, Date Filters offer calendar-based choices such as a particular period, month, quarter, or year, depending on the data and Excel version. If a column contains mixed data types, Excel may show more than one category of filter option.

Use the search box or check boxes when you already know the exact items you want. Use a rule-based filter when the criterion is a condition rather than a fixed list—for example, all prices below 25, all dates in the current year, or text that ends with a particular suffix. You can usually combine two rules for one column using the Custom Filter dialog, such as “is greater than 100” and “is less than 500.”

Combine criteria across columns

Filters applied to different columns work together. If you filter Region to West and Sales to greater than or equal to 1,000, the visible rows must meet both conditions. This is an AND relationship: a row must pass every active column filter to remain visible. Excel keeps the criteria active until you clear or change them, so you can add a date or category filter without losing the existing selections.

Within one column, selecting several distinct values from the check boxes works like an OR condition: a row can match any selected value. For rule-based conditions in a single column, the Custom AutoFilter dialog lets you choose And or Or. For example, to show sales below 100 or above 1,000, open the Sales arrow, choose Number Filters > Custom Filter, enter the two comparisons, and select Or. If you select And for those two conditions, no ordinary number can satisfy both, so the result will usually be empty.

Think through the logic before adding filters. “West and sales above 1,000” means both rules must be true, while “West or East” means either region is acceptable. A common source of confusion is expecting an OR relationship across separate columns: ordinary table filters combine different columns with AND. For a more complex condition that needs, for example, West with high sales or East with high sales, Advanced Filter or a formula-based result may be easier to manage.

Use Advanced Filter for a criteria range

Advanced Filter is useful when you need a visible criteria grid, more complex combinations, or a separate copy of matching records. In desktop Excel, place the criteria range outside the table, with at least one column header that exactly matches a source-table header. Put criteria under the matching headers. Conditions entered on the same criteria row are combined with AND; conditions on different rows are combined with OR.

For example, assume the table has Region, Sales, and Status columns. A criteria range with Region and Sales headers on one row and West and a value greater than 1,000 beneath them requests West records with sales above 1,000. If you add another criteria row containing East and a sales condition, that row represents an alternative. This layout makes the logic visible and easier to review than a long sequence of dropdown selections.

To run it, select a cell in the source list, choose Data > Advanced, and check the List range and Criteria range. Choose Filter the list, in-place to hide nonmatching rows, or Copy to another location to create a separate result. The criteria headers must match the list headers, and the source data should have one clear header row. Advanced Filter is a desktop feature; Excel for the web does not offer the same full Advanced Filter dialog.

Return matching rows with the FILTER function

If you want a separate, formula-driven result rather than hiding rows in the original table, the FILTER function is another option in Microsoft 365 and Excel 2021 or later. It returns rows that meet a logical condition and updates when the source data changes. This is different from a header filter: the original table remains visible and unchanged, while the formula spills matching records into a separate area.

Assume the Excel table is named Table1 and has columns Region and Sales. Enter this formula in a blank cell outside the table to return West-region rows with sales of at least 1,000:

=FILTER(Table1,(Table1[Region]="West")*(Table1[Sales]>=1000),"No matches")

The multiplication combines the two tests as AND. The text shown when no rows qualify is “No matches.” Because the result can spill into multiple cells, leave enough empty space below and to the right of the formula. A spill error can occur if another value blocks that output area. Do not put the formula inside the source table, where it cannot spill as a multi-cell result.

For an OR condition, use addition between the tests. With the same Table1 layout, this returns rows from either West or East:

=FILTER(Table1,(Table1[Region]="West")+(Table1[Region]="East"),"No matches")

Table names and column headers in the formula must match the actual workbook. Rename a table from the Table Design tab if you want a clearer name, then use that name in the formula. FILTER is a good choice for a live report or dashboard, but use a regular table filter when your goal is simply to inspect or temporarily narrow the original records.

Common filter problems and fixes

  • The filter arrow is missing: Click inside the table and check that the header row is present. If the data is only a plain range, use Data > Filter or convert it to a table with Insert > Table.
  • Expected records are not showing: Check every column for an active filter, not just the one you are viewing. A filter icon in a header indicates that a filter is applied. Use Data > Clear to reset filters, then apply the criteria again.
  • Numbers sort or filter like text: Some cells may contain numbers stored as text, spaces, or inconsistent entries. Inspect the source values and correct the data type or clean the entries before relying on numeric comparisons.
  • Dates do not group as expected: A column may contain text that looks like dates alongside real Excel dates. Check representative cells and convert inconsistent entries to actual dates before applying date rules.
  • New rows are missing: Add them directly below the table so the table can expand, or resize the table from Table Design > Resize Table. A plain range with a separately applied filter may not include rows added outside its original boundaries.
  • The result looks incomplete: Hidden rows can result from filters, manual hiding, or grouped rows. Clear the filters first and inspect the row numbers to distinguish filtered-out rows from other hidden rows.

Practical habits for reliable filtering

Good source data makes criteria easier to apply. Keep one kind of information in each column, use a single header row with distinct labels, and avoid blank rows inside the data. A column that mixes dates, notes, and numbers can produce confusing filter menus. Consistent spelling also matters: “West,” “WEST,” and “West ” with a trailing space may behave like separate text values.

Before copying or summarizing filtered records, confirm which rows are visible and whether you intend to include all matching rows. Filtering does not remove records, but a later copy-and-paste action can behave differently depending on the selection and command used. For routine viewing, the table arrows are usually the simplest approach. For repeatable complex logic, keep an Advanced Filter criteria range or use a FILTER formula and label its output clearly.

In Excel for Mac, the table header menus and filter concepts are broadly similar, though ribbon layout and dialog placement can differ. Excel for the web also supports ordinary table filtering, while some advanced desktop commands are not available there. If a workbook is shared across platforms, prefer standard table filters for straightforward criteria and check that formula-based results are supported by the recipients’ Excel versions.

Frequently Asked Questions

How do I filter an Excel table for multiple values?
Open the relevant header arrow, clear Select All, check each value you want to keep, and click OK. To filter by values in more than one column, apply a selection in each column; those column filters work together with AND logic.

Can I filter Excel by two conditions at once?
Yes. Use Custom Filter for two conditions in one column, choosing And or Or as appropriate, or apply filters to separate columns when both conditions must be true. Use Advanced Filter or FILTER when the logic is more complex.

Does filtering delete rows in Excel?
No. A filter hides rows that do not meet the criteria. Clear the filter to show them again. Deleting rows is a separate action and should not be confused with filtering.

How do I clear a filter without removing the table?
Open the filtered column’s header menu and choose Clear Filter From [column name], or use Data > Clear to clear the active filters. The table and its data remain in place.

Can Excel automatically filter a table when new data is added?
Table filters apply to rows that are part of the table, including rows added as the table expands. If new records are outside the table, resize the table or add the records directly beneath it so they become part of the table.

Related

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.