How to Filter With Multiple Or Criteria in Excel

Why This Task Matters in Excel

Every analyst eventually reaches a point where a simple one-condition filter is not enough. Suppose you manage sales data for several regions and product categories, or you run a help-desk dashboard that tracks ticket status, priority, and assignee. Real business questions rarely sound like “show me everything where Region equals North.” Instead, decision-makers ask compound questions such as:

• “Show me orders from North or East.”
• “List customers in Technology or Healthcare industries.”
• “Return all tickets marked Closed or Resolved.”

The ability to apply multiple OR criteria dramatically reduces manual labor. Without it, you would repeatedly copy, paste, and re-filter, risking errors every time you hand-stitch partial results. In financial services, a portfolio manager may need to see securities in Banking or Insurance to monitor sector performance. In HR, recruiters often pull lists of applicants who live in New York or New Jersey. Marketing teams routinely slice campaigns by Facebook or Instagram channels.

Excel is superb for this task because:

1. It combines a rich formula language with built-in user-interface filters, giving you options for both quick exploration and fully automated pipelines.
2. Modern functions such as FILTER spill results dynamically, so dashboards update themselves when source data changes.
3. Traditional techniques such as Advanced Filter, AutoFilter, and structured Table filters remain compatible with older versions and low-code workflows.

Failing to master multi-criteria filtering leads to bloated workbooks, duplicated data, and reporting delays. More importantly, it severs the link between source and report, forcing analysts to reconcile data by hand—an auditing nightmare. The skill therefore connects directly to data-cleaning, dashboard design, scenario analysis, and every discipline where Excel still reigns. When you know how to filter with multiple OR criteria, you can pivot quickly, run what-if analyses, and maintain a single source of truth that refreshes itself.

Best Excel Approach

For dynamic, formula-driven reports in Microsoft 365 or Excel 2021+, the FILTER function offers the most elegant solution. FILTER returns only the rows that meet the inclusion test, and you can combine tests with arithmetic operators. When you add two logical arrays, Excel treats TRUE as 1 and FALSE as 0, effectively creating an OR relationship.

Syntax overview:

=FILTER(return_range, (logical_test1) + (logical_test2) + … )

Parameter explanations:

• return_range – the full block of data you want spilled into the result.
• logical_test1, logical_test2, … – comparisons that evaluate to TRUE/FALSE for each row.
• A plus sign between tests creates OR logic; a multiplication symbol creates AND logic.

Why this is best:

• It is entirely formula-driven, requiring no clicks once set up.
• It spills results of any size; you never have to resize the output range.
• It recalculates automatically, ideal for dashboards or templates distributed across teams.

When to choose alternatives:

• If you are on Excel 2019 or earlier and cannot access FILTER, you will need legacy methods such as AutoFilter, Advanced Filter, or SUMPRODUCT-based formulas.
• When working with millions of rows that exceed worksheet limits, Power Query is more scalable.

Recommended formula template

=FILTER(Data, (Region="North") + (Region="East"))

Alternative for pre-365 versions

=IFERROR(
    INDEX(return_cols, SMALL(IF((cond1)+(cond2), ROW(ref)-ROW(first_row)+1), ROWS($A$1:A1))),
"")

(Entered as an array formula in older Excel.)

Parameters and Inputs

To make FILTER work reliably, you must prepare:

1. Consistent data types

• Text comparisons must reference standardized strings—no extra spaces or mismatched cases (unless you purposely apply case-sensitive functions).
• Numeric tests should use proper number formatting, not text numbers.

2. Full return range

• Include every column you want back. For a table in [A1:F500], return_range might be [A1:F500].
• Avoid including headers inside the formula; keep them above the spill range.

3. Logical arrays

• Each logical test must be the same height as return_range to avoid #VALUE errors.
• Use structured references (Table[Region]) for automatic expansion.

Optional parameters

• FILTER offers a third argument, if_empty, to display custom text when nothing matches.
• Input cells for criteria let end users drive the filter without editing formulas.

Input validation

• Use data-validation drop-downs so criteria cannot be misspelled.
• Trap errors with IFERROR or by supplying an if_empty argument such as \"No results\".

Edge cases

