How to Use the FILTER Function in Excel

FILTER returns every row of a table that meets a condition. You tell it what to return and which rows to keep, and the matching rows spill into the cells below the formula. When the data changes, the list updates by itself:

It works like the Filter button on the Data tab, except the original table stays as it is, and the result is a live list you can sort, count or use in other formulas. FILTER is available in Microsoft 365, Excel 2021 and later, and Excel for the web.

The examples use this inventory in A1:E9:

Each formula below goes in H2, with the cells under and beside it empty, so the results have room to spill.

#The FILTER syntax

The include test must cover the same rows as array. If array is B2:B9, the test must check rows 2 to 9 too. It doesn't have to test a column you're returning, so you can return names while testing prices.

#A basic FILTER example

Which products are in the Lighting category?

You type the formula once, in H2, and press Enter. FILTER checks C2:C9 row by row, keeps the two rows that say Lighting, and spills their names into H2 and H3. Click H3 and the formula bar shows the formula grayed out, because it lives in H2.

The test can be any comparison. Which products have more than 50 in stock?

To return whole rows, make array the whole table:

FILTER returns values, not formatting, so the prices show as plain numbers until you format column K as currency. Dates come back as numbers too, so format them as dates.

Your turn. The sheet below is live and uses the same inventory. This time the exercise asks for a different category: change what you compare column C with, then press Enter and watch the list spill.

#When nothing matches: if_empty and #CALC!

If no row passes the test, FILTER has nothing to return, so it shows #CALC!. There's no Outdoor category, and nothing costs more than $500:

The third argument, if_empty, is what FILTER shows instead:

Use "" as if_empty to leave the cell looking blank. When at least one row matches, if_empty is ignored, so it's safe to add to any FILTER whose test could come up empty, such as one driven by a drop-down.

It's also safer than wrapping FILTER in IFERROR, because IFERROR hides real mistakes too. Here the test is one row short, C2:C8 instead of C2:C9:

The first formula tells you something is wrong. The second claims there are no Lighting products, when there are two.

#FILTER with multiple criteria (AND)

To keep only the rows that pass two tests, put each test in brackets and multiply them. Which products cost under $100 and are in stock?

Each test gives a TRUE or FALSE for every row. When you multiply them, Excel treats TRUE as 1 and FALSE as 0, so a row only scores 1 when both tests are TRUE:

FILTER keeps every row that isn't 0. You can multiply as many tests as you need. A price between $50 and $150 is two tests on the same column:

#FILTER with OR conditions

To keep rows that pass either test, add the tests instead. Which products are Lighting or Accessories?

The results stay in the same order as the table. Adding gives 0 when neither test is TRUE, and 1 or more when at least one is. A row that passes both tests scores 2, but FILTER still lists it once. Here are the Accessories, plus anything with more than 100 in stock:

Cable tray is an accessory with 210 in stock, so it passes both tests, but it appears only once.

You can mix OR and AND, as long as you add brackets around the OR part. This lists Lighting or Furniture products, but only the ones in stock:

Excel multiplies before it adds, so without the extra brackets the stock test only applies to Furniture, and the out-of-stock Floor lamp slips in:

#Filter rows that contain text

FILTER's test is a plain comparison, so wildcards don't work in it. B2:B9="*desk*" looks for a product literally named *desk*, finds none and returns #CALC!.

To match part of a cell, use SEARCH. It returns the position of the text, or a #VALUE! error if the text isn't there, and ISNUMBER turns that into TRUE or FALSE:

SEARCH ignores upper and lower case, so "desk" finds Desk lamp as well as Standing desk. For a case-sensitive match, use FIND instead:

Don't leave out ISNUMBER. SEARCH gives an error for every row that doesn't match, and FILTER passes that error on instead of skipping the row.

#Return only some columns

The array argument decides which columns come back. For columns that sit next to each other, narrow the range. Here are the product, category and price of everything with more than 100 in stock:

For columns that aren't next to each other, wrap FILTER in CHOOSECOLS and list the columns you want, counted from the left of array. Here are the name and stock of each accessory:

CHOOSECOLS needs Microsoft 365, Excel 2024 or Excel for the web. In Excel 2021, use a second FILTER on the columns. Give it a row of 1s and 0s, one for each column, where 1 means keep:

This returns the same two columns.

#FILTER with SORT and UNIQUE

FILTER keeps the order of the table. Wrap it in SORT to put the results in order. These are the products in stock, A to Z:

To sort a result with several columns, give SORT the column to sort by and -1 for largest first. This lists the products in stock, by price from highest to lowest:

The 3 means the third column of the filtered result, Price, not column C of the sheet.

UNIQUE removes repeats. Which categories have a product with more than 50 in stock?

For a single number instead of a list, wrap FILTER in SUM, AVERAGE or COUNTA. This adds up the stock of every accessory:

With one condition, =SUMIFS(E2:E9, C2:C9, "Accessories") gives the same 266 and works in every version of Excel. See the SUMIF and SUMIFS guide. FILTER earns its place when the condition is something SUMIFS can't do, like an OR across two columns.

#How to fix the #SPILL! error

FILTER needs an empty cell for every result. If anything is in the way, even a space or a formula that returns "", you get #SPILL! instead of the list. The three accessories need H2:H4, so with something typed in H3:

Click the formula cell and Excel draws a dashed border around the cells it wants to use. Clear H3 and the list appears. To check how many rows a FILTER needs, COUNTIF counts the matches: =COUNTIF(C2:C9, "Accessories") returns 3.

The other common causes:

• The formula is inside an Excel table. Spilled formulas don't work in tables. Move the formula outside the table.
• Merged cells are in the way. Unmerge them, or move the formula.

#Common FILTER mistakes

#Frequently asked questions about FILTER

#Is FILTER case-sensitive?

No. C2:C9="lighting" matches Lighting. For an exact-case match, test with EXACT:

#What's the difference between FILTER and XLOOKUP?

XLOOKUP returns the first match only. FILTER returns every match:

Use XLOOKUP when each value appears once, such as an ID, and FILTER when you want them all. The XLOOKUP guide covers lookups in detail.

#Does FILTER pick up new rows?

Only if its ranges cover them. If you add a product in row 10, B2:B9 misses it. Format the data as an Excel table with Ctrl+T, and use the table's column names so the ranges grow with it. If the table is named Inventory:

Put the formula outside the table, because spilled formulas can't go inside one.

#How do I filter by a drop-down?

Point the test at the cell instead of typing the value. With a drop-down of categories in G1, made with Data Validation:

Pick a different category and the list changes.

#Which versions of Excel have FILTER?

FILTER works in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web, on Windows and Mac. It isn't in Excel 2019 or older, where it returns #NAME?. If a file has to work in those versions, use the Filter button on the Data tab, or INDEX and MATCH when you only need the first match.

Google Sheets has FILTER too, but extra conditions go in as separate arguments, as in =FILTER(B2:B9, D2:D9<100, E2:E9>0), and there's no if_empty argument. For Microsoft's own examples, see the FILTER function page.

For more practice, the dynamic arrays topic has Premium exercises on FILTER with AND, FILTER with OR and adding up a filtered list.

#Key takeaways

• =FILTER(array, include, [if_empty]) returns every row where include is TRUE, and the results spill into the cells below.
• Multiply tests for AND, add them for OR, and give each test its own brackets.
• Add if_empty so an empty result shows a message instead of #CALC!, and clear the cells in the way when you see #SPILL!.
• FILTER needs Microsoft 365, Excel 2021 or later, or Excel for the web.