Excel + Google Sheets · Deep guide
FILTER in Excel: return every row that matches
Lookup functions return one answer, but often you need all of them: every invoice from one customer, every task of one person, every expense of one category. FILTER returns every row that meets a condition and spreads the results over the cells below, without copying a formula down.
FILTER needs Excel 2021 or Microsoft 365, and it also works in Google Sheets. It does not work in Excel 2019 or earlier, so avoid it in files meant for people on older versions. In this guide you will see the syntax, a worked example with real numbers and the usual mistakes. See also: XLOOKUP guide and COUNTIF guide.
How FILTER works
FILTER takes the range to return, the test that decides which rows stay and, optionally, a message for when nothing matches. Write the formula like this:
=FILTER(C2:C6,B2:B6="b")Excel looks at column B, keeps the rows where the value is the letter b and returns the matching values of column C. The results spill into the cells below, which must be empty. Works in Excel 2021 or Microsoft 365.
Step by step example
- Type the categories in column B and the prices in column C.
- Click an empty cell, for example E2, with empty cells below it.
- Type
=FILTER(C2:C6,B2:B6="b")and press Enter. - Compare the result with the table below.
| A | B | |
|---|---|---|
| 1 | Category (B) | Price (C) |
| 2 | a | 10 |
| 3 | b | 20 |
| 4 | c | 30 |
| 5 | b | 40 |
| 6 | d | 50 |
The expected result is two values, 20 and 40, because the category b appears twice. Add a third argument such as the word none to show a message instead of an error when nothing matches.
Common errors
- The cells below the formula are not empty and Excel shows a spill error. Clear them first.
- Nothing matches and the cell shows an error. Add the third argument with a friendly message.
- The test range and the range to return have different sizes. Both must have the same number of rows.
- You open the file in Excel 2019 or earlier and the cell shows a name error. FILTER needs a newer version.
You can wrap FILTER inside SUM or COUNT to total or count the matches in one cell. Compare the first result with a manual filter to confirm that the condition is written correctly.