To filter Excel data by multiple criteria, use Data > Filter and set a condition in each relevant column; Excel keeps rows that meet all those column filters. For more flexible AND/OR logic, use Advanced Filter or, in Microsoft 365, the FILTER function.
How multiple criteria work in Excel
Excel’s standard filter is easiest to understand as a set of tests applied to rows. When you filter one column and then another, a row must pass both filters to remain visible. For example, filtering Region to West and Status to Open returns records that are in the West and are open. This is AND logic across columns.
Within a single column, you can usually select several listed values at once. Selecting West and East in the Region filter means West or East. Excel combines the selected values in that field as alternatives, while filters in other fields still narrow the results. Knowing this distinction helps avoid a common misconception: adding another column filter does not broaden the results; it makes the conditions more restrictive.
Before filtering, give each column a clear header and keep the data in one continuous rectangular range. Avoid completely blank rows or columns inside the data, since Excel may treat them as boundaries and omit records. Turning the range into a table with Home > Format as Table can make filtering easier to manage as new rows are added.
Use AutoFilter for common combinations
For everyday filtering in Excel for Microsoft 365 on Windows, click a cell in the data and choose Data > Filter. Drop-down arrows appear in the header row. Open a column’s arrow, select the values to keep, and choose OK. Repeat in other columns to combine conditions. You can also select the full range first if Excel does not identify the data block correctly.
For example, imagine a list with Region, Rep, Status, and Sales columns. To show open orders from the West, filter Region to West and Status to Open. To narrow those further to a particular representative, select that name under Rep. Excel hides rows that do not qualify; it does not delete them or change the underlying values.
- Several values in one field: check each value you want to include in that column’s filter list. The selected values are treated as alternatives.
- Conditions in different fields: apply a filter to each field. A row must satisfy each field’s filter.
- Remove a filter: reopen the relevant drop-down and choose Clear Filter From followed by the field name, or use Data > Clear to clear filters on the range.
On Excel for Mac, use the Data tab’s Filter command; the filter arrows and basic workflow are similar. Excel for the web also supports ordinary table and range filters, though the exact ribbon layout can vary. If you cannot find a command, check that the active cell is inside the data and that the ribbon is showing the Data tab.
Set two conditions in one column
When one field needs a comparison rule rather than a list of values, open its drop-down and choose Number Filters, Date Filters, or Text Filters, as appropriate. The menu includes options such as Greater Than, Before, Begins With, and Custom Filter. For numeric Sales data, for instance, a condition of greater than 500 and less than 2,000 limits results to that interval.
The Custom AutoFilter dialog lets you set two conditions for the same column and choose whether they are joined by AND or OR. Choose AND to find values that satisfy both tests, such as sales greater than 500 and less than 2,000. Choose OR to find values that meet either test, such as sales below 100 or above 5,000. The two-condition limit is important: for longer or more involved logic, Advanced Filter or a formula is usually a better fit.
Text filters can use wildcards. An asterisk (*) represents any number of characters, and a question mark (?) represents one character; place a tilde (~) before a wildcard when you need to search for that character literally. For example, a text condition for North* can match entries beginning with North. Wildcards are for text matching, not a substitute for numeric comparisons.
Use Advanced Filter for complex criteria
Advanced Filter is useful when criteria span multiple fields, when you need more than two tests in one field, or when you want to copy matching rows to another location. It uses a small criteria range on the worksheet. The criteria range must have headers that correspond to source columns for ordinary criteria, and the placement of criteria determines the logic.
- Make sure the source data has a single header row and no blank rows within the range.
- In an empty area, copy the source headers needed for the criteria. Put AND conditions on the same criteria row. Put alternative criteria on separate rows to express OR.
- Click in the source data, then choose Data > Sort & Filter > Advanced on Windows.
- In the dialog, confirm the List range and enter the Criteria range. Choose Filter the list, in-place or Copy to another location, then click OK.
For example, put Region and Status headers side by side in the criteria range, with West and Open beneath them on the same row. That asks for West AND Open. Put another pair, such as East and Open, on the next row to include West-and-Open OR East-and-Open. If you copy results elsewhere, choose a destination outside the source list. Advanced Filter is available in the desktop application; Excel for the web does not offer the same Advanced Filter dialog. On Mac, look for Data > Advanced; the dialog’s labels may differ slightly from Windows.
Advanced Filter also supports formula criteria. With source headers in A1:D100, the first data row in row 2, and the fields Region in A and Sales in D, enter this criterion under a new header such as Match in F1:
With the source data in A1:D100 and its first data row in row 2, enter the following in F2 beneath the unique criteria header Match. It returns TRUE for West rows with sales of at least 1,000, so Advanced Filter keeps only those matching rows:
=AND($A2="West",$D2>=1000)
Set the criteria range to F1:F2. The formula refers to the first data row, and the row reference is allowed to adjust as Excel evaluates subsequent records. A formula-based criteria range is handy for logic that is awkward to express with ordinary header-and-value criteria. Make sure the criteria header is not identical to a source header, and check that the formula evaluates as intended before applying the filter.
Filter with the FILTER function
In Microsoft 365, the FILTER function returns matching rows in a new area instead of hiding nonmatching rows in the source. This is useful for a live report, a separate results sheet, or a view that should update when source values change. The function is available in Microsoft 365 and Excel 2021 or later; older perpetual versions may not support it.
Assume headers are in A1:D1 and records are in A2:D100, with Region in column A and Status in column C. Enter the formula below in an empty cell outside the source data. It returns all four columns for records where Region is West AND Status is Open, or displays No matches if none qualify:
=FILTER(A2:D100,(A2:A100="West")*(C2:C100="Open"),"No matches")
Multiplying the two TRUE/FALSE tests makes both conditions necessary: TRUE is treated as 1, and a row qualifies only when both tests produce 1. To return West OR East records, use addition between the region tests. With the same A2:D100 layout, this formula returns the records from either region:
=FILTER(A2:D100,((A2:A100="West")+(A2:A100="East"))>0,"No matches")
FILTER results spill into neighboring cells, so leave enough empty space below and to the right of the formula. If the spill area is blocked, Excel reports a spill error. The include ranges must also be the same height as the data range; mismatched range sizes can cause an error. A formula result is a separate, dynamic output, not an AutoFilter applied to the original list. You can still sort or format the output, but take care not to put unrelated content in the spill area.
Choose the right filtering method
The best method depends on whether you want to inspect the original list, build a reusable report, or express complicated logic. Standard AutoFilter is usually fastest for temporary analysis and ordinary AND conditions across columns. The Custom AutoFilter dialog handles two comparisons in one field. Advanced Filter is a strong option for complex criteria and a copied snapshot of matching rows. FILTER is convenient when you want formula-driven results that recalculate as the source changes.
- Choose AutoFilter to explore a table interactively or select a handful of values.
- Choose Advanced Filter for criteria ranges, combinations that use several OR rows, or a one-time copied result.
- Choose FILTER for a dynamic extracted list in Microsoft 365 or Excel 2021 and later.
These approaches do not have identical behavior. A standard filter hides rows, so totals that use functions such as SUBTOTAL can respond to visible records, depending on the function used. Advanced Filter can copy matching records, which produces a separate result that does not automatically update. FILTER recalculates, but its output is an array and may require a clear destination range. None of these methods should be mistaken for deleting unwanted records.
Common problems and practical checks
If expected rows are missing, first inspect every active filter arrow. A filter left over from an earlier analysis can combine with the new condition and make the results seem too narrow. Use Data > Clear and reapply the desired conditions if you are unsure which filters are active. Also check for inconsistent entries such as trailing spaces, different spellings, or numbers stored as text. A value that looks like 100 may not behave like a numeric 100 if it is stored as text.
If Excel filters only part of a list, check for blank rows or columns that divide the data and verify that the selected range includes every record. A table is often a practical fix for a growing list because it expands when rows are added immediately below it. If a FILTER formula returns an error, confirm that the include arrays match the number of rows in the data array and that the result area is unobstructed.
Finally, distinguish between conditions that should narrow results and alternatives that should broaden them. “West and Open” means both conditions must be true. “West or East” means either region is acceptable. For complicated logic, write the intended rule in plain language first, then map each AND or OR relationship to the filter dialog, criteria rows, or formula. This small planning step prevents many logic mistakes.
Frequently Asked Questions
Can I filter Excel by two columns at once?
Yes. Apply a filter to each column using Data > Filter. Excel keeps rows that satisfy both column filters, so filtering Region to West and Status to Open returns records that meet both conditions.
How do I use OR criteria in an Excel filter?
For several allowed values in one field, select each value in that field’s drop-down list. For more complex OR logic, use Custom AutoFilter’s OR option, put alternative criteria on separate rows in an Advanced Filter criteria range, or combine tests with addition in a FILTER formula.
Can I filter a column for more than two conditions?
The Custom AutoFilter dialog offers two conditions for a field. To handle more, select multiple listed values where that fits, use Advanced Filter with a criteria range, or build a FILTER formula with the required tests.
Does filtering delete rows that do not match?
No. AutoFilter hides nonmatching rows; the records remain in the worksheet and can be shown again by clearing the filter. Advanced Filter can copy matching records to another location, while FILTER displays matching results elsewhere.
Why does my FILTER formula return an error?
Check that the data and criteria ranges have matching row counts, and that the cells where the results need to spill are empty. Also verify the criteria values and quotes. If no records match, include a third argument such as No matches to return a message instead of an empty-result error.
Related
- Excel Data Validation Lists: Hard-Coded or Flexible?
- Excel Data Validation List: How to Exclude Blanks in Excel
- Excel Data Validation List From Another Sheet: Steps
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- VLOOKUP Showing Formula? Causes and Fixes in ExcelIf VLOOKUP is showing as text instead of returning a result, first turn off Formulas > Show Formulas;…
- Filter Excel Rows by Cells That Contain Specific TextTo filter Excel rows by text inside a cell, turn on AutoFilter, open the filter arrow in the…
- Excel MIN Ignore Errors: Formulas and Common FixesTo find the smallest number in a range while ignoring error cells, use =AGGREGATE(5,6,A2:A10). The 5 selects MIN,…
- Excel Filter Merged Cells: Why Only the First Row ShowsYes—when a cell is merged vertically across several rows, Excel stores its value only in the top-left cell,…
- Copy Formulas in Excel to a New Sheet Without ErrorsTo copy formulas to a new sheet in Excel, select the formula cells, press Ctrl+C, open the destination…
- How to Find XLOOKUP in Excel and Use It in a FormulaTo find XLOOKUP in Excel for Microsoft 365 on Windows, open the Formulas tab and choose Lookup &…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.