Excel FILTER Function

last modified October 5, 2026

The FILTER function is a dynamic array function in Excel that extracts the rows or columns of a range that meet one or more conditions. It returns a new array that spills into the neighbouring cells, so the result resizes automatically as the source data or the criteria change. This tutorial covers the syntax, single and multiple criteria, the optional if_empty argument, horizontal filtering, and how to combine FILTER with functions such as SORT and UNIQUE.

FILTER Function Basics

The FILTER function tests every row, or every column, of a source array against a Boolean include array and keeps only the entries whose test is TRUE. The result is a spilled range that automatically adjusts to the number of matches.

Component	Description
Function Name	FILTER
Syntax	=FILTER(array, include, [if_empty])
Arguments	array: range or array to filter include: Boolean array that selects rows or columns if_empty: value returned when nothing matches (optional)
Return Value	A spilled array of the matching rows or columns
Availability	Excel for Microsoft 365, Excel 2021, Excel 2024, and Excel for the web

The table below describes each argument in more detail.

Argument	Meaning
array	The range or array to filter, such as A1:C10. It can also be an array constant.
include	A Boolean array (TRUE/FALSE, or 1/0) whose height matches the rows of array when filtering rows, or whose width matches the columns of array when filtering columns. It is usually written as a comparison such as B1:B10="Sales".
if_empty	Optional. The value to return when no entry passes the test. If it is omitted and nothing matches, Excel returns a #CALC! error.

FILTER is available only in Excel 2021 and later and in Microsoft 365. In Excel 2019 and earlier the function does not exist and produces a #NAME? error. Because the result is a dynamic array, it needs enough empty cells to spill into; otherwise Excel reports a #SPILL! error.

Single vs Multiple Criteria

A single criterion uses one Boolean comparison for include. For multiple criteria, build one Boolean array per condition and combine them with arithmetic operators, because Excel treats TRUE as 1 and FALSE as 0.

Logic	Operator	Example
Single criterion	comparison	B1:B3="Fruit"
AND (both true)	*	(B1:B3="Sales")*(C1:C3>5500)
OR (either true)	+	(B1:B3="Circle")+(C1:C3="Large")

Multiplying two Boolean arrays yields 1 only when both conditions are TRUE, which is exactly AND. Adding them yields a non-zero value when at least one condition is TRUE, which is OR. Wrap each comparison in parentheses so the operators apply to the whole array rather than to a single cell.

Basic FILTER Example

This example demonstrates the simplest use of the FILTER function with a basic condition to filter data.

A	B
Apple	Fruit
Carrot	Vegetable
Banana	Fruit
	=FILTER(A1:A3, B1:B3="Fruit")

The table shows a simple dataset with items and categories. The FILTER formula extracts only items marked as "Fruit" in column B.

=FILTER(A1:A3, B1:B3="Fruit")

This formula filters range A1:A3, returning only values where corresponding cells in B1:B3 equal "Fruit". The result spills vertically with "Apple" and "Banana". This demonstrates FILTER's basic functionality.

FILTER with Multiple Conditions

FILTER can handle multiple conditions using logical operators. This example shows how to combine criteria with AND logic.

A	B	C
John	Sales	5000
Sarah	Marketing	6000
Mike	Sales	7000
		=FILTER(A1:A3, (B1:B3="Sales")*(C1:C3>5500))

This table demonstrates filtering with two conditions: department is "Sales" AND sales amount exceeds 5500. The multiplication acts as an AND operator.

=FILTER(A1:A3, (B1:B3="Sales")*(C1:C3>5500))

The formula returns names from A1:A3 where department is "Sales" (B column) AND sales (C column) exceed 5500. Only "Mike" meets both criteria. The asterisk (*) combines conditions with AND logic.

FILTER with OR Logic

FILTER can implement OR logic by adding conditions together. This example shows how to filter for items meeting either of two criteria.

A	B	C
Red	Circle	Small
Blue	Square	Large
Green	Triangle	Medium
		=FILTER(A1:A3, (B1:B3="Circle")+(C1:C3="Large"))

The table shows shapes with color and size attributes. The FILTER formula returns colors of shapes that are either circles OR large.

=FILTER(A1:A3, (B1:B3="Circle")+(C1:C3="Large"))

This formula filters A1:A3 for items where shape is "Circle" (B column) OR size is "Large" (C column). The plus sign (+) combines conditions with OR logic. Results include "Red" and "Blue".

FILTER with if_empty Parameter

The optional if_empty parameter specifies what to return when no items match the criteria. This prevents errors in empty results.

A	B
North	100
South	200
East	150
	=FILTER(A1:A3, B1:B3>250, "No matches")

The table demonstrates the if_empty parameter in action. Since no values in B1:B3 exceed 250, the formula returns the specified message instead of an error.

=FILTER(A1:A3, B1:B3>250, "No matches")

This formula attempts to filter regions (A1:A3) where values (B1:B3) exceed 250. Since no matches exist, it returns "No matches" instead of a #CALC! error. Without the third argument, Excel would return #CALC! because it does not support empty arrays. If you would rather signal a missing result, pass NA() as the if_empty value and the formula returns #N/A instead. An empty string "" is the common choice when you simply want a blank cell.

