To filter rows by multiple criteria in Excel, use the FILTER function and multiply criteria for AND logic or add them for OR logic. With data in A2:E6, where column A is Region and column C is Product, this returns rows that are both East and Monitor: =FILTER(A2:E6,(A2:A6="East")*(C2:C6="Monitor"),"No matches").
How FILTER works with criteria
FILTER returns rows or columns from a range when the corresponding entries in an include argument meet a condition. Its syntax is =FILTER(array,include,[if_empty]). The array is the data to return; include is a TRUE/FALSE test for each row; and if_empty is optional text or a value to show if nothing qualifies. In the opening example, the formula returns the matching records from columns A through E, not just the two columns used as tests.
FILTER is available in Microsoft 365 and Excel 2021 or later. It returns a dynamic array: Excel places the results in the formula cell and spills them into adjacent cells as needed. You do not have to copy the formula down. If the matching records change, the displayed results update automatically.
The examples below use this small dataset, with headers in row 1 and records in rows 2 to 6:
- Row 2: East, Maya, Laptop, 1200, January 5, 2026
- Row 3: West, Leo, Monitor, 450, January 7, 2026
- Row 4: East, Leo, Monitor, 700, January 9, 2026
- Row 5: East, Maya, Monitor, 300, January 12, 2026
- Row 6: West, Maya, Laptop, 900, January 15, 2026
Assume the columns are A Region, B Rep, C Product, D Sales, and E Order Date. Enter a formula in an empty area with enough room to the right and below for the results to spill.
Use AND when every condition must match
Use the asterisk between logical tests when all conditions must be true for a row. Excel treats TRUE as 1 and FALSE as 0 in this arithmetic. Multiplying the tests leaves a nonzero result only when both are true. In the opening formula, a row must have East in column A and Monitor in column C. The results are rows 4 and 5: both are East Monitor records.
For example, to find East sales of at least 500, test the region and the numeric threshold together. With the sample data in A2:E6, this formula returns rows 2 and 4, with sales of 1200 and 700:
=FILTER(A2:E6,(A2:A6="East")*(D2:D6>=500),"No matches")
The criteria ranges must line up with the rows in the returned array. Here, A2:A6 and D2:D6 each have five entries, matching the five rows in A2:E6. If one test range starts on row 3 or extends farther than the array, the formula may return an error or test the wrong records.
Use OR when any condition may match
Use addition between tests when a row can qualify by meeting either one. A sum above zero counts as a match, even if both tests are true. For example, with the sample data in A2:E6, this returns records where the product is Laptop or sales are at least 700. The matching rows are 2, 4, and 6:
=FILTER(A2:E6,(C2:C6="Laptop")+(D2:D6>=700),"No matches")
Row 2 meets both conditions, but it appears once in the filtered output; FILTER does not duplicate a row just because two tests match. Row 4 qualifies by sales, while row 6 qualifies by product. This is often the useful distinction between multiplication and addition: AND demands every test, while OR accepts at least one.
For OR tests involving multiple possible text values, the same pattern works. With product names in C2:C6, this returns rows containing either Laptop or Monitor—which, in this particular sample, is every row:
=FILTER(A2:E6,(C2:C6="Laptop")+(C2:C6="Monitor"),"No matches")
Combine AND and OR conditions
Parentheses let you group tests to express more specific logic. For example, a reader might need East records that are either Laptop orders or have sales of at least 1000. The region test is required, while either of the other tests is sufficient. With the sample data in A2:E6, only row 2 qualifies:
=FILTER(A2:E6,(A2:A6="East")*((C2:C6="Laptop")+(D2:D6>=1000)),"No matches")
Read this as East AND (Laptop OR sales at least 1000). Without the parentheses, Excel would evaluate the expression according to its operator rules, and the intended grouping could be lost. When a formula has more than two tests, writing the logic in plain language first—such as “East and either Laptop or high-value”—helps you place the parentheses correctly.
You can also exclude a value with not-equal, written as <>. With sample data in A2:E6, this returns rows that are not West and have sales of at least 500. The results are rows 2 and 4:
=FILTER(A2:E6,(A2:A6<>"West")*(D2:D6>=500),"No matches")
Filter by dates, partial text, or criteria cells
Dates in Excel are stored as serial values, so comparisons such as greater than or equal to work when the cells contain real Excel dates. With order dates in E2:E6, the following returns orders on or after January 10, 2026; rows 5 and 6 qualify. DATE avoids ambiguity about how a typed date is interpreted in different regional settings:
=FILTER(A2:E6,E2:E6>=DATE(2026,1,10),"No matches")
To match text that contains a phrase rather than exactly equals it, use SEARCH inside ISNUMBER. With product descriptions in C2:C6, this formula finds cells containing “lap” without regard to capitalization; it returns the Laptop records in rows 2 and 6:
=FILTER(A2:E6,ISNUMBER(SEARCH("lap",C2:C6)),"No matches")
SEARCH is not case-sensitive. If case matters, Excel also has FIND, which is case-sensitive. Partial-text tests can be combined with other tests using the same multiplication and addition patterns.
Criteria cells make a report easier to reuse than editing text inside a formula. Suppose H2 contains a region name and H3 contains a minimum sales value. With the sample data in A2:E6 and those criteria entered in H2 and H3, use:
=FILTER(A2:E6,(A2:A6=H2)*(D2:D6>=H3),"No matches")
Changing H2 or H3 changes the results. For example, East in H2 and 500 in H3 returns rows 2 and 4. Make sure the criterion cells contain values of the right type: a number criterion should be numeric, and a date criterion should be an actual Excel date rather than text that only looks like one.
Use an Excel Table for growing data
Fixed ranges such as A2:E6 are clear for demonstrations, but they do not automatically expand when you add records below row 6. An Excel Table is a practical option for a growing list: select the data, choose Insert > Table, and confirm that the table has headers. Tables expand as you add rows and provide named columns in formulas.
Suppose the table is named SalesTable and has columns Region, Rep, Product, Sales, and Order Date. With those table names, this formula filters for a region and minimum sales value stored in H2 and H3:
=FILTER(SalesTable,(SalesTable[Region]=H2)*(SalesTable[Sales]>=H3),"No matches")
Structured references can be easier to read than cell coordinates, especially in a wide workbook. The filtered result still spills from the formula cell; it does not become part of the source table. Keep the spill destination clear, and avoid placing the formula inside a table, because dynamic array formulas cannot spill within table cells in the usual way.
Common errors and how to fix them
- #SPILL! means Excel cannot place the complete result in the spill range. Clear cells blocking the output, check for merged cells, or move the formula to an area with more room.
- #VALUE! can result when the include array and the data array have incompatible dimensions. Check that every criteria range covers the same number of rows as the array.
- No matches or an empty result: confirm spelling, spaces, number formats, and date values. A space at the end of a text entry can prevent an exact match.
- Formula appears not to update: check that calculation is set to Automatic under Formulas > Calculation Options. Also confirm that the source values actually changed and that the formula references the intended range.
- Comma syntax is rejected: some regional settings use semicolons between function arguments. Excel may require semicolons in place of the commas shown here, depending on your settings.
The optional if_empty argument is useful because FILTER otherwise returns a calculation error when no records qualify. In the examples, “No matches” is that fallback text. If you prefer a blank result, use an empty string as the third argument. Check that the formula itself is valid before relying on this fallback: it handles no matches, not every possible formula error.
FILTER versus the Data Filter command
The ribbon’s Data > Filter command is useful when you want to inspect and narrow the original list interactively. It adds dropdowns to the header row, and you can filter different columns at once. It changes which source rows are visible rather than creating a separate, automatically spilling result range.
FILTER is a better fit when you want a formula-driven output area, reusable criteria cells, or logic involving several conditions with explicit AND and OR grouping. For a quick one-time selection, the ribbon filter may be simpler. Advanced Filter is another built-in option for extracting records using a criteria range, but it is not the same as a dynamic array formula and does not update as a FILTER result does.
On Microsoft 365 for Windows, use Data > Filter for the interactive filter. The same general command is available in Excel for Mac, though ribbon layout can vary by version. FILTER formulas work in supported Excel versions on Windows and Mac; Excel for the web also supports the function in current Microsoft 365. A workbook opened in an older Excel version that lacks FILTER may show a _xlfn. prefix or fail to calculate the formula.
Frequently Asked Questions
How do I filter multiple criteria in Excel with AND?
Use multiplication between the criteria tests inside FILTER’s include argument. Each row must pass every test to remain in the result.
How do I use OR criteria in Excel FILTER?
Add the tests with +. Any row with at least one true test qualifies; put parentheses around OR tests when combining them with required conditions.
Can FILTER handle more than two criteria?
Yes. Multiply additional required tests for AND, or add alternatives for OR. Group mixed logic with parentheses and keep all test ranges aligned with the filtered array.
Why does my FILTER formula show #SPILL!?
Excel cannot write the full result into the destination area. Clear the cells in the spill range, unmerge cells if needed, or move the formula.
Does FILTER work in older Excel versions?
FILTER is available in Microsoft 365 and Excel 2021 or later. For older versions, use Data > Filter, Advanced Filter, or another formula-based approach compatible with that version.
Related
- Excel Engineering Number Format Explained
- Excel Employee Work Schedule: Setup and Tips
- Excel EIN Number Format: Keep All 9 Digits Visible
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Bing Chat Excel Formula Not Calculating? Causes & FixesIf a formula from Bing Chat is not calculating, first paste it into an Excel cell and confirm…
- Excel Top N Formula: Find the Highest Values and RowsTo return the top N numbers in Excel for Microsoft 365, use =LARGE(B2:B20,SEQUENCE(3)) to spill the three largest…
- Excel Filter by Color and Value: How It WorksYes. In Excel for Microsoft 365 on Windows, open a column’s filter menu and choose a color under…
- Excel Formula for Budget Amount RemainingIf your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another…
- Text After an Excel Formula: Add Labels and Sheet NamesTo put text after an Excel formula, join the formula result to a quoted text string with an…
- Excel Sum by Category: Formulas and PivotTablesTo sum values by category in Excel, use SUMIF: with category names in A2:A100, amounts in B2:B100, and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.