How to Use Advanced Filter in Excel: A Complete Guide

Excel's Advanced Filter is one of the most powerful — and most overlooked — tools in the spreadsheet toolkit. While the standard AutoFilter lets you filter data with basic dropdown menus, Advanced Filter gives you precise, rule-based control over which rows appear, where results go, and how complex your conditions can be.

Here's exactly how it works, what separates it from basic filtering, and what determines whether it fits your workflow.

What Is the Advanced Filter in Excel?

The Advanced Filter (found under Data → Sort & Filter → Advanced) is a filtering tool that reads your filter conditions from a separate range on your spreadsheet — called a criteria range — rather than from dropdown menus. This makes it possible to:

• Filter with multiple conditions across multiple columns
• Apply OR logic across rows (not just AND logic within a row)
• Copy filtered results to a different location on the sheet
• Extract unique records only, removing duplicates automatically

It's particularly useful when your filtering logic is too complex for AutoFilter to handle cleanly.

How to Set Up the Advanced Filter: Step by Step

1. Organize Your Data as a Proper Table

Your source data should have:

• A header row in the first row (column labels like "Region," "Sales," "Date")
• No blank rows within the data
• No merged cells in the data range

2. Build a Criteria Range 🔍

This is the key step that trips up most users. Your criteria range must:

• Have at least one header that exactly matches a column header in your data
• List filter conditions below the matching header

How AND vs OR logic works:

Example: If you want rows where Region is "North" AND Sales > 5000:

If you want rows where Region is "North" OR Region is "South":

Place the criteria range somewhere clearly separated from your data — a few columns to the right or in a distinct area above the dataset works well.

3. Open the Advanced Filter Dialog

Go to Data → Advanced. A dialog box appears with these options:

• Action: Filter the list in-place, or copy to another location
• List range: Your source data (including headers)
• Criteria range: The criteria table you built in Step 2
• Copy to: (Only if copying results) The destination cell or range
• Unique records only: Checkbox to deduplicate output

4. Run the Filter

Click OK. Excel applies the filter immediately. If you chose filter in-place, rows that don't match are hidden (like AutoFilter). If you chose copy to another location, the matching rows are written to your specified destination — the original data stays untouched.

Using Wildcards and Formulas in Criteria

Advanced Filter supports wildcard characters in text criteria:

• * matches any number of characters (Sm* matches Smith, Smart, Small)
• ? matches a single character (J?n matches Jan, Jon, Jun)

You can also use formula-based criteria for more complex logic. When using a formula:

• The criteria range header must be blank or different from any data column header
• The formula must evaluate to TRUE or FALSE
• It references the first data row (not the header)

For example, to filter rows where the value in column B is above the column average:

This unlocks filtering logic that no dropdown menu can replicate.

Copying Results vs. Filtering In-Place

The "copy to another location" option is one of Advanced Filter's most practical features — and one reason it stays relevant even as Excel gains newer tools.

When you copy results to another location:

• Your original dataset is never modified
• You can run multiple different filters and capture each result set separately
• The output can be placed on the same sheet or a different sheet (with some version-specific limitations on cross-sheet copying)

Filtering in-place behaves like AutoFilter — rows are hidden but not deleted — which works well for quick visual inspection.

Where Advanced Filter Differs From Other Excel Tools

Advanced Filter sits between AutoFilter's simplicity and Power Query's full data transformation capabilities. It's entirely worksheet-native — no add-ins, no data model, no special setup required. 🛠️

Factors That Affect How You'll Use It

How useful Advanced Filter is in practice depends on several things specific to your situation:

Data size and structure — Advanced Filter works well for datasets of a few hundred to several thousand rows. Very large datasets may be better served by Power Query or pivot tables, which are optimized for heavier processing.

How often criteria change — Because your criteria live in a visible range on the sheet, they're easy to update and re-run manually. But if your filter logic changes constantly and you need it refreshed automatically, a formula-based approach or Power Query may serve better.

Excel version — The core Advanced Filter feature has been stable across Excel versions for years. However, cross-sheet copy-to behavior and interaction with newer features like dynamic arrays can vary depending on whether you're using Excel 2016, 2019, Microsoft 365, or Excel for Mac.

Skill level with Excel references — Basic Advanced Filter use requires no formulas. Formula-based criteria, however, require comfort with absolute vs. relative references and logical functions. Where you sit on that spectrum shapes what you can realistically build. 📊

The gap between "I know how Advanced Filter works" and "I know which approach fits my specific dataset and workflow" is almost always about those four variables — and they're yours to assess.