FILTER function in Excel

FILTER returns the rows of a range that meet a condition, as a list that updates by itself.

FILTER does with a formula what the filter buttons do by hand, with one great difference: the result appears in other cells and updates by itself when the data changes. It is one of the dynamic array functions, available in Excel 2021 and Microsoft 365. Earlier versions do not have it.

Syntax

=FILTER(array, include, [if_empty])

Argument	What it is
`array`	The range to take rows from.
`include`	A condition on a range with the same number of rows, such as C2:C13="North".
`if_empty`	What to show when no row passes. Without it you get the error #CALC!.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Twelve sales made by four salespeople in three regions.

	A	B	C	D	E	F
1	Date	Salesperson	Region	Product	Units	Amount
2	05-Jan-2026	Asha	North	Notebook	20	1200
3	12-Jan-2026	Ravi	South	Pen	50	500
4	20-Jan-2026	Meena	North	Bag	4	3000
5	02-Feb-2026	Asha	East	Pen	30	300
6	14-Feb-2026	Kabir	South	Notebook	15	900
7	28-Feb-2026	Ravi	North	Bag	2	1500
8	03-Mar-2026	Meena	East	Notebook	40	2400
9	15-Mar-2026	Asha	North	Bag	3	2250
10	22-Mar-2026	Kabir	South	Pen	60	600
11	01-Apr-2026	Ravi	East	Notebook	25	1500
12	18-Apr-2026	Meena	North	Pen	80	800
13	06-May-2026	Asha	South	Bag	5	3750

=FILTER(B2:F13,C2:C13="East")
Result:
Asha East Pen 30 300
Meena East Notebook 40 2400
Ravi East Notebook 25 1500
Every row where the region is East. One formula returns a block of cells.
=FILTER(B2:B13,F2:F13>2000)
Result:
Meena
Meena
Asha
Asha
A single column back: the salespeople with a sale above 2000.
=FILTER(B2:F13,(C2:C13="North")*(D2:D13="Bag"))
Result:
Meena North Bag 4 3000
Ravi North Bag 2 1500
Asha North Bag 3 2250
Two conditions joined with a star mean AND: North and Bag.
=FILTER(D2:F13,(B2:B13="Kabir")+(B2:B13="Ravi"))
Result:
Pen 50 500
Notebook 15 900
Bag 2 1500
Pen 60 600
Notebook 25 1500
Joined with a plus they mean OR: sales by Kabir or by Ravi.
=FILTER(B2:F13,C2:C13="West","No sales")
Result: No sales
No row matches. The third argument supplies a message.
=SUM(FILTER(F2:F13,C2:C13="South"))
Result: 5750
FILTER inside another function: the total for the South.
=SORT(FILTER(B2:F13,F2:F13>=1500),5,-1)
Result:
Asha South Bag 5 3750
Meena North Bag 4 3000
Meena East Notebook 40 2400
Asha North Bag 3 2250
Ravi North Bag 2 1500
Ravi East Notebook 25 1500
Filter, then sort the result by its 5th column, largest first.
=ROWS(FILTER(B2:B13,D2:D13="Pen"))
Result: 4
Counting the rows that come back.

How it works

Give FILTER a range and a condition. It checks the condition for every row and returns the rows that pass. The formula is typed in one cell, and the answers spill into as many cells as are needed, below and to the right.

The condition must cover the same number of rows as the range. If the range is B2:F13, the condition must be on rows 2 to 13 as well, or the result is #VALUE!.

Spilling

The block of results belongs to the single formula in its top-left cell. You cannot edit the other cells of the block. If something already occupies a cell where the results need to go, the formula shows #SPILL! until you clear the way.

To refer to the whole result, however large it becomes, put a hash sign after the first cell: if the formula is in H2, H2# means "everything H2 spilled". =SUM(H2#) or =COUNTA(H2#) then stays correct as the list grows and shrinks.

More than one condition

Put each condition in its own brackets.

AND: multiply them. (C2:C13="North")*(D2:D13="Bag")
OR: add them. (B2:B13="Kabir")+(B2:B13="Ravi")

The words AND and OR cannot be used here, because they collapse a whole column into a single TRUE or FALSE.

Conditions can be anything that gives TRUE or FALSE for each row: comparisons with dates, ISNUMBER(SEARCH("pen",D2:D13)) for "contains", or MONTH(A2:A13)=3 for a month.

When nothing matches

With no third argument, an empty result is the error #CALC!. Always give a third argument, such as "None" or "", in any sheet that other people will read.

FILTER as a lookup that returns everything

VLOOKUP and XLOOKUP return the first match. FILTER returns all of them. "Show every order of this customer" is =FILTER(orders, customer_column=H1, "None"), with the customer's name typed in H1. Change H1 and the list changes.

Choosing columns

To return only some columns, filter a narrower range, or wrap the result in CHOOSECOLS in Microsoft 365. In Excel 2021, a second FILTER can pick columns: =FILTER(FILTER(A2:F13,C2:C13="East"),{1,0,0,1,0,1}) keeps the first, fourth and sixth.

Working with other functions

FILTER's result can feed anything.

SORT to put it in order.
UNIQUE to remove repeats.
SUM, AVERAGE, MAX or COUNT for a figure about the matching rows.
TEXTJOIN to put the matches in one cell.

Common mistakes

#SPILL!: something is in the way of the results.
#CALC!: nothing matched and there is no third argument.
#VALUE!: the condition's range is a different height from the data.
#NAME?: the Excel version is older than 2021.
Headings are not included in the result. Type them above the formula.

In Google Sheets

Google Sheets has FILTER too, with a different shape: =FILTER(range, condition1, condition2, ...). Extra conditions are simply listed, and there is no "if empty" argument, so IFERROR is used around it.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.