FILTER

Pull specific rows from a range based on conditions you define, returning a dynamic array of matching results.

Lookup & Reference
|
Excel 2019+
|
Google Sheets Supported

Spreadsheet editor

Syntax

=FILTER(array, include, [if_empty]) Returns: Array

Arguments

Argument Required Description
array Yes The range or array you want to filter.
include Yes A Boolean array the same height (or width) as the array. Rows where this evaluates to TRUE are included in the results.
if_empty No The value to return when no rows match. Without this, Excel returns a #CALC! error if nothing qualifies.

About

The FILTER function returns only the rows (or columns) from a range that meet the conditions you set. It produces a dynamic array, so Excel automatically spills the results into neighboring cells. Use it whenever you need a live subset of your data, for example pulling all orders from a specific region or listing employees in a certain department.

FILTER really shines when you combine it with other dynamic array functions. Wrap it in SORT to order the results, or pass its output to UNIQUE to remove duplicates. You can also apply multiple conditions at once: multiply conditions together for AND logic, or add them for OR logic.

Because the results update automatically when your source data changes, FILTER is a great replacement for manual filtering or complex INDEX/MATCH lookups. Just keep in mind that if no rows match, Excel returns a #CALC! error unless you provide the optional if_empty argument.

Examples

Handling zero matches with if_empty

Change the region in B6 to "West" and see what happens: no rows match, so FILTER falls back to the if_empty message. Remove the third argument to see the #CALC! error you'd get without it.

Spreadsheet editor

AND logic with multiple conditions

Try combining two conditions by multiplying them together. Edit the sales values or change the region — only rows that pass both tests appear in the results.

Spreadsheet editor

FILTER + SORT for ordered results

Wrap FILTER inside SORT to get filtered results in order. Here high-priority tasks come back sorted by due date. Change a priority to see the list update instantly.

Spreadsheet editor

Watch out for

⚠

#CALC! error with no matches

When no rows satisfy the condition and you haven't supplied the if_empty argument, Excel returns a #CALC! error.

→ Always provide the third argument, even if it is just an empty string (""), to handle the case where nothing matches.

⚠

Mismatched array sizes

The include array has a different number of rows (or columns) than the source array, causing an error.

→ Make sure the include range covers exactly the same number of rows as the array argument. For example, if your data is A2:D50, the condition range should also span 49 rows (like B2:B50).

⚠

Errors inside the include array

If any cell in the include range contains an error (such as #N/A or #VALUE!), the entire FILTER formula returns that error.

→ Clean up errors in the condition range first, or wrap the condition in IFERROR to convert errors to FALSE before passing them to FILTER.

Tips & notes

FILTER is a dynamic array function, so it is only available in Excel 2021, Excel for Microsoft 365, and Excel for the web. The results spill automatically, so make sure the cells below (or beside) the formula are empty. If you are working in Google Sheets, the same function is available with identical syntax.

Common questions

How do I apply AND and OR conditions in FILTER?

For AND logic, multiply conditions together: (condition1)*(condition2). For OR logic, add them: (condition1)+(condition2). Wrap each condition in parentheses to keep the Boolean math correct.

Can I filter columns instead of rows?

Yes. If the include argument is a horizontal Boolean array (one row), FILTER returns the matching columns instead of rows.

Can I nest FILTER inside other functions?

Yes. You can pass the result of FILTER to functions like SUM, AVERAGE, SORT, or UNIQUE. This lets you calculate totals or rearrange a filtered set in one formula.