FILTER
Shows only the rows that meet a condition: orders from one city, deals of one sales rep, products above a price threshold.
FILTER(array, include, [if_empty])- [ ]optional part
- array
- The table without headers to pick rows from.
- include
- A condition for one column of the same height, for example B2:B9="Chicago".
- if_empty
- What to show if no row matches. Without it, you get #CALC!.Optional
Return value
The rows that meet the condition. They spill down into the neighboring cells. If no row matches, the function returns the third argument, or #CALC! without it.
Examples
Orders from one city
Details
FILTER takes a table and a condition and returns only the matching rows. The condition compares a column with a value: B2:B9="Chicago". A row makes it into the result if the condition is true for it.
You write the formula in one cell, and the result spills down and to the right across as many rows and columns as needed. The cells below the formula must be empty, or you get #SPILL!.
The function is available in Excel 2021 and Microsoft 365. Excel 2019 and earlier don't have it, and the formula shows #NAME? there.
Common mistakes
- There is data below or to the right of the formula. The result has nowhere to spill, and #SPILL! appears.
- The table and the condition have different heights, for example A2:C9 and B2:B8. The function returns an error.
- The third argument is left out. If nothing is found, you get #CALC! instead of a clear message.
In Google Sheets
In Google Sheets, FILTER has no if_empty argument: any extra arguments are more conditions, and an empty result gives an error.