How to Use Advanced Filter with Multiple Criteria in Excel

Filtering data efficiently is key to analyzing large datasets in Excel. When simple filters aren’t enough, especially if you want to apply complex logic across multiple criteria, the Advanced Filter tool is your best option. It lets you extract records that meet multiple conditions, including combinations of AND and OR logic, all without needing complex formulas.

In this article, you’ll learn how to use Excel’s Advanced Filter feature with multiple criteria by setting up proper criteria ranges, applying AND/OR logic, using custom formulas, wildcards and even VBA for automation.

Excel’s Advanced Filter is a versatile tool that lets you extract data based on custom conditions without writing formulas. It works by setting up a criteria range that controls what gets filtered. Whether you want to match all conditions (AND), any condition (OR), or a combination of both, it all depends on how you structure the criteria rows and columns. Let’s get started.

AND Logic with Advanced Filter

Use this method when you want to extract records that meet all specified conditions. AND logic starts working when all criteria are written on the same row in the criteria range.

In this example, our sample dataset tracks product sales by region and date. We’ll extract entire rows that match specific criteria like Region = “East” and Sales > 150 using formulas that return all matching records, not just one value.

Steps:

➤ Add two new columns for the criteria headers: Region and Sales in cells F1 and G1.
➤ In F2, type East. In G2, type >150. Since both values are on the same row, Excel treats this as Region = East AND Sales > 150.

➤ Go to the Data tab and click Advanced in the Sort & Filter group.

➤ In the dialog, select Copy to another location. Use A1:D11 as your List range.
➤ Set Criteria range to F1:G2, and choose an empty cell like F4 as the Copy to range.

➤ Click OK.

Now Excel will copy only the records where both Region is East and Sales are over 150.

OR Logic with Advanced Filter

Use OR logic when you want to extract records that match any of your conditions. This happens when you place different criteria on separate rows under the same headers.

This method outputs all rows where either Region is “East” or Sales is greater than 150. Excel reads each criteria row as an alternative condition and returns every row that satisfies at least one of them.

Steps:

➤ In F2, type East. In G3, type >150. This time the Region and Sales values are on separate rows, so Excel reads it as Region = East OR Sales > 150.

➤ Repeat the same steps to run the Advanced Filter, using F1:G3 as the Criteria range this time and select F5 as your Copy to cell.

Excel will return all rows where either Region is East or Sales are greater than 150.

Combining AND with OR Logic in Advanced Filter

You can combine both logics by placing multiple AND conditions on separate rows. Excel treats each row as a separate AND group, then joins them with OR logic across rows.

This method returns every row that satisfies at least one full set of AND conditions. For example, it will include rows where the Region is “East“, Sales are less than 300, and the Date is 1-Sep-24, as well as rows where the Region is “West“, Sales are under 200, and the Date is 2-Sep-24. Excel treats each row in the criteria range as a separate condition group and returns all matching records with their complete row data intact.

Steps:

➤ Type Region, Sales and Date as criteria headers ranging from F1:H1.
➤ Add East in F2, <300 in G2, and 1-Sep-24 in H2 which defines your first AND condition.
➤ Insert West in F3, <200 in G3, and 2-Sep-24 in H3. This defines your second AND condition.
➤ Since both conditions are in separate rows, they are now binded by the OR logic. Now Excel reads this criteria range as:

(Region = “East” AND Sales < 300 AND Date = 1-Sep-24) OR (Region = “West” AND Sales < 200 AND Date = 2-Sep-24)

➤ Go to the Advanced filter and repeat the same steps. Just set the criteria range as F1:H3.

Now your filtered data is displayed at your desired location.

If you’re using Advanced Filter in Excel with multiple criteria and want to filter partial text matches like product names that start with such as “Ba”, wildcards can make filtering fast and flexible.

Here, Excel returns all rows where the Product name starts with “Ba” or “Dr”. In our dataset, this would include entries for Banana and Dragon. Each row appears in full, with Product, Region, Sales, and Date intact.

Steps:

