How to use FILTER in Excel
Returns only the rows that meet a condition. The result spills.
Syntax
arrayThe range to returnincludeA condition, like B2:B9="West"[if_empty]What to show if nothing matches optionalExample
Here’s FILTER on a small table of orders. The highlighted cells are the ones the formula reads.
=FILTER(C2:C9,B2:B9="West")Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with FILTER
No matches gives #CALC!
Add a third argument, such as "None", to show something friendlier.
The condition is a full-column comparison
Write B2:B9="West", comparing the whole range, not a single cell.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=FILTER(C2:C9,B2:B9="West"
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Text without quotes
=FILTER(C2:C9,B2:B9=West)
Text needs quotes. West isn’t a function or a cell, so Excel can’t read it. If you mean the word, write "West" with quotes.
Questions
What does the FILTER function do in Excel?
Returns only the rows that meet a condition. The result spills. The syntax is FILTER(array, include, [if_empty]).
Why is my FILTER formula giving the wrong answer?
No matches gives #CALC!: Add a third argument, such as "None", to show something friendlier. The condition is a full-column comparison: Write B2:B9="West", comparing the whole range, not a single cell.
Does FILTER work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.