FILTER with Multiple Columns

FILTER can return multiple columns from the source data. This example shows how to filter and display several related columns at once.

A	B	C
Product1	Electronics	50
Product2	Furniture	120
Product3	Electronics	75
		=FILTER(A1:C3, B1:B3="Electronics")

The table contains product data with name, category, and price. The FILTER formula returns all columns for electronics products, creating a multi-column result.

=FILTER(A1:C3, B1:B3="Electronics")

This formula filters the entire table (A1:C3) for rows where category (B1:B3) is "Electronics". The result spills both vertically and horizontally, showing Product1 and Product3 with their categories and prices. The include array is a single column, so its height matches the three rows of the source array even though the source is three columns wide.

FILTER with Dates

FILTER works well with date criteria. This example demonstrates filtering records based on date ranges.

A	B
Task1	1/15/2023
Task2	2/20/2023
Task3	3/10/2023
	=FILTER(A1:A3, (B1:B3>=DATE(2023,2,1))*(B1:B3<=DATE(2023,2,28)))

The table shows tasks with dates. The FILTER formula extracts tasks falling within February 2023, demonstrating date-based filtering.

=FILTER(A1:A3, (B1:B3>=DATE(2023,2,1))*(B1:B3<=DATE(2023,2,28)))

This formula filters tasks (A1:A3) for dates (B1:B3) in February 2023. The DATE function creates the range boundaries. Only "Task2" falls within this period. This technique is useful for time-based data analysis.

FILTER for Columns (Horizontal Filtering)

When the include array is horizontal, FILTER selects columns instead of rows. The include array must be a single row whose width matches the number of columns in the source array.

A	B	C
Q1	Q2	Q3
100	250	80
		=FILTER(A1:C2, A1:C1="Q2")

The table holds three quarter headers in row 1 and their values in row 2. The formula keeps only the column whose header is "Q2".

=FILTER(A1:C2, A1:C1="Q2")

This formula returns the two-row, one-column array Q2 and 250. A vertical comparison such as A1:A3 filters rows, while a horizontal comparison such as A1:C1 filters columns. Supplying an include array whose size matches neither the rows nor the columns of array produces a #VALUE! error.

FILTER with SORT

The result of FILTER is an array, so it can be passed directly to SORT to order the matching rows.

A	B	C
John	Sales	5000
Sarah	Marketing	6000
Mike	Sales	7000
Anna	Sales	5500
		=SORT(FILTER(A1:C4, B1:B4="Sales"), 3, -1)

The inner FILTER keeps the three Sales rows and the outer SORT orders them by the third column (salary) in descending order, because 3 is the sort index and -1 means descending.

=SORT(FILTER(A1:C4, B1:B4="Sales"), 3, -1)

The result is Mike (7000), Anna (5500), then John (5000). Use 1 instead of -1 for ascending order. When an empty result is possible, pass if_empty inside FILTER, for example =SORT(FILTER(A1:C4, B1:B4="Sales", ""), 3, -1), so the outer SORT never receives an empty array.

FILTER with UNIQUE

UNIQUE removes duplicates from the array produced by FILTER. This is useful for building a distinct list from rows that meet a condition.

A	B
East	100
West	200
East	300
North	50
West	400
	=UNIQUE(FILTER(A1:A5, B1:B5>100))

The inner FILTER returns the regions whose amount is greater than 100 (West, East, West) and UNIQUE reduces them to West and East.

=UNIQUE(FILTER(A1:A5, B1:B5>100))

UNIQUE keeps the first occurrence of each value, so the output follows the order in which the matching regions appear. Combine the two with SORT to get an alphabetical list: =SORT(UNIQUE(FILTER(A1:A5, B1:B5>100))).

FILTER Errors and Troubleshooting

Cause	Symptom	Fix
No rows match and if_empty is omitted	#CALC! error	Add an if_empty value such as "" or 0
include size does not match the array	#VALUE! error	Make the include array the same height (rows) or width (columns) as the array
include contains an error value	The same error is returned	Clean the source data or guard it with IFERROR
Spill range is blocked by other data	#SPILL! error	Clear the cells the result needs, or move the formula
Opened in Excel 2019 or earlier	#NAME? error	Use Excel 2021 or Microsoft 365, or an array-entered alternative
Source workbook is closed	#REF! error when refreshed	Keep both workbooks open for cross-workbook dynamic arrays

To return #N/A instead of #CALC! when nothing matches, pass NA() as the if_empty argument, for example =FILTER(A1:A3, B1:B3>250, NA()). To trap any FILTER error in a single cell, wrap the whole formula in IFERROR.

The FILTER function brings query-style extraction to Excel. From a single comparison to combined AND and OR conditions, and from vertical row filtering to horizontal column filtering, FILTER returns a live spilled array that updates the moment the data changes. Paired with SORT and UNIQUE, it replaces many older array and helper-column techniques. Remember that an empty result returns #CALC! unless you supply if_empty, that include must match the array's rows or columns exactly, and that the function requires a modern version of Excel with dynamic arrays. Mastering FILTER will make your spreadsheets shorter, clearer, and far more responsive.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.