• Blank cells in numeric columns may trigger #VALUE in some logical expressions; wrap comparisons in IF( cell=\"\", FALSE, test ).
• Mixed data types (numbers stored as text) create invisible mismatches; run VALUE() or TEXT() conversions first.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a small sales table in [A1:D11] with columns: OrderID, Region, Rep, Amount. We want all rows where Region equals North or East.

Step 1 – Convert to a Table
Select [A1:D11] and press Ctrl+T. Name it SalesData for clear references.

Step 2 – Add criteria cells
In G2 type “North”, in G3 type “East”. Label G1 as “Region criteria”.

Step 3 – Write the formula
In I2 enter:

=FILTER(SalesData, (SalesData[Region]=G2) + (SalesData[Region]=G3), "No matching sales")

Step 4 – Observe result
Rows with Region North or East spill below I2. If you delete G2, only East rows remain.

Why it works

• SalesData[Region]=G2 produces an array like [TRUE, FALSE, TRUE…].
• Adding two arrays converts TRUE to 1, FALSE to 0. Any row with at least one TRUE yields a positive integer, passing the FILTER test.

Common variations

• Replace equals with a numeric test, e.g., Amount greater than 10000.
• Reference a list of several criteria cells and use COUNTIF to create the logical OR dynamically.

Troubleshooting

• #CALC! with message “Spill range isn’t blank” means something blocks the spill area—clear space below the formula.
• #VALUE indicates misaligned ranges—check that every logical array matches row count.

Example 2: Real-World Application

Scenario: A customer service team tracks tickets in a 3,000-row table called Tickets with columns TicketID, Status, Priority, AssignedTo, OpenDate. Managers need a live view of tickets that are either Open or Pending and have Priority High.

Step 1 – Data preparation
Ensure “Status” uses consistent spelling (Open, Pending, Closed). Priority should be High, Medium, Low.

Step 2 – Criteria cells
In H2:H3 list statuses to include: “Open”, “Pending”. In H5 enter “High” for priority.

Step 3 – Build logical tests
Logical OR for status:

(ISNUMBER(MATCH(Tickets[Status], H2:H3, 0)))

Logical AND for priority:

(Tickets[Priority]="High")

Step 4 – Combine tests in FILTER

=FILTER(
    Tickets,
    (ISNUMBER(MATCH(Tickets[Status], H2:H3, 0))) * (Tickets[Priority]="High"),
    "Nothing meets criteria"
)

Explanation

• MATCH returns the position of each Status in [H2:H3] or #N/A if not found. ISNUMBER converts matches to TRUE, others to FALSE.
• Multiplication enforces AND (must be High) while the ISNUMBER expression alone supports OR across Open or Pending.

Integration with other features

• Create a slicer connected to the Table for Status and Priority; when you select Open or Pending, the FILTER result updates instantly.
• Feed the FILTER output into a PivotTable for aggregated metrics (average resolution time).

Performance considerations

• On 3,000 rows, FILTER recalculates almost instantly, but volatile functions inside MATCH (such as INDIRECT) could slow things down. Use direct references.

Example 3: Advanced Technique

Edge case: You have an inventory list exceeding 50,000 rows with columns SKU, Category, Quantity, ReorderLevel. Leadership wants items where Quantity beneath ReorderLevel or Category equals “Discontinued”, but the workbook pushes Excel limits and you must avoid slow formulas.

Solution: Stage logic in helper columns and minimize array calculations.

Step 1 – Insert two helper columns
In Table column “NeedRestock”:

=[@Quantity] < [@ReorderLevel]

In “Flag”:

=OR([@NeedRestock], [@Category]="Discontinued")

Step 2 – Filter only on helper column

=FILTER(Inventory, Inventory[Flag], "All good")

Why advanced

• Evaluating NeedRestock row-by-row avoids performing two large arrays inside the FILTER formula each time; Excel reads a single Boolean column instead.
• This technique helps with CPU cache locality and lowers recalculation time on big sheets.

Error handling and edge cases

• If ReorderLevel is blank, NeedRestock yields FALSE via implicit zero comparison. Optionally wrap NeedRestock in IF( ISBLANK([@ReorderLevel]), FALSE, … ).
• If Category might have trailing spaces, nest TRIM().

Professional tips

• Convert helper columns to Power Query custom columns for even higher scalability.
• Use a one-click Refresh All workflow to pull latest inventory from your ERP.

When to use this vs simpler approaches

