What is the syntax for FILTER in Excel?

The syntax for FILTER is: =FILTER(array, include, [if_empty])

What are common errors with FILTER?

#CALC! if no matches and no if_empty specified. Arrays must be same size

How do I filter with multiple conditions in FILTER?

Use * for AND logic and + for OR logic. For AND: =FILTER(A2:D10, (B2:B10="Sales")*(C2:C10>100)). For OR: =FILTER(A2:D10, (B2:B10="Sales")+(B2:B10="Marketing")). The * multiplies TRUE/FALSE values (both must be TRUE), while + adds them (either can be TRUE).

Why does FILTER return #CALC! error?

FILTER returns #CALC! when no rows match your criteria. Always use the third argument (if_empty) to handle this: =FILTER(A2:D10, B2:B10="Sales", "No matches found"). You can also return an empty string "" or a specific message.

Can I use FILTER with SORT together?

Yes! Nest them for powerful results. =SORT(FILTER(A2:D10, C2:C10>100), 3, -1) filters first, then sorts by column 3 descending. This creates dynamic, auto-updating filtered and sorted lists without helper columns.

How do I filter for partial text matches?

Use ISNUMBER with SEARCH or FIND: =FILTER(A2:D10, ISNUMBER(SEARCH("keyword", B2:B10))). SEARCH is case-insensitive, FIND is case-sensitive. This returns all rows where column B contains "keyword" anywhere in the text.

Lookup & Reference

FILTER Function in Excel

Filters a range of data based on criteria you define. Returns an array of values that meet the specified conditions.

Syntax

=FILTER(array, include, [if_empty])

Arguments

array (required): The range or array to filter
include (required): Boolean array of same height/width as array
if_empty (optional): Value to return if no results match

Examples

=FILTER(A2:D10, C2:C10>100) - Filter rows where column C > 100 - Result: Filtered array
=FILTER(A2:B10, A2:A10="Sales", "No results") - Filter by text with empty handler - Result: Matching rows

Overview

FILTER returns every row that meets your conditions and spills the result. It is the M365 answer to “show me all East orders over $500” without copying formulas down.

Core patterns

One condition: =FILTER(A2:C100, C2:C100="East").
AND two conditions: =FILTER(A2:C100, (C2:C100="East")*(D2:D100>500)).
Sort results: =SORT(FILTER(...)).

Fix #SPILL! before changing logic

Clear cells below and to the right of the formula.
Unmerge destination cells.
Move formula out of Excel Tables if spill is blocked.

FILTER vs single-match lookups

Need first match only → VLOOKUP or XLOOKUP.
Compare: [FILTER vs VLOOKUP](/compare/filter-vs-vlookup/).
Spill help: [FILTER spill error](/problems/filter-spill-error/).

Quick answer: filter function excel

FILTER returns all rows that meet one or more conditions and spills the result. Requires Microsoft 365 or Excel 2021 with dynamic arrays.
#SPILL! means the output range is blocked — clear cells below/right.
Combine with SORT: =SORT(FILTER(...)).
Need only the first match? See [FILTER vs VLOOKUP](/compare/filter-vs-vlookup/).

FILTER best practices

Use FILTER when you need all matching rows, not just the first matching value.
Provide the if_empty argument to avoid #CALC! when no records match.
Make sure the include argument has the same height or width as the filtered array.

Fix #SPILL! on FILTER

Clear cells below and to the right of the formula so the full result can expand.
Unmerge cells in the spill destination range.
Move the formula to a column with enough empty rows for the filtered result.

FILTER + SORT + UNIQUE stack

Unique sorted list: =SORT(UNIQUE(A2:A100)).
Filtered then sorted: =SORT(FILTER(A2:C100,C2:C100="East"),3,-1) to sort filtered rows by column 3 descending.
Compare with [FILTER vs VLOOKUP](/compare/filter-vs-vlookup/) when you only need the first match.

Common errors

#CALC! if no matches and no if_empty specified
Arrays must be same size

Use cases

Dynamic filtering
Extract matching records
Create filtered reports

Frequently asked questions

Does FILTER work in Excel 2019? No. FILTER requires Excel 2021 or Microsoft 365 with dynamic arrays.
What is the if_empty argument? Returns custom text when no rows match instead of #CALC!.
FILTER vs Excel AutoFilter? AutoFilter is manual UI. FILTER is formula-driven and updates when data changes.
How do I filter with multiple conditions in FILTER? Use * for AND logic and + for OR logic. For AND: =FILTER(A2:D10, (B2:B10="Sales")*(C2:C10>100)). For OR: =FILTER(A2:D10, (B2:B10="Sales")+(B2:B10="Marketing")). The * multiplies TRUE/FALSE values (both must be TRUE), while + adds them (either can be TRUE).
Why does FILTER return #CALC! error? FILTER returns #CALC! when no rows match your criteria. Always use the third argument (if_empty) to handle this: =FILTER(A2:D10, B2:B10="Sales", "No matches found"). You can also return an empty string "" or a specific message.
Can I use FILTER with SORT together? Yes! Nest them for powerful results. =SORT(FILTER(A2:D10, C2:C10>100), 3, -1) filters first, then sorts by column 3 descending. This creates dynamic, auto-updating filtered and sorted lists without helper columns.
How do I filter for partial text matches? Use ISNUMBER with SEARCH or FIND: =FILTER(A2:D10, ISNUMBER(SEARCH("keyword", B2:B10))). SEARCH is case-insensitive, FIND is case-sensitive. This returns all rows where column B contains "keyword" anywhere in the text.

Editorial review

Reviewed by Excel.Directory Editorial Team. Updated May 2026.