Filter Multiple Criteria in Excel: 3 Methods That Work

Most Excel tutorials act like the FILTER function is the only way to filter multiple criteria in Excel. It's not — and if you're on Excel 2019 or earlier, that advice leaves you completely stranded. The right method depends on your version, your data size, and whether you need results that update automatically when your data changes. This guide covers all three approaches so you can pick the one that actually works for your setup.

I've been building operational dashboards at a logistics company since 2019, and I've answered versions of this question hundreds of times on MrExcel forums and the Excel subreddit. The confusion is almost always the same: people learn one method, hit a wall, and assume they're doing something wrong. Usually they're just using the wrong tool for their version.

---

The Three Methods at a Glance: AutoFilter, Advanced Filter, and the FILTER Function

AutoFilter is the dropdown-based approach built into every version of Excel. It's fast, requires no formulas, but it doesn't update automatically — you have to re-apply it when data changes. Advanced Filter works similarly but gives you a structured criteria range for more complex conditions, making it the go-to for legacy users who need something repeatable. The FILTER function (available only in Microsoft 365, Excel 2021, and Excel 2024) returns results dynamically as a spilled array. Change the source data and the output updates immediately, no button-clicking required.

How to Tell Which Version of Excel You're Running

Go to File → Account → About Excel. If you see "Microsoft 365" or a version number of 2021 or later, you have access to the FILTER function. Excel 2019, 2016, or earlier? Stick with AutoFilter or Advanced Filter — both are covered below. If you're brand new to Excel's interface and want a fuller orientation before diving into filtering, the Excel for Beginners starter guide is worth a few minutes of your time first.

---

Step 1: Use AutoFilter to Filter Multiple Columns at Once

AutoFilter is the fastest way to filter multiple criteria in Excel when you don't need a formula. It works in every version, takes about thirty seconds to apply, and for quick one-off analysis it's genuinely hard to beat. The trade-off: results are static. The moment your data changes, you're re-filtering manually.

Adding AND Logic Across Multiple Columns

Select any cell in your data range, then go to Data → Filter. Dropdown arrows appear in each column header. Click the arrow on your first column, apply your condition, and click OK. Then do the same on your second column. Excel treats each column filter as an AND condition — rows must match all active filters to appear. No formula needed to filter multiple columns at once.

Excel only allows one AutoFilter per sheet. If you're working with multiple tables on a single sheet, only one can have an active AutoFilter at a time.

Applying OR Logic Within a Single Column

To filter by multiple values within one column (say, orders from both the Northeast and Southeast regions), open the dropdown and check the boxes next to each value you want. That's OR logic: rows matching any checked value will show. For text-based OR conditions with custom rules (like "begins with A" OR "ends with Z"), use Custom Filter in the dropdown menu.

---

Step 2: Use the FILTER Function to Apply Multiple Criteria Dynamically

Once you've used AutoFilter for quick lookups, you'll eventually hit a situation where you need results that update automatically, or where you want to output filtered data somewhere else on the sheet entirely. That's where the FILTER function earns its place.

Most tutorials present the FILTER function as three tidy arguments and move on. What they skip is the third argument — the if_empty parameter — and that omission will break your workbook in production. An operational dashboard I built ran cleanly for eleven months before throwing a #CALC! error in December: for the first time, the Northeast region had zero qualifying orders, and I'd left if_empty out of every FILTER formula. The fix took ten minutes. The lesson took eleven months.

Writing AND Logic: Multiply Your Conditions Together

The FILTER function takes a single include argument, which initially seems limiting. The workaround is elegant once you see it. Multiply your criteria arrays together to create AND logic:

=FILTER(A2:D100, (B2:B100="Northeast") * (C2:C100>500), "No results")

That formula returns every row where the region is Northeast and the order value exceeds $500. The multiplication works because TRUE and FALSE evaluate as 1 and 0 — multiplying them together returns 1 only when both conditions are true. In a "Regional Warehouse Orders Q4 2024" table with columns for Region, Order Value, and Status, the formula above pulls only Northeast orders over $500. Clean, no nesting required.

The * operator as AND logic is underexplored in most FILTER tutorials. It's not a trick — it's the intended pattern.

Writing OR Logic: Add Your Conditions Together

Swap multiplication for addition and you get OR logic:

=FILTER(A2:D100, (B2:B100="Northeast") + (B2:B100="Southeast"), "No results")

Addition returns a non-zero value when either condition is true. You can combine both operators in the same formula — wrap the OR portion in double negatives (--) when mixing AND and OR to keep the array math clean.

Why Your FILTER Formula Returns #CALC! or #VALUE!

The #CALC! error doesn't mean your formula is wrong — it means no rows matched your criteria and you left out the if_empty argument. Always write it as FILTER(array, criteria, "No results") or at minimum FILTER(array, criteria, ""). Test with zero matches. Always.

A #VALUE! error usually points to a criteria array that doesn't match the size of your data range, often because the range accidentally includes the header row. Start your array at row 2, not row 1. And if your criteria cells are typed manually rather than driven by drop-down lists, a single typo ("Norteast" instead of "Northeast") will silently return zero results with no error at all. Validated drop-downs in criteria cells prevent this entirely.

---

Step 3: Use Advanced Filter with a Criteria Range on Older Excel Versions

If you're on Excel 2019 or earlier, the FILTER function isn't an option. Advanced Filter fills that gap — it's more structured than AutoFilter and handles complex AND/OR combinations through a dedicated criteria range. If you're on a Mac and run into interface differences along the way, the guide on sorting and filtering data in Excel on Mac covers the platform-specific quirks.

Building the Criteria Range Correctly

Set up a separate area of your sheet (above your data or on another sheet) with headers that exactly match your table headers. Case doesn't matter, but spelling does. Conditions on the same row are AND logic; conditions on separate rows are OR logic. Then go to Data → Advanced, set your list range and criteria range, and choose between filtering in place or copying results to another location. The copy-to option is useful — it gives you a static snapshot you can work with without disturbing your original data.

A header in your criteria range that doesn't exactly match the table header returns zero results with no error and no warning. Double-check those headers every time.

If you want a broader look at how sorting and filtering work together before applying multiple criteria, the introduction to sorting and filtering in Excel is a solid place to start.

---

Common Mistakes When Filtering Multiple Criteria in Excel

The silent failure is the worst kind. With Advanced Filter, a mismatched header in your criteria range returns zero results — no error, no warning, just empty output that looks like your data doesn't exist. With the FILTER function, the #CALC! error is actually the honest failure: at least it tells you something went wrong. Omit if_empty and you'll see it the moment a filter genuinely returns no matches. Don't treat it as optional syntax. It's a production requirement.

AutoFilter has its own gotcha: one filter per sheet. If you're applying filters to two separate data ranges on the same worksheet, the second clears the first. The solution is to move one range to its own sheet, or use the FILTER function if your version supports it.

The biggest structural difference between these methods: AutoFilter and Advanced Filter are static — they don't update when your data changes. The FILTER function is dynamic. If you're still on a legacy version and this kind of automation matters to your work, it might be worth the upgrade conversation.

Microsoft Copilot in Excel now lets you describe a filter in plain language ("show me all orders over $500 from the Northeast region") and will attempt to apply it for you. It's useful for quick exploratory analysis, though for production workbooks, building the formula yourself gives you more control and reliability.

---