Excel Data Filter Multiple Criteria: Practical Methods

Formulas & functions · Updated November 19, 2026

To filter Excel data by multiple criteria, turn on Data > Filter, then set conditions in the column dropdowns: conditions in different columns work together as AND rules, while selected values in one dropdown work as OR choices. For more complex combinations, use a column’s Custom Filter, Advanced Filter, or the FILTER function in Microsoft 365 or Excel 2021.

What “multiple criteria” means in Excel

A criterion is a condition that determines whether a row should remain visible. For example, a sales list might need to show records for the East region where sales are at least $1,000. That request has two conditions: Region equals East and Sales is at least 1,000.

It helps to distinguish two common logical relationships:

  • AND: a row must satisfy every stated condition. Filtering Region to East and Status to Open normally means East and Open.
  • OR: a row can satisfy one condition or another. Selecting East and West in the same Region dropdown means East or West.

Excel’s regular filters are often enough for these cases. More involved logic—such as East and Open, or West and sales above a threshold—may call for Advanced Filter or a formula. The best method depends on whether you want a quick view, a reusable criteria setup, or a separate output that updates automatically.

Apply multiple criteria with AutoFilter

For a typical list with one header row, use the built-in filter controls. In Excel for Microsoft 365 on Windows, click inside the data, then choose Data > Filter. Excel adds a dropdown arrow to each header. Filter one column, then another; the second filter narrows the rows that remain after the first one.

  1. Make sure each column has a clear, unique header and that the data has no completely blank rows or columns interrupting the list.
  2. Click a cell in the list and choose Data > Filter. If the list is an Excel table, filter arrows are usually already available.
  3. Open a header dropdown. Clear Select All, select the values to keep, or choose a text, number, or date filter.
  4. Repeat in other columns to add more conditions. Excel hides rows that do not meet the combined filters.

For instance, filter Region to East and then Status to Open to show only records meeting both conditions. Filters do not delete the other rows; they temporarily hide them. The status bar may show how many records are visible, but always check that your range includes all the intended data and headers.

In Excel for Mac, the equivalent command is on the Data tab as Filter; the dropdown behavior is similar. Excel for the web also supports ordinary column filters, although some desktop-only tools, including Advanced Filter, may not be available there. If an option is missing, open the workbook in the desktop app.

Combine AND and OR conditions

With ordinary dropdown filters, different columns combine as AND. If you filter Department to Marketing and Status to Active, a visible row must match both. Within a single column, selecting several listed values usually makes an OR group: choosing Marketing and Sales includes either department.

For two comparisons on the same field, open its dropdown and choose Number Filters, Text Filters, or the corresponding date option, then select Custom Filter if offered. The dialog can compare a field against two values and lets you choose And or Or. For example, “is greater than 500” AND “is less than 1,000” keeps values between those limits; choosing OR would keep values meeting either comparison, which may include nearly all numbers.

The standard Custom AutoFilter dialog is limited to two conditions for that field at a time. It is not a general-purpose logic builder for any number of nested groups. If your rule needs more conditions, or needs parentheses-like groupings across columns, use Advanced Filter or a formula-based approach instead.

Filter text, numbers, and dates carefully

Excel offers filter choices based on the values in a column. Text filters can include options such as Begins With, Contains, and Equals. Number filters can compare values, select a range, or use options such as Top 10. Date filters can group dates by periods such as months or years, depending on the data and Excel version.

A useful example is finding open orders from a date range. Filter Status to Open, then use the Order Date dropdown to specify the desired period. Because each column filter narrows the same list, the result meets both the status and date requirements. If dates behave unexpectedly, verify that the cells contain real Excel dates rather than text that merely looks like a date; changing the display format alone does not convert text to a date.

Text filters can also use wildcard characters in supported criteria: an asterisk represents a sequence of characters and a question mark represents one character. For example, a condition for names beginning with “Mar” can match multiple longer names. Wildcard rules do not mean the same thing in every Excel feature, so check the selected filter type and preview the visible rows before relying on the result.

Use Advanced Filter for complex criteria

Advanced Filter is useful when ordinary dropdowns cannot express the required logic, or when you want to copy matching records to another location. In desktop Excel for Windows, select a cell in the list and choose Data > Advanced in the Sort & Filter group. The dialog asks for a list range and a criteria range; you can filter the list in place or copy results elsewhere. Excel for Mac desktop versions may expose Advanced Filter from the Data tab, while Excel for the web does not provide the same full desktop workflow.

Set up the criteria range with header names that match the source headers exactly. Put criteria that must all be true on the same row; put alternative criteria on separate rows. For example, a criteria range could have headers Region and Status. One row containing East and Open means East AND Open. A second row containing West and Open adds the alternative West AND Open. In effect, the rows express (East AND Open) OR (West AND Open).

