Excel functions / Lookup & Reference
Lookup & Reference function · Excel
FILTER function
FILTER returns only the rows that meet your condition. The result spills into the cells below and updates when the data changes.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| array | Yes | The data to return. |
| include | Yes | A TRUE or FALSE test with the same height as the data. |
| if_empty | No | Excel only. The result when nothing matches. |
Worked example
| A | B | C | |
|---|---|---|---|
| 1 | Region | Month | Sales |
| 2 | East | Jan | 1200 |
| 3 | West | Jan | 950 |
| 4 | East | Feb | 1400 |
| 5 | West | Feb | 800 |
| 6 | East | Feb | 300 |
=FILTER(C2:C6,A2:A6="East")Result: 1200, 1400, 300
Three rows have East in column A, so their sales spill into three cells.
Try it
Edit the formula and run it against the table above. Results come from a browser spreadsheet engine.
Availability
| Platform | Status |
|---|---|
| Excel | Microsoft 365, Excel 2024, Excel 2021, Excel for the web. |
| Google Sheets | Available. Extra conditions are separate arguments. |
Common mistakes
- Occupied cells below the formula cause #SPILL! in Excel and a #REF! error in Sheets.
- In Excel, join conditions with * for AND and + for OR.
Do it with AI
Paste this prompt into Copilot, Gemini or another assistant. Check the result on a small sample first.
Write a FILTER that returns all columns from A2:C100 where region is East and sales are above 1000. Give the Excel and Google Sheets versions.