Excel Filter Command for Multiple Criteria Explained

Formulas & functions · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, use Data > Filter, then set a condition from each relevant column’s dropdown: criteria in different columns are combined as AND, while multiple selected items in one dropdown generally work as OR. For more complex rules, use Data > Advanced or the FILTER function; the best method depends on whether you want to hide rows, copy matching records, or return a formula-driven result.

Choose the right way to filter

Excel does not have a single command called “multiple criteria filter.” Instead, its regular AutoFilter, Advanced Filter, and worksheet formulas handle different versions of the task. Before setting anything up, decide what “multiple criteria” means in your case: all conditions must be true, any one condition may be true, or the logic mixes both.

  • Use AutoFilter for quick, interactive filtering of a list or Excel table. It hides rows that do not meet the selections.
  • Use Advanced Filter when you need more elaborate AND/OR logic, or want to copy matching records to another location.
  • Use FILTER when you want a live result range that updates as source data changes. It is available in Microsoft 365 and Excel 2021 or later.

For routine work, start with AutoFilter. It is easy to adjust, and it leaves the original data in place. Switch methods only if the rules become awkward to represent in the dropdowns or you need a separate output.

Apply multiple criteria with AutoFilter

To filter a range, make sure each column has a clear header and there are no blank rows splitting the data. Click inside the list, then select Data > Filter. Excel adds a dropdown arrow to each header. In a formatted Excel table, filter arrows are normally already present; if not, select a cell in the table and use the same command.

  1. Open the dropdown for the first column you want to filter.
  2. Select or clear the checkbox values you want to include, or choose a condition under Text Filters, Number Filters, or Date Filters.
  3. Open another column’s dropdown and set its condition too.
  4. Repeat for any other columns. Excel displays only rows that meet all the active column filters.

For example, a sales list filtered to Region = East and Status = Open shows records that satisfy both conditions. A row with Region = East but Status = Closed is excluded, as is a row with Region = West and Status = Open. Filtering does not delete excluded rows; it temporarily hides them. The row numbers may skip while a filter is active.

On Windows, Ctrl+Shift+L toggles filter arrows for the selected data range. On Mac, the same shortcut is commonly available, but keyboard settings and Excel versions can affect shortcuts; use Data > Filter if it does not work. In Excel for the web, use the Data tab’s Filter command or the table’s header dropdowns.

Understand AND and OR rules

Excel’s ordinary AutoFilter combines filters on separate columns using AND. Choosing East in the Region column and Open in the Status column means “Region is East and Status is Open.” This is the usual arrangement for combining criteria across fields.

Within one column, selecting more than one listed value generally means OR. Selecting East and West in a Region dropdown returns rows where Region is East or West. This is useful for including a few known categories. The search box in the dropdown can help locate values in a long list, but selecting a value there does not change the basic AND/OR logic.

For two conditions on the same field, open the field dropdown and choose Text Filters, Number Filters, or Date Filters, then choose a rule such as Contains, Greater Than, or Between. In the Custom AutoFilter dialog, set both conditions and choose And or Or. For example, “greater than 100” AND “less than 500” finds values inside that interval; “less than 10” OR “greater than 90” finds values outside it.

One limitation is that a simple dropdown setup is not a general-purpose expression builder. A request such as “Region is East and Status is Open, or Region is West and Status is Pending” can be represented with Advanced Filter criteria or a formula more clearly than by stacking ordinary dropdown choices.

Use Advanced Filter for complex criteria

Advanced Filter lets you express criteria in a small worksheet range. Put the field names in the criteria range using the same headers as the source list. Put conditions that must all be true on the same criteria row. Put alternative criteria on separate rows, which represents OR.

For example, to find East-region records with Open status, place the headers Region and Status next to each other in the criteria area and put East and Open beneath them on the same row. To find either East/Open records or West/Pending records, put those two pairs on separate rows under the matching headers. Each row is an AND group; the rows are alternatives.

Click a cell in the source list, then choose Data > Advanced. Select Filter the list, in-place to hide nonmatching rows, or Copy to another location to produce a separate result. Check the List range and Criteria range before applying the filter. The criteria headers must match the source headers, and the criteria cells need to use appropriate values or comparison expressions.

Advanced Filter is a desktop Excel feature; it is not the same as the basic filter controls in Excel for the web. It is also a useful choice when you want a reusable criteria layout or several OR groups. If you change the source data, you may need to apply Advanced Filter again; unlike a dynamic array formula, its copied result is not automatically recalculated as a live spill range.

Return matching rows with FILTER

The FILTER function returns matching records in a separate area rather than hiding rows in the original list. In Microsoft 365 and Excel 2021 or later, its results spill into neighboring cells. Keep the output area clear, or Excel may show a spill error. The source list can remain visible while the formula result changes with its criteria.

