Dynamic arrays
FILTER function playground
Returns the rows of a range that meet a condition.
The playground runs in your browser. Nothing you type is sent anywhere.
How FILTER works
FILTER works out the include test for every row, keeps the rows where it is TRUE and returns them as a new list that spills into the cells below and to the right.
Multiply tests for AND: (B2:B11="West")*(D2:D11="Laptop"). Add them for OR. The result updates automatically when the data changes.
Syntax
=FILTER(array, include, [if_empty])| Argument | What it means |
|---|---|
arrayrequired | The range to filter, such as A2:F11. |
includerequired | A TRUE/FALSE test with one result per row, such as B2:B11="East". |
if_emptyoptional | What to show when no rows match. Without it you get #CALC!. |
Examples
| Formula | What it does | Result |
|---|---|---|
=FILTER(A2:F11,B2:B11="East") | Every East sale, all columns. | 1/5/2026 (spills 4×6) |
=FILTER(C2:C11,F2:F11>=2000) | Reps with a sale of 2,000 or more. | Ana (spills 3×1) |
=FILTER(A2:F11,(B2:B11="West")*(D2:D11="Laptop")) | Two conditions (AND): West laptops. | 2/11/2026 (spills 2×6) |
=FILTER(C2:C11,B2:B11="South","No sales") | No match, so the if_empty text appears. | No sales |
Common mistakes
#SPILL!The result needs empty cells to spill into. Clear anything in the way.
Test sizeinclude must have the same number of rows as array.
Related functions
Learn more
Download the example workbook (.xlsx)
The workbook has the same sample data and formulas, plus a practice sheet.