For a comparison such as sales greater than 1,000, put a matching field heading in the criteria range and enter the comparison beneath it using the appropriate criteria syntax. To express two alternatives on the same field, use separate criteria rows. Avoid placing a condition beneath a misspelled or differently named header: Advanced Filter relies on header matching, and a mismatch can produce incorrect or empty results.

When copying results, choose a destination outside the source list and make sure the output area has room. Advanced Filter creates a result snapshot; it does not refresh like a dynamic formula when source values change. Run it again after the data or criteria change. If you need a repeatable, refreshable transformation over a large dataset, Power Query may be a more suitable alternative, though its setup is more involved than a quick filter.

Return matching rows with the FILTER function

The FILTER function returns matching records in a separate, spilling range, leaving the original data intact. It is available in Microsoft 365 and Excel 2021 or later. It is useful for dashboards and reports where the result should recalculate when source data changes. Keep the formula outside the source data and leave enough blank cells for the result to spill.

Assume A1:D5 contains headers Region, Rep, Status, and Sales, with four sample records: East/Ana/Open/1200; East/Ben/Closed/1500; West/Ana/Open/1800; and East/Ana/Open/900. With Region in A2:A5 and Sales in D2:D5, this formula returns records where Region is East AND Sales is at least 1,000:

=FILTER(A2:D5,(A2:A5="East")*(D2:D5>=1000),"No matches")

The multiplication combines the two TRUE/FALSE tests as AND. From the stated sample, the result is the first record only: East, Ana, Open, 1200. The final argument supplies a message if nothing meets the conditions. The criteria arrays need to line up row for row with the data array.

To return records where Region is East OR Status is Closed, use addition between the tests. With the same A1:D5 layout and sample records, the formula is:

=FILTER(A2:D5,(A2:A5="East")+(C2:C5="Closed"),"No matches")

That result contains three records: the first, second, and fourth. The second meets both tests, but it still appears once. For more complicated rules, combine multiplication and addition carefully with parentheses so the intended AND groups and OR alternatives are unambiguous. If formulas show a spill error, check for nonblank cells blocking the output range.

Choose the right method

Use AutoFilter for interactive exploration: it is quick, visible, and easy to adjust, but it hides rows in the original list. Use Custom Filter when one column needs a pair of comparisons. Choose Advanced Filter when you need a criteria grid with multiple AND/OR groups or a copied snapshot of matching rows. Choose FILTER when you want a separate result that updates as source data changes.

For recurring imports and repeatable cleanup, Power Query can filter rows through its query steps and refresh from the source. It is often better for a maintained data-preparation process than repeatedly configuring worksheet filters. It may be unnecessary for a one-time question. PivotTables are another option for summarizing categories and measures, but they aggregate data rather than simply displaying every matching source row, so they are not a direct replacement for a row-level filter.

Common problems and practical checks

  • Filters affect only part of the list: check for blank rows or columns that split the data, then remove and reapply the filter to the complete range. Converting a consistent dataset to a table can help its range expand as rows are added.
  • The result is empty: verify spelling, spaces, data types, and whether the conditions are AND when you intended OR. A number stored as text may not behave like a numeric value.
  • New rows are not included: confirm they are inside the filtered range. A table generally expands when data is entered directly below it, but a manually selected range may not.
  • Only some rows appear after clearing a filter: use the header dropdown’s clear-filter option or choose Data > Clear to clear filters. Clear removes filter criteria, while Data > Filter toggles the filter controls.
  • Sorted data seems incomplete: remember that filtering hides rows rather than removing them. Before copying or calculating results, confirm whether hidden rows should be included in the operation.

Before sharing a filtered workbook, consider whether the recipient should see the hidden rows. A filter is not a security feature: it does not protect the underlying records. Use appropriate workbook permissions or remove sensitive data rather than relying on hidden rows.

Frequently Asked Questions

Can Excel filter multiple columns at the same time?
Yes. Apply a filter to each column in turn. Excel combines those column filters, so a row must meet all the active column conditions.

How do I filter for two values in the same column?
Open the column dropdown and select both values. That includes rows matching either value. For two comparisons, such as greater than one number and less than another, choose the column’s Custom Filter and set the conditions to And.

Can I use AND and OR together in one filter?
Yes, but the standard dropdowns have limits. Use Advanced Filter’s criteria range for grouped conditions, or use a FILTER formula with multiplication for AND and addition for OR. Carefully group mixed conditions with parentheses.

Does filtering delete rows that do not match?
No. Ordinary filters hide nonmatching rows; they remain in the worksheet. Advanced Filter can copy matching rows to another location, but that does not automatically delete the other source records.

Why does my filter miss new records?
The new rows may be outside a manually selected filter range. Expand or reapply the filter to cover the full list, or use an Excel table so data entered directly below it can be incorporated into the table range.

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.