Assume A1:D6 contains headers Order, Rep, Region, and Status, and rows 2:6 contain these records: 101, Ana, East, Open; 102, Ben, West, Open; 103, Cara, East, Closed; 104, Dev, North, Open; and 105, Eli, West, Closed. With those headers and records in A1:D6, use this formula to return rows where Region is East and Status is Open:

=FILTER(A2:D6,(C2:C6="East")*(D2:D6="Open"),"No matches")

The multiplication combines the two TRUE/FALSE tests as AND. For this sample, the result is the single record 101, Ana, East, Open. The optional third argument displays No matches if no row meets the conditions.

To return either East or West records when Status is Open, use the same A1:D6 layout:

=FILTER(A2:D6,((C2:C6="East")+(C2:C6="West"))*(D2:D6="Open"),"No matches")

Here addition combines the two Region tests as OR, while multiplication requires Open status as well. The sample returns two rows: 101, Ana, East, Open and 102, Ben, West, Open. The criteria arrays must have the same number of rows as the data being filtered. If your Excel installation uses semicolons rather than commas as formula separators, enter the separators required by your regional settings.

Dates, numbers, and text conditions

AutoFilter offers condition menus that fit the data type Excel recognizes. Number Filters include comparisons such as Greater Than and Between. Date Filters provide date-based choices, including date periods, while Text Filters include conditions such as Begins With, Ends With, and Contains. The exact options shown can depend on the field’s contents and how Excel interprets them.

Dates and numbers should be stored as actual Excel values, not merely text that looks like a date or number. A date stored as text may not respond to date conditions as expected. If a column mixes dates, text, blanks, and numbers, check its data consistency before assuming the filter logic is wrong. For text criteria in Advanced Filter, comparison operators can be included in the criteria cell, such as >100 for a numeric threshold; ensure the source values are numeric.

Wildcards can help with text searches. In criteria that support them, an asterisk represents a series of characters and a question mark represents one character. For example, a text condition for North* can match entries that begin with North. When possible, the menu’s built-in Text Filters are easier to understand and less prone to typing errors.

Common filtering problems and fixes

  • A filter misses expected rows: Check for leading or trailing spaces, inconsistent spelling, and text values that look like numbers. Also confirm that the source range includes every record.
  • Rows from other columns seem to disappear: That is expected when filters are active on multiple columns. Each active column narrows the results further.
  • The dropdown shows incomplete values: Look for blank rows or columns that may have caused Excel to detect only part of the list. Convert the data to a table or select the full range before applying Filter.
  • A formula returns no matches: Confirm that the criteria text matches the source exactly and that each condition range has the same height as the return range. Check whether the source contains spaces or different data types.
  • FILTER displays a spill error: Clear cells in the output area that block the results. A dynamic array cannot spill into occupied cells.
  • Records appear missing after filtering: Clear filters to show all rows. Use the header dropdown’s Clear Filter command, or toggle filtering off and on as appropriate; do not assume hidden rows were removed.

To remove all active filters while keeping the arrows, use Data > Clear on Windows. To remove the arrows as well, use Data > Filter to toggle Filter off. Clearing a filter and turning off Filter are different actions: the first restores visible records, while the second removes the dropdown controls.

AutoFilter, Advanced Filter, or a formula?

AutoFilter is the quickest choice for examining a list interactively and is usually best for occasional questions. It changes which source rows are visible, not the data itself. Advanced Filter is preferable when the logic has several OR groups or when a one-time copied extract is needed. The FILTER function suits reports where matching records should update automatically as the source or criteria change.

Do not confuse filtering with sorting. Sorting changes the order of records; filtering limits which records are shown. You can use both together, and sorting filtered results does not by itself alter which records satisfy the criteria. If other people rely on the workbook, label criteria and output areas clearly so they can tell whether they are looking at source data, a hidden-row view, or a separate formula result.

Frequently Asked Questions

Can Excel filter two conditions in the same column?
Yes. Open the column dropdown, choose the appropriate Text, Number, or Date Filters option, and enter two conditions in Custom AutoFilter. Choose And when both must be true, or Or when either may be true.

How do I filter for multiple values in one column?
Open the dropdown and select the checkboxes for each value to include. The selected values act as alternatives, so a row can match any selected value in that column.

How do I use OR across different columns?
For grouped rules such as East/Open or West/Pending, use Advanced Filter with each group on a separate criteria row, or build OR and AND tests with the FILTER function. Ordinary filters on separate columns apply AND logic.

Does filtering delete rows that do not match?
No. AutoFilter hides rows that fail the conditions. Clear the filter to show them again. Advanced Filter can copy matching rows elsewhere, but that also does not delete the original records.

Can Excel for the web use multiple filter criteria?
Yes. Its standard column filters can be applied to multiple columns, using the same general narrowing behavior. For Advanced Filter or a desktop-only workflow, open the workbook in the Excel desktop app.

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.