FILTER: show only the rows that pass the test
FILTER builds a live view of records instead of altering the original list. It is handy for review queues, but an empty result and a blocked spill range need different responses.
Formula syntax
=FILTER(array, include, [if_empty])
What goes into it
array
Range or array to return rows/columns from.
include
TRUE/FALSE array that decides what to keep.
if_empty
Optional value when no rows match.
A quick example first
=FILTER(A2:D100,D2:D100="Open","No rows")
Returns rows where column D is Open.
=FILTER(A2:C100,(B2:B100>=70)*(C2:C100="East"),"None")
Applies two AND-style conditions.
Build a live queue of open East-region orders
Enter this source table in A1:C5. Put the formula in E2 with a clear output area extending to G3.
| Row | A: Order | B: Region | C: Status |
|---|---|---|---|
| 2 | O-11 | East | Open |
| 3 | O-12 | West | Open |
| 4 | O-13 | East | Closed |
| 5 | O-14 | East | Open |
| Formula | Expected result / interpretation |
|---|---|
| =FILTER(A2:C5,(B2:B5=”East”)*(C2:C5=”Open”),”No open orders”) | Two rows: O-11 / East / Open and O-14 / East / Open |
| =FILTER(A2:C5,B2:B5=”North”,”No orders”) | No orders |
Multiplication combines the two Boolean tests as AND. The result is a new view; editing its source requires changing the original table. The output can grow or shrink when source values change, so do not put unrelated notes immediately underneath it.
Before you copy this into a real sheet
This Excel example needs dynamic-array support such as Microsoft 365, Excel 2021 or Excel 2024. A blocked destination can cause #SPILL!, and an error in the include condition is not fixed by the no-results argument. Keep the output outside an Excel Table even if the source uses one. Google Sheets FILTER has different argument conventions: its later arguments are additional conditions, not Excel’s if_empty text. Do not paste this formula into Sheets unchanged.
Useful habits
- Keep the include array aligned with the filtered array.
- Provide if_empty to avoid an avoidable no-result error.
- Leave enough blank cells for the result to spill.
Where people get caught out
- Blocking the spill range with existing values.
- Using mismatched range sizes inside the include calculation.
Other functions worth a look
A detail worth remembering
Leave blank space for the results to spill. If the view needs to keep a fixed structure, test what should happen when zero rows satisfy the condition.
Reference and examples
The examples above use small, invented datasets and English-language formula names. Check your Excel version and argument separator against the official Excel function reference if a formula behaves differently on your computer.