FILTER Function vs AutoFilter in Excel

FILTER function vs AutoFilter is a choice between looking and reporting. AutoFilter hides the rows you don't need inside the original table, so you can look at a subset in seconds, while the FILTER function copies the matching rows into a new, live list that updates by itself. This comparison is for office, admin and analyst job seekers who need to pull "just the East deals" or "just the overdue invoices" quickly and correctly.

FILTER function vs AutoFilter at a glance#

Both show only the rows that pass a test. AutoFilter changes what you see in the table. FILTER builds a new result somewhere else.

The same task, solved both ways#

Here is a small table of deals. The task: show only the East deals.

With AutoFilter#

1. Click any cell in the table.
2. Press Ctrl+Shift+L (or Data > Filter). A small arrow appears in each header.
3. Click the arrow in Region (B1), untick (Select All), tick East and click OK.

Rows 3, 5, 7 and 9 are hidden. The row numbers turn blue and skip the hidden rows, and the Region button shows a funnel. The four East rows stay in their original place.

To show everything again, click the funnel and choose Clear Filter From "Region", or press Ctrl+Shift+L again to remove the filter buttons altogether. If you edit the data while a filter is on, choose Data > Reapply so the filter checks every row again; otherwise a row you just changed to "West" stays visible.

With the FILTER function#

In an empty cell such as E2, type:

=FILTER(A2:C9,B2:B9="East")

• A2:C9 is the array: the rows to return, without the headers.
• B2:B9="East" is the include test, checked row by row.

The four East rows spill into E2:G5: Ava, Noah, Olivia and Sophia, with their regions and amounts. The original table is untouched. If someone changes Liam's region to East, he appears in the list straight away. The FILTER function guide shows more examples.

Adding a second condition#

Now only East deals of 10,000 or more. With AutoFilter, keep the Region filter and add a Number Filters > Greater Than Or Equal To filter on Amount.

With FILTER, put each test in parentheses and multiply them:

=FILTER(A2:C9,(B2:B9="East")*(C2:C9>=10000))

TRUE counts as 1 and FALSE as 0, so a row only passes when both tests are TRUE. The result is Ava (12,500) and Olivia (10,000). For OR, add the tests instead: (B2:B9="West")+(B2:B9="South") keeps rows from either region.

When to use AutoFilter#

Use AutoFilter when you want to look at part of a table, not build something from it. It is the better choice when:

• You are answering a quick question, like "which North expenses are under $1,000?", and will clear the filter afterwards.
• You need menu-based filters: text that contains a word, filter by cell colour, top 10, or dates grouped by month.
• The file must work in any Excel version.
• You want to edit the matching rows in place, for example to fix a typo in every Sales row.
• You want to copy just the matches. Copying a filtered range copies only the visible rows.

To total the visible rows, use =SUBTOTAL(109,C2:C9). A plain =SUM(C2:C9) still adds the hidden rows too. Admin roles use this every day; see the Excel skills for admin jobs.

When to use the FILTER function#

Use FILTER when the matching rows are a result that other people or formulas depend on. It is the better choice when:

• You need a live list that updates whenever the data changes, such as "open tickets" on a summary sheet.
• You want the matches on another sheet, for example =FILTER(Data!A2:C500,Data!B2:B500="East","None").
• You need to calculate with the matches. =ROWS(FILTER(A2:C9,B2:B9="East")) counts East deals, and =SUM(FILTER(C2:C9,B2:B9="East")) totals them (33,700).
• You want to sort the result too. =SORT(FILTER(A2:C9,B2:B9="East"),3,-1) lists East deals from largest to smallest.
• Several people use the file, and you don't want anyone to forget a filter is on.

Analysts use FILTER to build small live reports; see the Excel skills for data analysts. To get a list of each region once, rather than every matching row, the function you want is UNIQUE; we compare it with the menu tool in UNIQUE vs Remove Duplicates.

Common mistakes with FILTER and AutoFilter#

These cause most wrong answers:

1. Trusting SUM on a filtered table. SUM ignores the filter. Use SUBTOTAL(109, …) for visible rows only.
2. Forgetting a filter is on. Someone opens the file, sees 40 rows instead of 400 and reports the wrong total. Look for blue row numbers and funnel icons, and clear filters before you share.
3. Using AND() or OR() inside FILTER. AND(B2:B9="East",C2:C9>=10000) returns a single TRUE or FALSE for the whole range, not one per row. Multiply or add the tests instead.
4. No if_empty message. When nothing matches, FILTER shows #CALC!. Add a third argument: =FILTER(A2:C9,B2:B9="North","None").
5. Blocking the spill. If any cell where the results need to go isn't empty, FILTER shows #SPILL!. Clear the cells below and to the right.
6. Ranges of different heights. =FILTER(A2:C9,B2:B10="East") returns #VALUE!, because the test must have as many rows as the array.
7. Typing over a spilled result. You can't edit one cell of a spill. Change the source data or the formula instead.

Do FILTER and AutoFilter treat hidden rows the same?#

No. AutoFilter hides rows, and functions such as SUM and AVERAGE still read them. FILTER doesn't hide anything: it reads every row of its source and returns the ones that pass, even if some source rows are hidden by an AutoFilter at the time. If you combine them, the FILTER result won't follow what the AutoFilter shows.

Averages of filtered data need the same care. A few very large deals can pull an average up, which is why we explain AVERAGE vs MEDIAN separately. And if your condition is a date, such as "due before today", see TODAY vs NOW to pick the right function for the test.

Our recommendation#

Learn AutoFilter first, because it works everywhere and job tests often ask you to "filter the table" directly. Then learn the FILTER function for anything that has to stay up to date.

• Looking, checking or editing a subset: AutoFilter, plus SUBTOTAL(109, …) for totals.
• A live list, a list on another sheet, or numbers built from the matches: FILTER with an if_empty message.
• Older Excel: AutoFilter only.

Practise it#

Try the FILTER exercise on this page, then open the FILTER function lesson to add AND and OR conditions, an if_empty message and a count with ROWS. After that, the filter a table lesson walks you through AutoFilter with Ctrl+Shift+L, number filters and SUBTOTAL, the way an admin test would ask for it.