Skip to content

Excel function · Lookup & reference

Excel FILTER function

FILTER takes every row of a table that meets your condition and lists them, spilling down and across from the formula: one region's sales, the open invoices. It is available in Microsoft 365 and Excel 2021.

In Turkish Excel: FİLTRE

What it does

FILTER needs two things: the range to list and a condition that gives TRUE or FALSE for each row (B2:B7="West"). Rows where the condition is TRUE come back in order, and the result spills down and to the right of the single cell that holds the formula. When a value in the source changes, the list updates by itself.

Several conditions go in brackets and are combined: multiply them for "and" ((B2:B7="West")*(C2:C7>=1000)), add them for "or" ((B2:B7="West")+(B2:B7="South")). If no row qualifies you get #CALC!; the message in the third argument appears instead.

Wrap the result in SORT to sort it or in UNIQUE to remove duplicates, and use CHOOSECOLS to keep only some columns. If you need a single value, XLOOKUP fits better, and for a conditional total use SUMIFS. In Excel 2019 and earlier, the Filter button on the Data tab does the job by hand.

Syntax

English Excel=FILTER(array, include, [if_empty])
Turkish Excel=FİLTRE(dizi; ekle; [boşsa])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
arrayRequiredThe range to list, e.g. A2:C7. Leave out the header row; type the headers once above the result.
includeRequiredA condition with as many rows as array: B2:B7="West". Combine several with * (and) or + (or).
if_emptyOptionalThe value shown when no row meets the condition, e.g. "No records". If omitted, #CALC! is returned.

Examples

1List the sales of one region

The sales list is in A:C. The formula in G2 returns the rows where column B equals the region in E2. The spill area G2:I7 is left empty in the data, and the headers are typed once in G1:I1.

G2fx=FILTER(A2:C7,B2:B7=E2)
List the sales of one region: example table
ABCDEFGHI
1RepRegionAmountRegionRepRegionAmount
2EmmaWest1,250WestEmmaWest1,250
3LiamNorth830OliviaWest640
4OliviaWest640AvaWest2,100
5NoahSouth1,580
6AvaWest2,100
7MasonNorth1,190
  • G2 (the result spills into G2:I4)=FILTER(A2:C7,B2:B7=E2)

West appears in three rows; the result spills from G2 to I4, and the last row shows Ava with 2,100. Type North in E2 and the list refreshes with the North rows.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Rows that meet two conditions at once

We want rows where the region is West and the amount is at least 1,000. The two conditions go in separate brackets and are multiplied: the product is 1 where both are TRUE and 0 elsewhere.

E2fx=FILTER(A2:C7,(B2:B7="West")*(C2:C7>=1000),"No records")
Rows that meet two conditions at once: example table
ABCDEFG
1RepRegionAmountRepRegionAmount
2EmmaWest1,250EmmaWest1,250
3LiamNorth830AvaWest2,100
4OliviaWest640
5NoahSouth1,580
6AvaWest2,100
7MasonNorth1,190
  • E2 (the result spills into E2:G3)=FILTER(A2:C7,(B2:B7="West")*(C2:C7>=1000),"No records")

Olivia is in the West but below 1,000, so she is left out; the result has two rows: Emma and Ava.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

3An OR condition and a message when nothing matches

In E2 the conditions are added: reps in the South or with sales above 2,000 come back. In G2 there are no sales in the East, so the message in the third argument appears.

E2fx=FILTER(A2:A7,(B2:B7="South")+(C2:C7>2000),"No records")
An OR condition and a message when nothing matches: example table
ABCDEFG
1RepRegionAmountSouth or over 2,000East
2EmmaWest1,250NoahNo records
3LiamNorth830Ava
4OliviaWest640
5NoahSouth1,580
6AvaWest2,100
7MasonNorth1,190
  • E2 (the result spills into E2:E3)=FILTER(A2:A7,(B2:B7="South")+(C2:C7>2000),"No records")
  • G2=FILTER(A2:A7,B2:B7="East","No records")

Column E lists the reps who meet at least one condition: Noah and Ava. G2 shows No records instead of an error.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#CALC!

Cause: No row meets the condition and if_empty was left out.

Fix: Add a message as the third argument: ,"No records".

#SPILL!

Cause: A cell in the area the result spills into is not empty.

Fix: Clear the cells below and to the right of the formula; the list grows with the number of matching rows.

#VALUE!

Cause: The condition range is not the same size as array, e.g. A2:C7 and B2:B9.

Fix: Start and end both ranges on the same rows.

#NAME?

Cause: The file was opened in a version without FILTER (Excel 2019 or earlier).

Fix: In those versions use Data > Filter or Advanced Filter.

Good to know

  • Tie the condition to a cell (B2:B7=E2); change the region in E2 and the list changes with it.
  • To filter by a word that appears inside the text, use the condition ISNUMBER(SEARCH("screw",B2:B7)) (SEARCH).

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Put only the West region sales of 1,000 or more from the sales list into a separate table

Convert to table

Bring your sales list into the tool and type this. Nexell keeps the rows that meet the condition and first shows in the preview how many remain; once you approve, you download the result as .xlsx.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between the FILTER function and the Filter button?

The Filter button on the Data tab hides rows in the table itself and has to be reapplied after changes. The FILTER function brings the matching rows to another place and updates by itself as the source changes; the original table stays as it is.

How do I use several conditions in FILTER?

Put each condition in brackets. If all must hold, multiply them: =FILTER(A2:C7,(B2:B7="West")*(C2:C7>=1000)). If any one is enough, add them: =FILTER(A2:C7,(B2:B7="West")+(B2:B7="South")).

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free