Quick Navigation

FILTER Function

Excel 365, Excel 2019+

Summary

The FILTER function dynamically extracts rows or columns from an array based on specified criteria, returning only the matching records. This powerful dynamic array function revolutionizes data analysis by eliminating the need for complex helper columns or manual filtering.

Syntax

FILTER(array, include, [if_empty])

Parameters

Parameter	Type	Required	Description
array	`Array/Range`	Yes	Source data to filter - can be range, table, or array formula
include	`Boolean Array`	Yes	Filter condition matching array dimensions - non-Boolean values coerce to TRUE/FALSE
if_empty	`Any`	No	Fallback value when filter returns no results

Using the FILTER Function

FILTER transforms static data analysis into dynamic, criteria-driven insights. Use it to extract specific records without altering source data, combine with other dynamic array functions like SORT, UNIQUE, and SEQUENCE for complete data manipulation workflows.

Common FILTER Examples

Filter Records by Single Criterion

=FILTER(A5:D20,C5:C20="Apple","No matches")

Returns all rows where Product equals Apple, showing 'No matches' if none found

Multiple Criteria (AND Logic)

=FILTER(A5:D20,(C5:C20="Apple")*(A5:A20="East"),"")

Returns records that are both Apple products AND in East region using * operator

Multiple Criteria (OR Logic) with Sorting

=SORT(FILTER(A5:D20,(C5:C20="Apple")+(A5:A20="East"),""),4,-1)

Filters Apple OR East records, sorts Units column descending

Filter Numeric Values Above Threshold

=FILTER(A5:E20,E5:E20>1000)

Returns all rows where Units exceed 1000

Frequently Asked Questions

Without if_empty argument, returns #CALC! error. Always include if_empty for production formulas.

Yes, use structured references like =FILTER(Table1,Table1[Product]="Apple") for automatic resizing.

#SPILL! occurs when results have no empty space. Clear adjacent cells or use fewer columns.

Common Errors and Solutions

#CALC! error

Cause: No records match criteria and if_empty omitted

Solution: Add if_empty parameter: =FILTER(A1:C10,B1:B10>5,"None")

#VALUE! error

Cause: include array dimensions don't match source array

Solution: Ensure include array has same height (vertical filter) or width (horizontal filter)

#SPILL! error

Cause: No empty cells adjacent to formula for results

Solution: Clear space around formula or reference smaller output range

Notes

Requires Excel 365 or Excel 2021+ with dynamic arrays
Automatically spills results - no need to select output range
include argument coerces numbers/text to Boolean (non-zero/non-empty = TRUE)
Works with 2D arrays filtering rows OR columns
Limited cross-workbook support - both files must be open

Compatibility

Available in: Excel 365, Excel 2021, Excel 2019 (limited), Excel for the Web

Not available in: Excel 2016 and earlier, Excel 2019 perpetual license (no dynamic arrays)

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2019+