Back to All Formulas
Dynamic ArraysLevel 5: Blue (Analytics & Pivot Models)Difficulty: Advanced
How to Use FILTER in Excel
Filters a range of data dynamically based on criteria and spills the matching records automatically.
Formula Syntax
=FILTER(array, include, if_empty)
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| array | Required | The data range to filter. |
| include | Required | A boolean array condition (e.g. B2:B10='North'). |
| if_empty | Optional | Value to return if no items match (e.g. 'No results'). |
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Order ID | Status | Amount |
|---|---|---|
| ORD-101 | Delivered | 1200 |
| ORD-102 | Pending | 850 |
| ORD-103 | Delivered | 3400 |
Applied Formula:Result: Spills 2 rows: ORD-101 & ORD-103
=FILTER(A2:C4, B2:B4="Delivered", "None")
How it works: Returns only the rows where Status is 'Delivered' in real-time as data changes.
Common Errors & How to Fix Them
#CALC!
Why it happens: No rows matched the criteria and the if_empty argument was omitted.
Fix: Provide an if_empty value like 'No Records Found'.
#SPILL!
Why it happens: Cells in the output path are occupied.
Fix: Ensure there is empty space beneath and to the right of the formula cell.
Ready to Master Excel?
Explore 7 LevelsMaster FILTER in Level 5
Learn FILTER alongside 500+ formulas, hands-on practice workbooks, and verifiable certificates.