• Choose helper columns when formulas become unwieldy or when several departments need to read the logic; each column documents a single business rule.
• Use direct inline comparisons for ad-hoc analysis on small data.

Tips and Best Practices

1. Build criteria lists on the side and reference them with MATCH or COUNTIF; this scales from two to twenty OR conditions without rewriting the formula.
2. Wrap your FILTER formula inside SORT or SORTBY to present results cleanly:

=SORTBY(FILTER(Data, tests), 4, -1)  // sort by fourth column descending

3. Color-code criteria cells with Data Validation to alert users if they clear or mistype a value.
4. Store data in Excel Tables to gain structured references and automatic range expansion—no need to adjust row numbers when data grows.
5. Turn on Workbook Calculation = Automatic except for very large models; controlled recalculation keeps dashboards live without manual refresh.
6. Document logic with named formulas (Formulas ➜ Name Manager); a name like StatusOR makes the main FILTER expression readable.

Common Mistakes to Avoid

1. Mixing AND and OR without parentheses

• Excel follows math precedence; (test1)+(test2)*(test3) may not produce what you expect. Wrap OR portion inside parentheses, then multiply for AND.

2. Misaligned ranges

• FILTER fails if return_range is [A2:E3000] but logical array covers only 2,999 rows. Always use structured references or absolute ranges of identical size.

3. Forgetting empty-result handling

• A blank spill looks alarming; use the third FILTER argument to display “No matching rows.”

4. Hard-coding text with invisible spaces

• “North ” (with a trailing space) never equals “North”. TRIM or CLEAN data during import to prevent silent mismatches.

5. Overusing volatile functions like INDIRECT

• INDIRECT forces recalculation on every worksheet change, slowing large models. Use INDEX for dynamic range references instead.

Alternative Methods

When to use each

• Choose AutoFilter for a one-off visual check.
• Use Power Query for enterprise-scale ETL pipelines.
• SUMPRODUCT or INDEX for legacy shared workbooks under strict version constraints.
• FILTER for daily reporting in modern Excel.

Migration strategies

• Replace a SUMPRODUCT-based list with FILTER once all users upgrade; same criteria cells can drive the new formula.
• Export an Advanced Filter spec to Power Query’s “Filter Rows” step to automate refresh.

FAQ

When should I use this approach?

Use FILTER with multiple OR criteria when you need a live, formula-based subset of data that updates automatically—ideal for dashboards, KPI scorecards, or templated client reports where criteria change frequently.

Can this work across multiple sheets?

Yes. Point return_range to Sheet1!SalesData and criteria to Sheet2 cells. Example:

=FILTER(Sheet1!SalesData, (Sheet1!SalesData[Region]=Sheet2!G2) + (Sheet1!SalesData[Region]=Sheet2!G3))

Just ensure both logical arrays live on the same sheet as their referenced range to avoid performance penalties.

What are the limitations?

• FILTER is unavailable in Excel 2019 and earlier.
• A spilled range cannot overlap another non-empty cell.
• Large arrays can hit the one-million-row Excel limit—use Power Query or databases for bigger data.

How do I handle errors?

Wrap in IFERROR or supply the if_empty argument:

=FILTER(Data, tests, "No rows meet criteria")

Debug by testing each logical array individually with =--tests to see TRUE/FALSE counts.

Does this work in older Excel versions?

Not natively. Use INDEX/SMALL/IF array formulas or SUMPRODUCT to simulate FILTER, or rely on Advanced Filter. Upgrading to Microsoft 365 is strongly recommended for maintainability.

What about performance with large datasets?

• Helper columns restrict calculations to simple row operations.
• Avoid volatile functions.
• Convert spill output to a PivotTable cache for additional aggregation without recalculating source logic.
• For truly massive data, offload to Power Query or Power Pivot.

Conclusion

Mastering multi-criteria OR filtering empowers you to answer nuanced questions instantly, transform static reports into dynamic dashboards, and eliminate repetitive manual tasks. Whether you rely on the modern FILTER function or legacy techniques, the principles remain the same: build clear criteria, keep ranges aligned, and plan for growth. Continue experimenting by combining OR with AND, nesting SORTBY, or feeding results into PivotTables. With these skills, you are well-equipped to tackle complex data challenges and elevate your Excel proficiency to the next level.