FILTER - Return the records that meet a rule
Create a low-stock report as a spilled result, then combine Excel conditions without losing record alignment.
By the end, you can
- Build a Boolean include vector.
- Return complete matching records.
- Handle no matches and blocked spills deliberately.
Before you start: Understand comparisons, aligned ranges, and an empty output area.
In this lesson
A report that expands into cells
FILTER returns the matching rows or columns of an array. An inventory team can produce a low-stock list without copying qualifying rows manually. One formula at the output's top-left cell can return many records; this is a spilled array. The source stays unchanged, and the result updates when the formula's inputs recalculate.
Syntax and arguments
=FILTER(array, include, [if_empty])include is an aligned TRUE/FALSE or equivalent include array. A matching row mask has the same height as the source; a column mask has the same width.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | Records or values to return. | A2:B6 |
includeRequired | A Boolean condition for each relevant source row or column. | B2:B6<10 |
if_emptyOptional | A deliberate result when nothing matches; omitted can give #CALC!. | "No matches" |
A two-column low-stock report
Printer paper| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Stock | Status | Low-stock product | Stock | |
| 2 | Printer paper | 4 | Active | Printer paper | 4 | |
| 3 | Pens | 15 | Active | USB cables | 7 | |
| 4 | USB cables | 7 | Paused | Desk lamps | 2 | |
| 5 | Desk lamps |
Empty results and multiple conditions in Excel
=FILTER(A2:B6,B2:B6<0,"No matches")No stock value is negative, so the chosen scalar fallback is returned.
=FILTER(A2:C6,(B2:B6<10)*(C2:C6="Active"),"No matches")In Excel, multiplication combines per-row conditions. Paused USB cables are excluded even though stock is low.
=FILTER(A2:B6,(B2:B6<10)+(B2:B6>20),"No matches")Excel's added Boolean masks include rows satisfying either rule, in their source order.
Spill, alignment, and compatibility
- A blocked output cell causes #SPILL!. Place the anchor outside an Excel Table and keep the destination available; the source may use a Table's structured references.
- A fixed A2:B6 source does not automatically include row 7. Use an appropriate expanding source when new records must enter the report.
- An error in the include mask can propagate. A single aggregated AND across whole columns is not the same as a per-row include mask.
- FILTER is available in Microsoft 365 and Excel 2021/2024, not perpetual Excel 2019/2016. Linked dynamic arrays across workbooks require the source workbook to remain open.
Common mistakes and Practice scope
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.