Excel Filter Table Multiple Criteria: Practical Methods

Formulas & functions · Updated November 19, 2026

To filter an Excel table by multiple criteria, use the filter arrows in its column headings: set criteria in different columns to keep rows that meet all of them, or set multiple choices in one column to keep rows matching any selected value. For more control, use a custom filter, Advanced Filter, or the FILTER function in Microsoft 365.

How multiple criteria work in an Excel table

Excel’s ordinary table filters hide rows that do not match your selections; they do not delete those rows. A row remains visible only when it passes the active filters. The key distinction is whether criteria apply across different columns or within the same column.

  • Different columns usually mean AND. Filtering Region to West and Status to Open shows rows where the region is West and the status is Open.
  • Multiple selected values in one column mean OR. Selecting West and East in the Region menu shows rows where the region is West or East.

This behavior makes the table dropdowns a good starting point for everyday questions such as “show open orders from the West” or “show orders from either the West or East.” If the request mixes conditions in a more complicated way—for example, West and Open, or East and Overdue—you may need Advanced Filter or a formula.

Apply filters with table heading arrows

Excel tables normally have filter arrows in their header row. If the data is not yet a table, click a cell in the data and choose Insert > Table, confirm the range and whether it has headers, then select OK. You can also enable or remove filters from the Data tab, but a table is useful because its filter controls and formatting expand with added rows.

  1. Click the arrow in the first column you want to filter.
  2. Select or clear the checkboxes for the values to include. Use the search box in the menu to find a particular value in a long list.
  3. Click OK, then open another column’s arrow and set its condition.

For example, suppose a table has Order, Region, Status, and Amount columns. Select West in Region, then Open in Status. Excel displays only records that satisfy both selections. The active filter icon on a heading helps indicate which columns have filters applied.

To remove a filter from one column, open that column’s menu and choose Clear Filter From [column name]. To clear all active filters, use Data > Sort & Filter > Clear. Clearing filters makes hidden rows visible again; it does not remove the filter arrows or erase the table’s contents.

Use AND and OR conditions in a custom filter

When a column needs a comparison rather than a simple checklist, use its menu’s filter commands. Depending on the data, the menu offers Text Filters, Number Filters, or Date Filters. Choose the relevant category and then Custom Filter to enter one or two conditions.

For example, to show amounts greater than 100 and less than 500, open the Amount filter, choose Number Filters > Custom Filter, set the first condition to is greater than 100, select And, then set the second to is less than 500. To show amounts below 100 or above 1,000, use the same dialog but select Or between the conditions.

Text filters can match values that begin with, end with, contain, or do not contain specified text. Date filters offer calendar-oriented choices as well as comparisons. The exact available options depend on the type of values Excel recognizes in the column. If numbers or dates appear as text, the intended comparison may not work as expected; convert the entries to real numeric or date values where appropriate.

A custom filter generally gives you two conditions for one column. It does not replace the regular filters on other columns: those separate column filters still combine with one another. In other words, a custom Amount condition can be combined with Region = West and Status = Open.

Choose the right method for a complex request

Before building a complicated filter, translate the request into groups of conditions. Conditions joined by AND must all be true for a row; alternatives joined by OR allow a row through if any listed combination is true. For instance, “West and Open, or East and Overdue” means two paired groups, not simply West or East and Open or Overdue.

That distinction matters because filtering Region to West and East, then Status to Open and Overdue, produces every combination: West/Open, West/Overdue, East/Open, and East/Overdue. It does not limit the results to just West/Open and East/Overdue. Choose a method that expresses the logic you actually need:

  • Table dropdowns: best for straightforward selections and AND conditions across columns.
  • Custom Filter: useful for two comparisons within one column, joined by AND or OR.
  • Advanced Filter: useful for more elaborate combinations, including paired conditions.
  • FILTER formula: useful when results should appear in a separate, automatically updating area.

Set up Advanced Filter criteria

Advanced Filter uses a small criteria range on the worksheet. Put the relevant column headings in that range, matching the source table’s headings, then enter the condition beneath each heading. Choose Data > Sort & Filter > Advanced to open the tool. You can filter the list in place or copy matching records to another location on the same worksheet.

In an Advanced Filter criteria range, conditions on the same row are combined with AND; conditions on separate rows are combined with OR. For example, use headings Region and Status. Put West and Open on the first criteria row, then East and Overdue on the next. This represents (West AND Open) OR (East AND Overdue), which the regular column checkboxes cannot express as a pair of groups.

When copying results, specify a destination cell and ensure the copied range has appropriate headings. The criteria range and destination should be outside the source list to avoid confusion. Advanced Filter is not a live, spilling result: if the source data changes, run the filter again to refresh the copied results. The in-place version hides nonmatching rows, much like an ordinary filter.

Return matching rows with the FILTER function

Microsoft 365 also supports the FILTER function, which returns matching values in a separate spill range and recalculates when its referenced data changes. It is available in Microsoft 365 and Excel 2021 or later; it is not available in older perpetual Excel versions such as Excel 2019. A formula is useful when you want a reusable report without manually opening filter menus.

Assume the Excel table is named Sales and has Region and Status columns. To return rows where Region is West and Status is Open, enter this formula in a blank cell outside the table:

=FILTER(Sales,(Sales[Region]="West")*(Sales[Status]="Open"),"No matches")

Multiplication combines the two TRUE/FALSE tests as AND. To return rows where Region is West or East, use addition for OR:

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

The table reference Sales represents its data rows, so the formula returns matching records rather than the header row. The third argument supplies a message if there are no matches. The output needs room to spill; existing content in the output area can cause a #SPILL! error. The returned range is also not automatically a new Excel table, though you can format it or use it as the basis for another report.

These examples use exact text matches. If your data contains inconsistent spaces or spelling, first standardize the entries or adjust the logic. Structured references make formulas easier to read than fixed cell addresses, and they generally include new rows added to the named table.

Common filter problems and useful checks

A filter that seems to miss records often reflects inconsistent data rather than a broken command. A value such as Open with a trailing space may be treated differently from Open, and dates stored as text may not behave like real dates. Check representative source cells, not just the visible results.

  • Unexpected blanks: the dropdown checklist may include a blank item. Check whether the source range contains empty cells or formulas that return an empty string.
  • Missing new records: confirm that the records were added inside the table or that the table range expanded. Ordinary filters on a plain range may not include data pasted below the original range.
  • More results than expected: check whether the criteria are OR choices within a column or AND conditions across columns. Also inspect filters left active on other headings.
  • Formula error: for FILTER, make sure the output cells are clear and the include tests have the same number of rows as the data being filtered.
  • Filter arrows are gone: click in the table and use the Table Design tab to confirm the header row is enabled, or use Data > Filter to toggle filter controls.

Filtering can also make it easy to edit only visible records by mistake or misunderstand a subtotal. Before deleting or changing records, clear filters or verify which rows are selected. Hidden rows still exist, and a filter is not a substitute for a backup when making substantial edits.

Frequently Asked Questions

Can Excel filter two columns at the same time?
Yes. Apply a filter from each column’s heading menu. Excel combines the conditions across those columns, so a row must pass both active filters to remain visible.

How do I filter for multiple values in one column?
Open the column’s filter arrow and select each value you want to include. Excel treats the selected values as alternatives, so a row is shown when its value matches any selected item.

Can I use AND and OR in the same Excel filter?
Yes, but the regular dropdowns are limited for complex grouped logic. Use Advanced Filter criteria rows for combinations such as (West and Open) or (East and Overdue), or build a FILTER formula that expresses the conditions.

Does filtering delete rows from a table?
No. Filtering hides rows that do not match the criteria. Clear the filter to show them again; deleting visible rows is a separate action.

Why does my FILTER formula show #SPILL!?
The formula’s result needs an unobstructed area to expand into. Clear cells in the intended output range, and check for merged cells or other obstructions that prevent the results from spilling.

Do Excel filters update when I add table rows?
Table filters generally include rows added to the table. Advanced Filter results copied elsewhere are a snapshot and must be run again; a FILTER formula referencing the table recalculates as the table data changes.

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.