FILTER function vs Advanced Filter in Excel

Three ways to filter, side by side

AutoFilter is the drop-down arrows you get with Data > Filter or Ctrl+Shift+L. It hides rows in place, and conditions in different columns combine with AND. It's the fastest way to have a look, but it changes what everyone sees on the sheet.

Advanced Filter uses a small criteria range that you type on the sheet, so it can combine AND and OR in ways AutoFilter can't, and it can copy the result elsewhere. The FILTER function does the same job with a formula, and the result is live. All three work on this order list, and each suits a different job.

The FILTER function: live results

FILTER(array, include, [if_empty]) returns every row where include is TRUE. Multiply tests for AND, add them for OR. Here the question is "East orders, or any order over 1,500", which is an OR across two different columns.

Add a new East order to the data and the result grows by itself. The result can sit on another sheet, feed other formulas like SUM or SORT, and the criteria can come from cells someone types into.

Advanced Filter: a criteria range and a one-off result

Put a copy of the headers you need above or beside the data, with at least one blank row between them and the list. Under them, type the conditions. Conditions on the same row are AND; conditions on separate rows are OR. For the same question as above, type Region and Amount as headers in G1:H1, ="=East" in G2 (the odd form is explained below), and >1500 in H3.

Copy to another location only copies to the sheet you're on. To send results to another sheet, click on that sheet first, then start Data > Advanced and point the list range back at the data.

1. Click a cell inside the data.
2. Go to Data > Advanced (in the Sort & Filter group).
3. Check the List range covers the data with its headers, and set the Criteria range to the criteria headers and rows, here G1:H3.
4. Choose Filter the list, in-place to hide non-matching rows, or Copy to another location and pick a single cell for the top-left of the output.
5. Click OK. To clear an in-place filter, use Data > Clear.

The Advanced Filter gotcha: text matches "begins with"

In a criteria range, typing East matches every value that starts with East, so Eastgate's order PO-2206 is included too. For an exact match, type the criterion as ="=East" in the cell; it shows =East and matches only East. FILTER doesn't have this trap: B2:B9="East" is exact, and you'd use LEFT or SEARCH if you wanted begins-with or contains.

Two more traps. A completely blank row inside the criteria range means "no condition" and lets every row through. And criteria headers must match the data headers exactly, or nothing matches.

Unique records: the other reason people use Advanced Filter

Tick Unique records only in the Advanced Filter box and Excel keeps the first of each identical row. With Copy to another location and a single column, that's a quick distinct list in any version of Excel, and it leaves the original alone.

In Excel 365 and 2021, UNIQUE does the same with a formula and stays up to date. Wrap it in SORT for an A to Z list.

Which one to pick

Pick the FILTER function when the result is a report people will open again: a list per customer, overdue items, anything driven by a drop-down. It updates by itself and never hides rows from colleagues. It needs empty cells to spill into and can't go inside an Excel table.

Pick Advanced Filter when you're on Excel 2019 or earlier, when you need a one-off extract to send someone, or a quick unique list without formulas. Pick AutoFilter for a quick look at your own data, and remember to clear it before you share the file. To total only the rows an AutoFilter or in-place Advanced Filter leaves visible, use SUBTOTAL(109, range).