Home
How to Filter Excel With Multiple Criteria Using Formulas and Tools
Filtering data is one of the most common tasks in Excel, yet it quickly becomes complicated when a single dropdown arrow is not enough. Whether you need to find sales from a specific region that exceeded a certain amount, or identify customers who purchased in either January or February, mastering multiple criteria filtering is essential for efficient data analysis.
For a quick answer, you can filter Excel with multiple criteria using two primary methods:
- The FILTER Function: Use
=FILTER(array, (condition1)*(condition2))for AND logic, and=FILTER(array, (condition1)+(condition2))for OR logic. - The Advanced Filter Tool: Go to Data > Advanced, where placing criteria in the same row creates an AND relationship, and different rows create an OR relationship.
This article explores these methods in depth, providing real-world scenarios and technical breakdowns to help you choose the right approach for your specific dataset.
The Modern Solution: The FILTER Function
The FILTER function, introduced in Excel 365 and Excel 2021, changed the landscape of data manipulation. Unlike traditional methods, it is a dynamic array function. This means that as your source data changes, the filtered results update automatically without any manual clicks.
How Boolean Logic Powers the FILTER Function
The power of the FILTER function lies in its "include" argument, which uses boolean logic. In Excel, TRUE is treated as 1 and FALSE is treated as 0. When you apply multiple conditions, you are essentially performing math on these ones and zeros.
AND Logic (The Multiplication Operator)
When you want to return rows where all conditions are met, you multiply the conditions together using the asterisk (*).
- Formula Logic:
(Condition A) * (Condition B) - The Result: If Condition A is TRUE (1) and Condition B is TRUE (1), the result is 1 (TRUE). If either is FALSE (0), the product is 0 (FALSE).
OR Logic (The Addition Operator)
When you want to return rows where at least one condition is met, you add the conditions together using the plus sign (+).
- Formula Logic:
(Condition A) + (Condition B) - The Result: If either condition is TRUE (1), the sum is 1 or greater, which Excel interprets as TRUE. Only if both are FALSE (0 + 0) does it return 0 (FALSE).
Practical Scenario: Filtering a Sales Report
Imagine a dataset in range A2:D100 with columns: Date, Salesperson, Region, and Amount.
To find all sales made by "Sarah" in the "North" region, use the following formula:
=FILTER(A2:D100, (B2:B100="Sarah") * (C2:C100="North"), "No results found")
To find sales that were either in the "North" region or exceeded $5,000, use:
=FILTER(A2:D100, (C2:C100="North") + (D2:D100>5000), "No results found")
Pro Tip: Always wrap individual conditions in parentheses. Failing to do so is the most common reason for formula errors in multi-criteria filtering.
Using Advanced Filter for Complex Static Lists
While the FILTER function is dynamic, the Advanced Filter tool remains a staple for analysts working with older versions of Excel or those who prefer a dedicated criteria range on their worksheet. It is particularly useful for extracting unique records or handling complex "nested" logic without writing long formulas.
Setting Up the Criteria Range
The secret to Advanced Filter is the "Criteria Range." You must copy the exact headers of your data table to a separate area (e.g., above your data or on another sheet).
- AND Conditions: Type your criteria in the same row under the headers. For example, typing "East" under Region and ">1000" under Amount in Row 2 tells Excel to find rows that meet both.
- OR Conditions: Type your criteria in different rows. If you put "East" in Row 2 and "West" in Row 3, Excel returns all records from both regions.
Steps to Apply Advanced Filter
- Click any cell within your data set.
- Navigate to the Data tab and select Advanced in the Sort & Filter group.
- Choose "Filter the list, in-place" to hide rows or "Copy to another location" to create a separate list.
- Select your List range (your data).
- Select your Criteria range (the headers and the rows of conditions you just typed).
- Click OK.
One major drawback of this method is that it is static. If you add new data to your source table, you must run the Advanced Filter again to update the results. In our testing, this often leads to "stale data" in reports, which is why we generally recommend the FILTER function for active projects.
When to Use the Standard AutoFilter
Don't overlook the standard Ctrl + Shift + L AutoFilter. While it seems basic, it has built-in logic for multiple criteria within a single column.
Multiple Selection in a Single Column
When you click the filter arrow on a column, you can check multiple boxes. This is technically an OR filter (e.g., show "Apples" OR "Oranges").
Custom AutoFilter for Ranges
If you need to filter a numeric column for a range (e.g., Amount between $500 and $1,000), you can use the Number Filters > Between option. This is an AND filter applied to a single column.
However, the standard AutoFilter struggles with OR logic across different columns. For example, you cannot easily use AutoFilter to show "All Sales in the East" OR "All Sales by John (regardless of region)." For that, you must move to the FILTER function or Advanced Filter.
Why Does the FILTER Function Return a #CALC! Error?
The #CALC! error is the most common hurdle when filtering with multiple criteria. It typically occurs when the function finds zero records that match your conditions.
To handle this gracefully, always use the third argument of the FILTER function: [if_empty].
Instead of =FILTER(A2:B10, A2:A10="NonExistent"), which returns #CALC!, use:
=FILTER(A2:B10, A2:A10="NonExistent", "No Matches Found")
This keeps your dashboards clean and prevents errors from cascading through other dependent formulas.
How to Filter Based on Partial Text Matches
Often, your criteria aren't exact. You might need to find all products that contain the word "Pro" and are in the "Electronics" category. Combining FILTER with the SEARCH or FIND functions allows for wildcard-like behavior.
The ISNUMBER/SEARCH Pattern
To filter a column for partial text, use this structure:
=FILTER(A2:D100, (ISNUMBER(SEARCH("Pro", A2:A100))) * (C2:C100="Electronics"))
- SEARCH("Pro", A2:A100): Returns the position of the text if found, or an error if not.
- ISNUMBER(...): Converts those positions to
TRUEand errors toFALSE. - The Result: A clean array of TRUE/FALSE values that the
FILTERfunction can process.
Advanced Scenario: Filtering Between Two Dates
Filtering by date ranges is a frequent requirement in financial reporting. To filter for orders between January 1, 2024, and March 31, 2024, you treat dates as numbers.
=FILTER(A2:D100, (A2:A100 >= DATE(2024,1,1)) * (A2:A100 <= DATE(2024,3,31)))
Note that using the DATE(year, month, day) function is safer than typing date strings like "1/1/2024," as it avoids issues with different regional date formats (DMY vs. MDY).
Power Query: The Professional Choice for Large Datasets
When your dataset exceeds 100,000 rows, or when you need to combine and filter data from multiple workbooks, the FILTER function might slow down your spreadsheet. This is where Power Query shines.
Creating a Multi-Criteria Filter in Power Query
- Select your data and go to Data > From Table/Range.
- In the Power Query Editor, click the dropdown arrow on a column.
- Use Text Filters or Number Filters to set your first condition.
- To add more criteria, you can either filter another column (creating an AND relationship) or use the "Advanced" option in the filter dialog to add "OR" clauses.
- Click Close & Load.
Power Query is superior for repeatable workflows. If you receive a new data export every Monday, you simply replace the source file and click "Refresh." The complex multi-criteria logic is saved as a series of steps, ensuring consistency that manual filtering cannot match.
Comparison of Filtering Methods
| Feature | AutoFilter | Advanced Filter | FILTER Function | Power Query |
|---|---|---|---|---|
| Updates Dynamically? | No | No | Yes | Yes (on refresh) |
| Logic Complexity | Low | High | High | Very High |
| Ease of Setup | Easiest | Medium | Medium | Hard |
| Data Size Handling | Moderate | Moderate | Moderate | Excellent |
| AND/OR Support | Limited | Full | Full | Full |
Best Practices for Data Organization Before Filtering
Even the best formulas will fail if your data is poorly structured. Before applying multiple criteria, ensure your data follows these rules:
- Consistent Headers: Headers should be in a single row. Avoid merged cells in the header or the data range.
- No Blank Rows: Formulas like
FILTERrely on contiguous ranges. Blank rows can cause the function to stop prematurely or return unexpected zeros. - Use Excel Tables: Format your data as a Table (
Ctrl + T). This allows you to use structured references (e.g.,Table1[Amount]) instead of cell ranges (e.g.,D2:D100). Structured references expand automatically as you add new rows.
Troubleshooting Common Issues
1. Mixed Data Types
If you are filtering for "100" but the column contains the number 100 stored as text, the filter will return nothing. Use the VALUE() function or the "Text to Columns" tool to standardize your data types before filtering.
2. Case Sensitivity
By default, the FILTER function is not case-sensitive. "NORTH" and "north" are treated the same. If you need a case-sensitive filter, you must incorporate the EXACT function:
=FILTER(A2:D100, EXACT(C2:C100, "North"))
3. Merged Cells
Merged cells are the enemy of Excel data tools. If your criteria range or source data contains merged cells, the Advanced Filter tool will often fail to recognize the correct columns. Always "Unmerge" and use "Center Across Selection" for formatting instead.
Summary of Key Techniques
Filtering with multiple criteria in Excel moves you from basic data entry to meaningful analysis. The FILTER function is the best choice for modern, responsive reports due to its dynamic nature and boolean logic. For static extractions or complex logic across many rows, the Advanced Filter tool remains a powerful alternative. For high-volume data processing, Power Query provides the most robust and scalable solution.
By understanding the difference between AND (*) and OR (+) logic, you can construct precise queries that isolate the exact information you need, saving hours of manual searching.
Frequently Asked Questions
What is the shortcut for filtering in Excel?
The primary shortcut to toggle AutoFilter on and off is Ctrl + Shift + L. To open the Advanced Filter dialog, you can use the sequential shortcut Alt > A > Q.
Can I use the FILTER function with multiple OR conditions?
Yes. Simply continue adding conditions with the + operator. For example: (Condition1) + (Condition2) + (Condition3) will return rows that meet any of the three criteria.
How do I filter for multiple values in one column?
If you are using the FILTER function, you can use the MATCH function or a series of + conditions. For example, to find "Red," "Blue," or "Green":
=FILTER(A2:B10, (A2:A10="Red") + (A2:A10="Blue") + (A2:A10="Green"))
Does the FILTER function work in Excel 2019?
No, the FILTER function is only available in Excel 365, Excel 2021, and newer versions. Users on Excel 2019 or earlier should use the Advanced Filter tool or Power Query.
Why is my FILTER function returning a #VALUE! error?
This usually happens when the "include" array does not have the same dimensions as the source "array." Ensure that if your data is A2:D100, your criteria range is also 100 rows long (e.g., B2:B100).
-
Topic: Filter by using advanced criteria | Microsoft Supporthttps://support.microsoft.com/en-us/excel/filter-by-using-advanced-criteria
-
Topic: What Most People Miss About Filtering in Excel with Multiple Criteriahttps://office.alibaba.com/officesoftware/how-to-filter-in-excel-with-multiple-criteria
-
Topic: Excel FILTER function | Exceljethttps://exceljet.net/functions/filter-function