ExcelDatabaseIntermediate

FILTER

Filters a range of data based on criteria you define.

Read the syntaxReview worked examplesOpen the spreadsheet app
=FILTER(array, include, [if_empty])
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Filters a range of data based on criteria you define.

The FILTER function is a high-performance dynamic array tool used to extract specific rows or columns from a dataset based on one or more logical conditions. Unlike static filtering tools, it automatically 'spills' the results into neighboring cells and updates instantly when source data changes. A critical technical requirement is that the 'include' argument must have a height or width identical to the source 'array.' For advanced filtering, you can apply Boolean logic: use multiplication (*) to represent AND conditions and addition (+) to represent OR conditions. It is considered a best practice to define the optional [mpty] argument to provide a user-friendly message, which prevents the function from returning a #CALC! error when no records meet the specified criteria.

Quick reference

Syntax

=FILTER(array, include, [if_empty])

Inputs

Arguments

arrayThe range to filter
Example: A2:D100
includeA boolean array (TRUE/FALSE) to determine which rows to keep
Example: C2:C100="Active"
if_empty (optional)Value to return if no matches found
Example: "No Results"

Formula patterns

Examples

1

basic text filter

=FILTER(A2:D100, B2:B100="West")
basictext filterdynamic array
2

multiple criteria using and logic

=FILTER(A2:C50, (B2:B50="Active")*(C2:C50>500), "No matches found")
multiple criteriaand logicmath operators
3

filtering with or logic

=FILTER(A2:E20, (D2:D20="Electronics")+(D2:D20="Appliances"))
or logicarray additioncategory filter

Avoid these issues

Common Errors

1

#CALC!

Cause: This occurs when no records in the source range match the criteria provided in the 'include' argument and the [if_empty] argument is not defined.

Fix: Add a descriptive string to the third argument, such as =FILTER(A2:B10, C2:C10="None", "No Results").

2

#VALUE!

Cause: The range provided for the 'include' argument (the criteria) does not match the dimensions (number of rows or columns) of the source 'array'.

Fix: Ensure the criteria range has the exact same row count (or column count) as the primary data range, such as A2:A100 matching B2:B100.

3

#SPILL!

Cause: The range where FILTER intends to place its results is not empty; existing data or merged cells are blocking the spill range.

Fix: Clear all cells below and to the right of the formula cell to allow the dynamic array to expand.

Platform support

Compatibility

Excel 2021+Excel-first

Dynamic array function. Excel 2021+ and Microsoft 365

Source: Microsoft Support

Common questions

Frequently Asked Questions

Filters a range of data based on criteria you define.

array: The range to filter include: A boolean array (TRUE/FALSE) to determine which rows to keep mpty: Value to return if no matches found

#CALC!: Add a descriptive string to the third argument, such as =FILTER(A2:B10, C2:C10="None", "No Results"). #VALUE!: Ensure the criteria range has the exact same row count (or column count) as the primary data range, such as A2:A100 matching B2:B100. #SPILL!: Clear all cells below and to the right of the formula cell to allow the dynamic array to expand.