FILTER Function
Excel Guides / Dynamic Arrays & LAMBDA
Dynamic Arrays & LAMBDA
Pull every row that meets your conditions into a live list that updates on its own.
Updated 12 Oct 2026 Ā· 5 min read Ā· By Sky Dashboards
FILTER pulls every row that meets your conditions into a new range, and it updates the moment the data changes. No AutoFilter buttons, no copying and pasting. It is the function behind most live lists in a workbook: this month's orders, unpaid invoices, the clients for one coach. This guide covers the syntax, how spilling works, AND and OR conditions, sorting the result and using it in other formulas.
In this guide
FILTER syntax
=FILTER(array, include, [if_empty])| Argument | Required? | What it does |
|---|---|---|
array |
Required | The range or array to return rows from, for example A2:C8. Leave the header row out. |
include |
Required | A TRUE/FALSE test with the same number of rows as array (or the same number of columns, to filter columns). Rows where the test is TRUE are kept. |
if_empty |
Optional | What to return when nothing matches, such as "No results". Without it, an empty result returns #CALC!. |
FILTER is a dynamic array function. It is available in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. Excel 2019 and earlier cannot calculate it.
Your first FILTER formula
Here is a stationery shop's sales list. To list only the online sales, type one formula in E2:
| A | B | C | D | E | F | G | ||
|---|---|---|---|---|---|---|---|---|
| 1 | Product | Channel | Sales | Product | Channel | Sales | ||
| 2 | Notebook A5 | Online | $1,240.00 | Notebook A5 | Online | $1,240.00 | ||
| 3 | Pen set | Market | $380.00 | Planner 2027 | Online | $2,150.00 | ||
| 4 | Planner 2027 | Online | $2,150.00 | Washi tape | Online | $295.00 | ||
| 5 | Washi tape | Online | $295.00 | Desk pad | Online | $860.00 | ||
| 6 | Sticker pack | Market | $410.00 | |||||
| 7 | Desk pad | Online | $860.00 | |||||
| 8 | Greeting cards | Market | $520.00 |
One formula in E2 returns all four online sales. The headers in E1:G1 are typed in.
You type the formula once, in E2, and Excel fills the rest of the result, four rows by three columns, by itself. This is called spilling, and the cells it fills are the spill range. Click any cell inside it and Excel draws a blue border round the whole range and shows the formula greyed out in the formula bar. Only E2 holds the formula, so that is the only cell you can edit.
Change a channel, or edit a row inside A2:C8, and the result updates straight away. To pick up new rows as well, turn the data into an Excel table called Sales and use =FILTER(Sales, Sales[Channel]="Online").
Filter by a cell, not typed text
Point the test at a cell: =FILTER(A2:C8, B2:B8=I1). Put a drop-down list in I1 and the result changes each time you pick a channel.
Spill ranges and the #SPILL! error
A spill needs empty cells to land in. If anything is in the way, even a single space or a formula that returns empty text, Excel will not overwrite it. The formula cell shows #SPILL! and a dashed border marks where the result wants to go. The usual causes:
- Something in the way. Click the warning icon next to the error, choose Select Obstructing Cells, then clear them.
- The formula is inside an Excel table. Spilled formulas cannot live in a table. Put the FILTER next to the table and refer to the table by name.
- Merged cells in the spill range. Unmerge them.
- Too close to the edge of the sheet. The result would run past the last row or column.
A different error, #CALC!, means no rows matched and FILTER has nothing to return. Fill in the third argument to show a message instead:
=FILTER(A2:C8, B2:B8="Wholesale", "No sales")There are no Wholesale rows, so E2 shows the text No sales.
Watch out
Never type over one value inside a spill range to correct it. Whatever you type blocks the spill, and the whole result turns into #SPILL!. Fix the source data instead.
AND and OR conditions
FILTER has no separate argument for a second condition. Instead you combine tests inside include with arithmetic. TRUE counts as 1 and FALSE as 0, and FILTER keeps any row where the result is not zero.
AND: multiply the tests
=FILTER(A2:C8, (B2:B8="Online")*(C2:C8>500))Online sales over $500: Notebook A5, Planner 2027 and Desk pad. Washi tape is online but only $295.
A row only scores 1 when every test is TRUE, because anything multiplied by 0 is 0.
OR: add the tests
=FILTER(A2:C8, (B2:B8="Market")+(C2:C8>2000))Market sales plus anything over $2,000: Pen set, Planner 2027, Sticker pack and Greeting cards.
Adding gives 1 or 2 when at least one test is TRUE, and 0 only when both are FALSE. Wrap each test in its own brackets, otherwise Excel does the multiplying or adding before the comparison.
Text that contains a word
=FILTER(A2:C8, ISNUMBER(SEARCH("pen", A2:A8))) keeps every row with pen anywhere in the product name. SEARCH ignores case; swap in FIND for a case-sensitive match.
Sort the result and return the top rows
FILTER keeps rows in their original order. Wrap it in SORT to reorder them:
=SORT(FILTER(A2:C8, B2:B8="Online"), 3, -1)Online sales sorted by the third column of the result (Sales), largest first.
SORT's second argument is the column number inside the filtered result, not the sheet column, and -1 means descending. When you want to sort by something that is not in the output, use SORTBY. This lists product names only, ordered by sales:
=SORTBY(FILTER(A2:A8, B2:B8="Online"), FILTER(C2:C8, B2:B8="Online"), -1)Returns Planner 2027, Notebook A5, Desk pad, Washi tape. The LET function lets you write the repeated test once.
For a top-N list, add TAKE, which keeps the first rows of an array:
=TAKE(SORT(FILTER(A2:C8, B2:B8="Online"), 3, -1), 3)The three best online sellers. TAKE needs Microsoft 365 or Excel 2024.
Refer to the result with the # operator
Other formulas can use a spill without knowing how big it is. Type the top-left cell of the result followed by #. In the example, E2# means the whole result, however many rows it has today.
-
=ROWS(E2#)counts the matching rows: 4. -
=SUM(INDEX(E2#, 0, 3))totals the Sales column of the result: $4,545.00. -
=MAX(INDEX(E2#, 0, 3))returns the largest online sale: $2,150.00.
Tip
Build summaries on E2# rather than a fixed range like E2:G20. When the filter returns more or fewer rows, everything that points at the spill follows it, and nothing is left counting blank cells.
Frequently asked questions
Why does FILTER return #CALC!?
Nothing matched the conditions, and FILTER cannot return an empty list. Add the third argument, for example "None", to show a message instead.
Can FILTER return only some of the columns?
Yes. Point array at just those columns, such as A2:A8 for product names only. For columns that are not side by side, wrap the source in CHOOSECOLS (Microsoft 365 and Excel 2024): =FILTER(CHOOSECOLS(A2:C8, 1, 3), B2:B8="Online").
Is FILTER case-sensitive?
No. B2:B8="online" also matches Online. For a case-sensitive test use EXACT: =FILTER(A2:C8, EXACT(B2:B8, "Online")).
Does FILTER work in Excel 2019?
No. It needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. In older versions use AutoFilter or Advanced Filter on the Data tab.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā