To filter a worksheet by more than one condition in Excel for Microsoft 365 on Windows, select your data and choose Data > Filter, then set a condition in each relevant column; conditions in different columns are combined as AND. For more flexible logic, use a FILTER formula or Data > Advanced to apply criteria that combine AND and OR rules.
The right method depends on whether you want to inspect a list temporarily, return a live results range, or apply a reusable set of complex conditions. Excel’s built-in column filters are often quickest, while formulas and Advanced Filter handle cases that are awkward to build by clicking.
What “multiple criteria” means in Excel
A criterion is a condition that a value must meet, such as “Region is West,” “Status is Open,” or “Sales are at least 1,000.” When several conditions apply, the key question is how they relate. AND means every stated condition must be true. OR means a record can meet any one of the alternatives.
For example, “Region is West AND Status is Open” returns only rows meeting both conditions. “Region is West OR Region is East” returns rows from either region. A more involved rule might be “Region is West AND Sales are at least 1,000, OR Region is East AND Sales are at least 2,000.” That last pattern mixes AND and OR, so a single quick filter menu may not express it clearly.
Excel’s standard AutoFilter applies conditions from different columns together as AND. Within a column, its custom filter dialog can combine two conditions with AND or OR. For more than two alternatives, or conditions that cross between columns, Advanced Filter or a formula may be easier to manage.
Apply conditions with the regular column filters
AutoFilter is the simplest choice when you want to hide nonmatching rows in place and inspect the remaining records. It does not delete the hidden rows. If the range has clear column headings, Excel usually detects the data block; converting it to a table first can make the range easier to expand and manage.
- Click a cell inside the data, or select the entire range if Excel does not identify it correctly.
- Choose Data > Filter. Drop-down arrows appear in the header row.
- Open a column’s arrow and select values, search for a value, or choose a filter category such as Text Filters, Number Filters, or Date Filters.
- Repeat in other columns. Excel keeps only rows that satisfy the active filters together.
Suppose a sales list has columns for Region, Representative, Status, and Sales. Select West in the Region filter, Open in the Status filter, and set Sales to greater than or equal to 1,000. The visible rows must be in the West region, have an Open status, and meet the sales threshold. This is a convenient way to answer a straightforward “all of these” question.
When filtering dates, Excel may group them by year, quarter, or month, depending on the data and menu. For a threshold or a specific interval, open Date Filters and choose a suitable comparison. Check that the cells contain actual Excel dates rather than text that merely looks like a date; text values may not sort or filter as expected.
Combine conditions inside one column
To filter one column by two conditions, open its drop-down and choose the relevant category, then choose Custom Filter or the corresponding comparison option. For example, a Number Filter can show values greater than or equal to 100 and less than 500. In the custom filter dialog, choose AND if both boundaries must be met. This returns values from 100 through 499.99, assuming ordinary numeric values.
Choose OR when either condition is enough. A text filter could show entries that begin with “North” OR entries that end with “Group.” The conditions can be different types, such as “is greater than 100” OR “is blank,” where the dialog offers those choices. The exact menu wording depends on the column’s detected data type.
For a short list of exact alternatives, selecting multiple checkboxes in the filter menu is often clearer than making a custom rule. For example, selecting East and West in the Region checklist means either value is allowed. This is OR logic within that field. If another column is filtered to Open, the overall result is East or West AND Open.
Use a FILTER formula for a live results list
The FILTER function returns matching rows in a separate area rather than hiding rows in the original data. It is available in Microsoft 365 and Excel 2021 or later. The returned results spill into nearby cells, so leave enough blank space below and to the right; an existing value in the spill area produces a #SPILL! error.
Assume the source data is in A2:D100, with Region in column A, Representative in B, Status in C, and Sales in D. In this illustrative data, rows 2 and 7 are the only rows where Region is West, Status is Open, and Sales are at least 1,000. Enter this formula in an empty cell to return those two complete records:
=FILTER(A2:D100,(A2:A100="West")*(C2:C100="Open")*(D2:D100>=1000),"No matches")
Multiplying the condition arrays makes the formula require every condition to be true: TRUE is treated as 1 and FALSE as 0, so any failed test makes the product zero. The third argument displays No matches if nothing meets the criteria. Keep the source and condition ranges the same height so each row is tested against the correct record.
For OR alternatives, add condition arrays. Assume the same A2:D100 layout, and that rows 2 and 5 are the only rows with sales of at least 1,000 in either West or East. This formula returns those matching rows:
=FILTER(A2:D100,((A2:A100="West")+(A2:A100="East"))*(D2:D100>=1000),"No matches")
Here, addition means West OR East, while multiplication joins that result to the sales threshold with AND. Parentheses matter: they make the intended OR group clear before it is combined with the other test. For several possible values in one field, you can extend the addition pattern, but a long formula can become difficult to maintain. A small criteria table or AutoFilter checklist may be more practical.
Use Advanced Filter for complex criteria
Advanced Filter is useful when criteria need to be written in a small worksheet range, especially when you want several alternatives or a repeatable filter setup. On Windows, choose Data > Advanced. You can filter the list in place or copy matching records to another location. The criteria range must include headings that match the source column headings exactly.
In an Advanced Filter criteria range, conditions written across the same criteria row are AND conditions. Separate criteria rows are OR alternatives. For example, under the headings Region and Status, one row containing West and Open means Region is West AND Status is Open. A second row containing East and Open adds the alternative Region is East AND Status is Open. Together, those rows mean (West AND Open) OR (East AND Open).
To create the criteria range, copy the relevant source headings into a blank area, enter the desired values below them, then open Data > Advanced. Check that List range points to the source data including its headings and Criteria range includes the criteria headings and entries. If copying results, specify a destination range and use Copy to another location. Advanced Filter is less immediate than AutoFilter, but its visible criteria layout can make complex logic easier to audit.
Choose the method that fits the task
- Use AutoFilter to explore a list interactively, hide unwanted rows, or select a few allowed values from drop-down menus.
- Use a custom filter when one column needs two comparison rules, such as greater than one number AND less than another.
- Use FILTER when you want a separate, automatically updating results list that can feed another report. The source data changes are reflected in the result, provided the formula ranges include the changed rows.
- Use Advanced Filter for multiple OR groups, a worksheet-based criteria layout, or a one-time extraction to another location.
AutoFilter changes the view of the source list; it does not create an independent output table. A FILTER formula does create a separate output, but it is a dynamic array and cannot be edited cell by cell while it is spilled. Advanced Filter can copy values to another place, but that copied output does not automatically refresh when source records change; run the filter again to update it.
Common problems and ways to fix them
If expected records disappear, look for filters still active in other columns. A filter applied earlier remains in force, and combined filters are an AND across columns. Choose Data > Clear to remove active filters, or clear an individual column’s filter from its menu before trying again.
Blank rows or columns can cause Excel to treat a list as separate blocks, especially if you click inside one block and apply a filter. Keep the data contiguous, use one clear header row, and avoid merged cells in the range. An Excel table can help keep filters attached as records are added.
Check for inconsistent values too. A cell containing “West ” with a trailing space may not match the visibly similar value “West”; numbers stored as text can behave differently from real numbers. Filters may also seem to omit records if they are outside the selected range. Confirm the range includes every data row and that headings are not accidentally included as records.
For a FILTER formula, #SPILL! usually means something is blocking the destination range; clear the obstructing cells. If the result says No matches, verify each condition independently, including spelling, spaces, date values, and comparison operators. If a formula returns unexpected rows, inspect its parentheses and make sure each condition range lines up row for row with the source range.
Frequently Asked Questions
Can I filter by multiple values in the same Excel column?
Yes. Open the column’s filter menu and select multiple checkboxes to allow any selected value. For comparison rules such as “greater than 10 and less than 50,” use the custom filter dialog and select AND. Use OR when either rule should qualify.
Does Excel treat filters in different columns as AND or OR?
AutoFilter combines active filters in different columns as AND. A row must meet the selection in every filtered column. To express alternatives across columns, use Advanced Filter criteria rows or a FILTER formula with explicit OR logic.
Can I filter for two conditions with a formula?
Yes. In FILTER, multiply logical tests for AND and add tests for OR, using parentheses to group alternatives. FILTER is available in Microsoft 365 and Excel 2021 or later. Make sure the data range and each condition range have matching dimensions.
Why does my filter show no matching records?
Check for another active column filter, spelling differences, extra spaces, numbers stored as text, and dates stored as text. Also confirm that the filter range includes the records you expect. For a formula, test each condition on its own and check that the condition ranges align with the source rows.
Do filtered-out rows get deleted?
No. AutoFilter hides rows that do not match; clearing the filter makes them visible again. If you need an independent list, use FILTER to return a live results range or Advanced Filter to copy matching records elsewhere.
Related
- Excel European Number Format: Decimals and Separators
- Excel Subscript Out of Range: Causes and Fixes
- Excel Engineering Number Format Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LAMBDA Examples for Reusable Custom FunctionsExcel’s LAMBDA function lets you turn a formula into a reusable custom function. For example, a definition with…
- Show Formulas in Excel Print: Steps and FixesTo show formulas in an Excel printout, turn on Show Formulas for the worksheet, then print it as…
- How to Clear an Excel Sheet and Keep All FormulasYes. In Excel for Microsoft 365 on Windows, select the cells, then choose Home > Find & Select…
- Delete an Array Formula in Excel Without Losing DataTo delete an array formula in Excel for Microsoft 365 on Windows, select the formula cell and press…
- Excel Timesheet Calculator Formula: Examples and TipsFor an Excel timesheet with start time in B2, end time in C2, and an unpaid break duration…
- Copy Formulas in Excel Without Creating External LinksTo copy a formula without linking it to another workbook, copy the formula text from the formula bar…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.