FILTER Function vs Advanced Filter in Excel

The FILTER function vs Advanced Filter choice comes down to one question: do you want a live result or a one-time copy? FILTER is a formula that returns matching rows and updates the moment the data changes, in Excel 2021 or Microsoft 365. Advanced Filter is an older menu tool that reads its conditions from cells on the sheet and copies the matches once, in any version. This guide compares them on the same data so you can pick the right one for a report, an old workbook or a job test.

FILTER function vs Advanced Filter at a glance#

Both pull out the rows that meet your conditions. The difference is how you set the conditions and whether the result stays up to date.

How each one works#

FILTER takes the data, a TRUE/FALSE test for each row, and an optional message for when nothing matches. The result spills: it fills as many cells as it needs below and to the right of the formula. The FILTER function guide covers each argument.

Advanced Filter needs a criteria range: a small block of cells with column headers copied exactly from your data, and conditions typed underneath. Excel reads the block like this:

• Conditions on the same row must all be true (AND).
• Conditions on different rows are alternatives (OR).
• An empty cell under a header means "anything".

You then choose Data > Sort & Filter > Advanced (Mac: Data > Advanced), tell Excel where the list and the criteria are, and choose whether to filter in place or copy the results elsewhere.

The same task, solved both ways#

Here is a list of orders in A1:D8:

The task: list every order that is North with an amount over 500, or any order in the South. That mix of AND and OR is where AutoFilter's buttons struggle and both of these tools shine.

With the FILTER function#

Click F2 and type:

=FILTER(A2:D8,(B2:B8="North")*(D2:D8>500)+(B2:B8="South"))

Multiplying the two North tests means both must be true. Adding the South test means either side can be true. The result spills into F2:I5:

Change Dev's amount in D5 to 900 and order 1004 appears straight away.

With Advanced Filter#

First, build the criteria range in F1:G3:

Row 2 says "North AND over 500". Row 3 says "South, any amount". Because they are on different rows, a record that matches either is included. Then:

1. Click any cell in the data.
2. Choose Data > Sort & Filter > Advanced.
3. Choose Copy to another location.
4. Check that List range is $A$1:$D$8, set Criteria range to $F$1:$G$3 and Copy to to $I$1.
5. Click OK.

The same four orders appear from I1, with headers. But if Dev's amount changes to 900, the result stays the same until you run Advanced Filter again.

When to use Advanced Filter#

Advanced Filter is still the right tool in a few situations:

• Older Excel. In Excel 2019 or earlier, FILTER shows #NAME?. Advanced Filter works everywhere.
• A one-time extract. If you need a fixed list to send or archive, a static copy is what you want.
• Unique values in older versions. Select one column, choose Copy to another location and tick Unique records only.
• Conditions other people can edit. The criteria range sits on the sheet, so a colleague can change "North" to "West" without touching a formula, then re-run the filter.
• Filtering in place with complex logic. Choose Filter the list, in-place to hide non-matching rows using AND/OR rules the filter buttons can't express.

When to use the FILTER function#

FILTER is the better choice for most new work in Excel 2021 or Microsoft 365:

• Reports and dashboards that must stay current as data is added.
• Results that feed other formulas. =SUM(FILTER(D2:D8,B2:B8="North")) adds the North amounts: 2,350. The guide to filtering and summing with one formula shows more.
• Conditions driven by input cells, such as a region in G1 or dates between two cells. See filtering by date for date ranges.
• Sorted or unique results in one step, by wrapping FILTER in SORT or UNIQUE.
• Returning many matches. A lookup returns one row; FILTER returns all of them. The comparison of XLOOKUP vs FILTER explains when each fits.

If you only need to look at a subset quickly, the filter buttons may be enough. The comparison of the FILTER function vs AutoFilter covers that choice, and the filter a table lesson practises the buttons.

Common mistakes with FILTER and Advanced Filter#

These are the slips that most often give the wrong rows:

1. Criteria headers that don't match. In Advanced Filter, "Amount " with a trailing space is not "Amount". Copy the headers from the data instead of typing them.
2. Expecting an exact text match. In a criteria range, North matches anything that starts with North, including "Northeast". To match exactly, type ="=North" in the criteria cell.
3. A blank row in the criteria range. An empty row means "anything", so every record passes. Make the criteria range stop at the last condition row.
4. Copying to another sheet from the wrong place. Advanced Filter can only copy to the active sheet. Start it from the sheet where the results should go.
5. Forgetting to re-run Advanced Filter after the data changes. The old result looks fine but is out of date.
6. FILTER returning #CALC! when nothing matches. Add a third argument: =FILTER(A2:D8,B2:B8="North","No orders").
7. FILTER returning #SPILL! because cells below the formula aren't empty. Clear them.

Advanced Filter can also use formula criteria: leave the criteria header blank (or use a name that isn't a column header) and type a test that refers to the first data row, such as =D2>AVERAGE($D$2:$D$8). That returns the three orders above the average of 854.29. The FILTER version is simpler: =FILTER(A2:D8,D2:D8>AVERAGE(D2:D8)). Questions like this come up in the advanced Excel interview questions, so it's worth knowing both.

Our recommendation#

If you and everyone who opens the file have Excel 2021 or Microsoft 365, use the FILTER function. It updates by itself, the logic is visible in one formula, and it combines with SORT, UNIQUE and SUM.

Keep Advanced Filter for older versions, for one-time extracts you want frozen, and for quick unique lists or in-place filtering with OR logic across columns. Learn its criteria-range rules too, because older workbooks and some job tests still use it.

In short: FILTER for anything that should stay live, Advanced Filter for a snapshot.

Practise it#

Try the exercise on this page: it combines AND and OR in one FILTER formula, the same logic as the criteria range above. Then practise the FILTER function in a live spreadsheet: you type each formula and it checks it instantly. The filter a table lesson covers the filter buttons and SUBTOTAL, and the data analyst Excel test puts both under time pressure.