➤ In an empty area (e.g., starting at F1), enter the exact column header you want to filter on. For example, type Product in F1.
➤ Below that (F2), type your wildcard condition. For instance, to match any product that starts with “Ba”, type:

Ba*

➤ To combine multiple OR criteria (e.g., “Ba*” or “Dr*”), list each condition on its own row under the same header.

➤ Go to the Data tab and click Advanced under the Sort & Filter group.
➤ Select your List range, e.g., $A$1:$D$11.

➤ Set the Criteria range to your wildcard conditions, e.g., $F$1:$F$3.
➤ Choose to filter in place or copy results elsewhere such as F5 cell, then click OK.

Now your filtered data will be displayed starting from your output location which is Banana OR Dragon. This works great alongside advanced filter in Excel for multiple criteria by listing each wildcard rule in a new row.

Sometimes you need to filter data with complex conditions like multiple AND criteria. Using a custom formula in the criteria range lets you create flexible and effective filters beyond simple equals. Based on set conditions, the output includes only rows where Region is “East” and Sales is above 150. The Advanced Filter tool copies those full rows to the destination.

Steps:

➤ Choose a blank area for your criteria range and enter any header (e.g., “Filter”) in the first cell (say, F1).
➤ In the cell below (F2), enter your custom formula starting with an equals sign. For example, to filter Region = “East” AND Sales > 150, use:

➤ Open the Advanced Filter dialog from the Data tab >> Sort & Filter group >> Advanced.

➤ Select your full data range including headers, e.g., $A$1:$D$11 as your List range.
➤ Set the criteria range to the header and formula cells, e.g., $F$1:$F$2.
➤ Choose to filter in place or copy the filtered data elsewhere such as F5 cell from where your output will begin, then click OK.

The filtered results will display only the rows that meet all your custom formula criteria, giving you exact control over your data selection.

When you want to automate filtering or avoid complex formulas, VBA lets you run Advanced Filter quickly with customized criteria and copy results automatically. Once you hit F5 key to run the macro, Excel copies only those rows that match your defined conditions into a specified output range such as when Region is set to East and Sales exceeds 150. It returns full rows based on multiple criteria without needing manual filtering steps.

Steps:

➤ Open the Visual Basic for Applications editor by pressing  Alt  +  F11 .
➤ Insert a new module via Insert tab >> Module.
➤ Paste the following VBA code into the module:

Sub FilterWithMultipleCriteria()
    Sheets("Sheet1").Range("A1:D11").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("Sheet1").Range("F1:G3"), _
        CopyToRange:=Sheets("Sheet1").Range("F5"), _
        Unique:=False
End Sub

➤ Customize the ranges in the code (A1:D11 for data, F1:G3 for criteria, and  F5  for output) to match your worksheet layout.

➤ Press  F5  key to run the macro to apply the filter and copy filtered results to the specified location.

Frequently Asked Questions

Can I use Advanced Filter with more than two criteria in Excel?

Yes, you can use as many criteria as you want. Just add more column headers to the criteria range and fill the rows according to your AND/OR logic structure.

What’s the difference between Advanced Filter and AutoFilter in Excel?

AutoFilter is for basic filtering using dropdowns, while Advanced Filter lets you apply complex criteria, copy results to a new range, use formulas, and even automate with VBA if needed.

How do I use wildcards like * or ? in Advanced Filter?

Type the wildcard pattern directly in the criteria cell. Use * mark for any characters and ? mark for single characters. Place each wildcard rule on a separate row for OR logic.

Why is my Advanced Filter not returning results?

This usually happens when your criteria formula references the wrong row or uses incorrect syntax. Ensure your criteria row references match the first data row and check your spelling or wildcards.

Wrapping Up

In this tutorial, we learned how to use Excel’s Advanced Filter with multiple criteria for efficient data filtering. From basic AND/OR setups to advanced methods using wildcards, formulas, and even VBA, Advanced Filter gives you unmatched control over what data you keep or extract. Feel free to download the practice file and share your feedback.