How to Filter This Or That in Excel

Why This Task Matters in Excel

Every analyst, accountant, project manager, or business owner eventually reaches the point where a simple one-condition filter no longer answers the question at hand. Imagine a customer list where you need everyone located in New York or California, a sales ledger where you want orders from March or any order over $10 000, or a support ticket queue where you must see tickets tagged High priority or opened in the last seven days. These are classic “this OR that” scenarios: you don’t want records that satisfy both criteria (although that is fine), you want records that satisfy either criterion.

In practical business contexts, the “this or that” requirement pops up everywhere:

• Marketing teams producing mailing lists filtered by (industry equals “Healthcare” or annual-revenue exceeds $5 million).
• Finance departments reviewing expense claims where (expense-type is “Travel” or approver equals “CFO”).
• HR pulling employees with (job-family equals “Engineering” or tenure below two years) for onboarding surveys.
• Operations teams monitoring shipments that are (status equals “Delayed” or destination region equals “EU”).

Excel excels (no pun intended) at these tasks because it offers both point-and-click options (AutoFilter, Advanced Filter) and formula-based solutions (FILTER, IF, OR, plus Power Query for large data). Mastering “this or that” filtering keeps your data exploration agile: you answer questions instantly without exporting to another tool. Without this skill, users often resort to manual hide/unhide steps, maintenance-heavy helper columns, or copy-paste gymnastics—costly in time and highly error-prone. Moreover, understanding logical OR filtering strengthens related Excel skills: conditional formatting, dynamic array formulas, PivotTable report filters, and dashboard interactivity. In short, knowing how to filter “this or that” is a foundational competence that pays dividends across every spreadsheet you touch.

Best Excel Approach

The most streamlined modern approach is the dynamic array FILTER function paired with the logical OR operator (+). FILTER returns only the rows that meet your criteria; combining two criteria with + creates the required “either/or” logic. It is fast, spill-aware (outputs an array that resizes automatically), and refreshes instantly when the source data changes.

Syntax for OR filtering with FILTER:

=FILTER(Data, (Condition1) + (Condition2))

Inside the Excel engine, TRUE evaluates to 1 and FALSE to 0. Adding two logical arrays asks Excel to keep rows where the sum is at least 1 (meaning at least one condition is TRUE).

Use FILTER when:

• You are on Microsoft 365 or Excel 2021+ (dynamic arrays are available).
• Your data is in a normal table layout (rows = records, columns = fields).
• You want results to update automatically without refreshing menus.

Alternatives are still important for compatibility or special requirements:

'Advanced Filter (UI): no formula, supports OR logic by placing criteria on separate rows.
'AutoFilter (UI quick filter): use multiple selections within a field or "Custom Filter".
'Helper Column + Table Filter: build OR logic in a new column with =OR(), then filter TRUE.
'Power Query: build OR logic in the query editor; excellent for very large datasets.

Parameters and Inputs

When you use FILTER for “this or that”:

Data — the range or structured table you want to filter, e.g., [A2:E5000] or TableSales.
Condition1 — logical test returning TRUE/FALSE for each row, e.g., (TableSales[State]=\"NY\").
Condition2 — second logical test, e.g., (TableSales[State]=\"CA\").

Optional parameter:
[If_Empty] — value returned when no rows meet either condition, e.g., \"No match\". If omitted, Excel returns #CALC!.

Data preparation:

• Ensure each field has consistent data types (numbers stored as numbers, dates as dates).
• Remove blank header rows; FILTER requires contiguous data.
• If the data source is a Table, conditions can use structured references—clearer and safer than cell coordinates.

Edge cases:

• Mixed text case—use functions like UPPER() around both sides to make comparisons case-insensitive.
• Hidden characters—trim spaces with TRIM or CLEAN in helper columns before filtering.
• Blank cells—decide whether they should count as TRUE for any criterion; handle with ISBLANK or LEN.

Step-by-Step Examples

Example 1: Basic Scenario

Suppose you have a small customer list in [A1:D15] with columns: ID, Name, State, and Sales. You need all customers located in “NY” or in “CA”.

1. Prepare the data: Put headers in row 1 and convert to an Excel Table named TblCust (Ctrl + T).
2. In a blank sheet cell A1 enter:

=FILTER(TblCust, (TblCust[State]="NY") + (TblCust[State]="CA"))

3. Press Enter. Excel spills the records into as many rows and columns as needed starting at A1.
4. Confirm results: Only rows where State is NY or CA appear, preserving original order.
5. Testing: Change a customer’s State to “FL”—the row disappears from the filtered list instantly.

Why this works: (TblCust[State]=\"NY\") returns an array of TRUE/FALSE for each row; so does the CA condition. Adding them produces 1 when either is TRUE. FILTER keeps rows where the summed logical value is non-zero.

Common variation: You might want to retrieve only Name and Sales columns. Inside FILTER, wrap Data with CHOOSECOLS:

=FILTER(CHOOSECOLS(TblCust,2,4), (TblCust[State]="NY") + (TblCust[State]="CA"))

Troubleshooting: If nothing returns, verify spelling (“ny” ≠ “NY”), extra spaces, or that you did not use the asterisk (*) which would create AND logic instead.

Example 2: Real-World Application

Scenario: A retail company tracks orders in a table named TblOrders with fields OrderID, OrderDate, Region, and Total. Management wants to review orders placed in the current month or any order exceeding $10 000 regardless of date.

Step-by-step:

1. Ensure OrderDate is a true date. Use short-date format to confirm.
2. Reference today’s date with the TODAY() function. Define a helper named FirstOfMonth:

=EOMONTH(TODAY(),-1)+1

3. In a report sheet cell A2 enter:

=FILTER(
    TblOrders,
    (MONTH(TblOrders[OrderDate])=MONTH(TODAY())) * (YEAR(TblOrders[OrderDate])=YEAR(TODAY()))
    + (TblOrders[Total]>10000)
)

4. Explain the logic: The first parenthesized expression checks “order occurs this month” by matching month and year. Multiplication (*) inside that expression is AND, ensuring both month and year conditions. The + adds the high-value order test, delivering OR overall.

5. Result: The spill range shows all qualifying records. Managers can quickly scan big deals and recent orders in one consolidated view.

6. Integration: Add a slicer connected to TblOrders for Region; the FILTER result will update live, providing an interactive dashboard.

Performance considerations: On large datasets (50 000+ rows), volatile TODAY() recalculates often. Mitigate by replacing TODAY() with a static date entered in a control cell like [H1] and referencing [H1] in the formula.

Example 3: Advanced Technique

Edge case: Multi-field OR across different data types with exclusion of cancelled records.

Data: Table TblTickets with columns TicketID (text), Opened (date), Priority (text), Status (text), SLA_Hours (number). Requirement: List tickets that are (Priority = \"High\" or SLA_Hours greater than 48) but Status must not be \"Closed\".

The added complexity is the NOT condition for Status and the mixture of text and numeric comparisons.

1. Create the formula:

=FILTER(
    TblTickets,
    (
        (TblTickets[Priority]="High") +
        (TblTickets[SLA_Hours]>48)
    )
    *
    (TblTickets[Status]<>"Closed")
)

Explanation:

• Parentheses around the OR block yield an array of 1s and 0s.
• The NOT condition uses <> “Closed”, producing TRUE where status is not Closed.
• Multiplying the two arrays applies an AND between overall OR result and NOT Closed requirement, ensuring only active tickets appear.

Advanced tip: If you also want to sort by SLA_Hours descending, wrap the formula with SORT:

=SORT(
    FILTER(
        TblTickets,
        ((TblTickets[Priority]="High") + (TblTickets[SLA_Hours]>48)) * (TblTickets[Status]<>"Closed")
    ),
    5, -1
)

Column 5 corresponds here to SLA_Hours. The -1 option sorts descending.

Error handling: Specify an empty message:

=FILTER(..., ((...)+(...))*(...), "No high-risk open tickets")

Memory optimization: Instead of referencing entire columns (TblTickets[SLA_Hours]) if your table is huge, restrict to used rows by Excel Table design or dynamic ranges; this keeps calculation efficient.

Tips and Best Practices

1. Turn your data into Excel Tables before filtering. Structured references make formulas readable, and tables auto-expand as you add rows.
2. Use parentheses generously to control precedence: multiplication is AND, addition is OR. Failing to group expressions leads to unexpected results.
3. Cache volatile functions such as TODAY() or RAND() in a separate cell and refer to that cell from all formulas to reduce recalc cost.
4. Combine FILTER with CHOOSECOLS or SELECTCOLUMNS (Power Query) to output only necessary fields, keeping downstream sheets tidy.
5. Give spill ranges clear labels and consider defining them as named ranges with `=LET(`) constructs for reuse in charts, validation lists, and dashboards.
6. Test edge cases like blank cells, mixed case, or hidden chars early using COUNTBLANK, LEN, and CLEAN. Fixing data quality first prevents mis-filters.

Common Mistakes to Avoid

1. Forgetting parentheses around OR conditions. Example: (A=\"x\")+B=\"y\" returns wrong results because + has lower precedence than =. Always wrap each logical test.
2. Mixing AND (*) and OR (+) without parentheses leads to unintended truth tables. Map out your logic or write helper sentences before coding.
3. Using whole-column references (A:A) on workbooks with hundreds of thousands of rows when only [A2:A2000] is populated. This bloats calculation time.
4. Assuming AutoFilter multi-selection applies OR across different columns—it does not. Choosing “NY” and “CA” in the same column is okay, but filtering “NY” in State and “Big” in Size simultaneously is AND logic.
5. Forgetting compatibility: FILTER is unavailable in Excel 2016 or earlier. Always check version before distributing a workbook; otherwise users will see #NAME? errors.

Alternative Methods

When to choose:

• Use FILTER when everyone is on Microsoft 365 and you want instant sheet-level results.
• Use Advanced Filter for legacy automation tasks controlled by VBA.
• Use Power Query for millions of rows or when combining data from multiple sources. Transitioning is simple: design your query with OR conditions, load to a Table, and replace earlier FILTER formulas with the query output reference.

FAQ

When should I use this approach?

Use dynamic array FILTER when you need results to update immediately as data changes, want a formula-only solution, and your audience runs Excel 365 or 2021. It’s perfect for dashboards, interactive models, and ad-hoc analysis.

Can this work across multiple sheets?

Yes. Point the Data argument to a range on another sheet such as Sheet1!A1:D1000 or to a Table residing elsewhere. All conditions can reference cross-sheet ranges as long as they align row-by-row with the Data array.

What are the limitations?

FILTER cannot spill into merged cells or a range already populated. It also does not support ORs exceeding the maximum formula length easily—though LET helps. Pre-2019 Excel versions lack FILTER entirely; use the alternatives above.

How do I handle errors?

Add the optional [If_Empty] argument: =FILTER(Data,logic,"No match"). To trap other errors like #VALUE! from mismatched dimensions, wrap the entire formula with IFERROR: =IFERROR(FILTER(...),"Error: check ranges").

Does this work in older Excel versions?

Not with FILTER. Use Advanced Filter, AutoFilter, or helper columns. Alternatively, create the filter logic in Power Query, then load the result back even in Excel 2010 (with the free Power Query add-in).

What about performance with large datasets?

FILTER is efficient up to tens of thousands of rows, but calculation time increases with volatile components and whole-column references. For hundreds of thousands of rows, offload to Power Query or a database, then pull a summarized set into Excel.

Conclusion

Mastering “this or that” filtering equips you to answer nuanced business questions in seconds rather than minutes. Whether you use the cutting-edge FILTER function, a time-tested Advanced Filter, or industrial-strength Power Query, the underlying logic is the same: build clear OR criteria and let Excel do the heavy lifting. Combine these techniques with solid data preparation practices and you will create fast, reliable analysis tools that scale with your organization’s needs. Continue exploring LET, LAMBDA, and dynamic array functions to push your filtering skills even further, and never again resort to manual row-hunting for your next urgent data request.