FILTER Function in Excel: Ultimate Guide

FILTER Function in Excel: Ultimate Guide
IL Inar Learn
FILTER Function in Excel — Ultimate Guide Saved
18 min read Excel 2021 · Microsoft 365 · Excel for the web Updated for 2026

FILTER is the function that made dynamic arrays worth the upgrade. It takes a table, tests every row against a condition, and spills the matching rows into the cells below — all from a single formula that updates itself the moment the data changes.

This guide covers the syntax, spilling behaviour, multiple conditions with AND and OR, text and date filtering, FILTER combined with SORT and UNIQUE, the #SPILL! errors, and the ways FILTER replaces older tools like Advanced Filter, helper columns, and PivotTable refreshes for interactive reports.

In one sentence

FILTER extracts the rows from a range that meet a condition and returns them as a spilled array, written as =FILTER(array, include, [if_empty]).

01

What Is the FILTER Function?

FILTER is a dynamic array function. It takes a source range, evaluates a logical test against each row, and returns every row where the test is TRUE. The result spills into the cells below and beside the formula, and it grows or shrinks automatically as the underlying data changes.

Before FILTER, you needed helper columns, Advanced Filter, or a PivotTable to answer "show me all the rows where X is true". FILTER does it in one formula, live, with no refresh button.

FunctionFILTER
CategoryLookup & Reference
Arguments2–3
ReturnsSpilled array
SinceExcel 2021 / M365

How FILTER evaluates a formula

Take the formula =FILTER(A2:C100, B2:B100="East", "No matches"). Excel does four things, in order:

  1. Build a TRUE/FALSE array. For every row in B2:B100, it asks: is this cell "East"? The result is a column of TRUE and FALSE values, one per row.
  2. Keep the TRUE rows. Excel walks through the source range and collects every row whose test returned TRUE.
  3. Spill the result. The collected rows appear in the cells starting at the formula and extending down and to the right. The blue outline around the result is Excel telling you "this is a spilled range, don't type inside it".
  4. Fall back to if_empty. If nothing matched, the third argument is shown instead of #CALC!.

What the TRUE/FALSE test looks like

The include test, one row at a timeFilter.xlsx
A
Region
B
Include?
2EastTRUE
3WestFALSE
4EastTRUE
5NorthFALSE
6EastTRUE
The test: A2:A6="East" → {TRUE;FALSE;TRUE;FALSE;TRUE}. Rows 2, 4, and 6 are kept; rows 3 and 5 are dropped.
i
FILTER needs Excel 2021 or Microsoft 365

It is not available in Excel 2019 or earlier. On the web, Excel for the web supports it. Excel for iPad, iPhone, and Android also support it. If you open a FILTER workbook in Excel 2019, the formula shows as _xlfn._xlws.FILTER or a #NAME? error.

✓
FILTER returns rows, not a single value

If you only need one number, use SUMIFS, COUNTIFS, or XLOOKUP. FILTER is for "give me the whole matching list".

02

Syntax and Arguments

The full signature has three arguments, and the last one is optional but strongly recommended.

Formula syntax
=FILTER(array, include, [if_empty])

Walking through each argument

  1. array — the range you want to filter. Can be one column, several columns, or a whole table. This is the data Excel will return.
  2. include — a TRUE/FALSE array with the same number of rows as array. Usually written as a comparison: B2:B100="East".
  3. if_empty (optional but recommended) — what to return when no rows match. Without it, FILTER returns #CALC!.
Argument reference
ArgumentRequired?Notes
arrayRequiredCan be one column or many. Must have the same number of rows as the include test.
includeRequiredA TRUE/FALSE array. Usually a comparison against a column in the array.
if_emptyOptionalIf omitted, FILTER returns #CALC! when nothing matches. Supply "" or a message.

What happens when the arguments line up vs. when they do not

Row counts must matchFilter.xlsx
A
array rows
B
include rows
C
Result
22–100 (99 rows)2–100 (99 rows)Correct result
32–100 (99 rows)2–50 (49 rows)#VALUE!
Both ranges must span the same rows. Tables solve this automatically because structured references grow and shrink as one unit.
!
Row counts must match

The include array must have exactly the same number of rows as array. If array is A2:C100, the test must be B2:B100="East", not B2:B50="East". Mismatch returns #VALUE!.

✓
Always supply if_empty

A blank-looking result is almost always better than a red #CALC! in a report. Use "" for a blank cell, or a message like "No rows match".

03

Dynamic Arrays and Spilling Explained

The first thing that surprises new FILTER users is that the result appears in more cells than the formula was entered in. That is spilling, and it is the feature that makes FILTER work.

What spilling looks like

Source data (A:C) and spilled result (E:G)Filter.xlsx
A
Region
B
Rep
C
Amount
E
Spilled result
FG
2EastAna1200EastAna1200
3WestBen900EastCarla1500
4EastCarla1500EastAna1100
5NorthBen700
6EastAna1100
Formula in E2: =FILTER(A2:C6, A2:A6="East", "None")
Rows 2, 4, and 6 of the source appear in E2:G4 with all three columns intact. The range is outlined in blue — that is the spilled range.

Rules of spilled ranges

01

Only the top-left cell holds the formula

Click any other cell in the spill and the formula bar shows a greyed-out version. You cannot edit those cells individually.

02

The range grows and shrinks automatically

Add a new matching row to the source and the spill expands down by one row. No re-running, no refresh.

03

Delete the top-left cell to delete the result

Pressing Delete on any other cell in the spilled range does nothing. The whole result is bound to the origin cell.

04

You cannot type inside a spilled range

Excel blocks typing and shows a #SPILL! warning, because the formula owns those cells.

What happens when the spill area is blocked

The #SPILL! warningFilter.xlsx
E
Formula cell
F
Row 3
G
Row 4
2#SPILL!Existing dataExisting data
3——
Cause: F2 or G2 already contains a value. Excel cannot expand into those cells. Clear them and the FILTER result appears.
✓
Reference a spilled range with the # operator

If the FILTER formula is in E2, you can reference its entire result with E2#. That grows automatically as the spill grows. It is the cleanest way to feed a FILTER result into another formula, chart, or PivotTable.

04

Multiple Conditions — AND and OR

FILTER takes a single include argument, but you can combine as many conditions as you like inside it. The trick is that FILTER uses arithmetic, not the AND and OR functions.

The sample data these examples use

Orders — the source for every AND/OR exampleOrders.xlsx
A
Region
B
Rep
C
Amount
D
Status
2EastAna1200Open
3WestBen900Open
4EastCarla1500Pending
5NorthBen700Open
6EastAna800Closed
7WestCarla2100Open
Each condition in the examples below operates on one of these columns.

AND vs OR — side by side

AND — multiply the conditions
=FILTER(A2:D7, (A2:A7="East") * (C2:C7>1000), "No matches")
Returns rows 2 and 4 (East with amount > 1000).
OR — add the conditions
=FILTER(A2:D7, (A2:A7="East") + (A2:A7="West"), "No matches")
Returns rows 2, 3, 4, 6, and 7 (East or West).

Result of the AND filter

Rows where Region = East AND Amount > 1000Orders.xlsx
A
Region
B
Rep
C
Amount
D
Status
2EastAna1200Open
4EastCarla1500Pending
Row 6 (East, 800) is excluded — it fails the amount test. Rows 3, 5, and 7 fail the region test.

Result of the OR filter

Rows where Region = East OR Region = WestOrders.xlsx
A
Region
B
Rep
C
Amount
D
Status
2EastAna1200Open
3WestBen900Open
4EastCarla1500Pending
6EastAna800Closed
7WestCarla2100Open
Row 5 (North) is the only row excluded.

AND with three or more conditions

Chained AND
' East AND over 1000 AND open
=FILTER(A2:D7,
        (A2:A7="East") * (C2:C7>1000) * (D2:D7="Open"),
        "No matches")
East AND amount > 1000 AND status = OpenOrders.xlsx
A
Region
B
Rep
C
Amount
D
Status
2EastAna1200Open
Row 4 (East, 1500, Pending) is excluded by the status test.

Combining AND and OR — (A OR B) AND C

(East OR West) AND amount over 1000
=FILTER(A2:D7,
        ((A2:A7="East") + (A2:A7="West")) * (C2:C7>1000),
        "No matches")
(East OR West) AND amount > 1000Orders.xlsx
A
Region
B
Rep
C
Amount
D
Status
2EastAna1200Open
4EastCarla1500Pending
7WestCarla2100Open
Rows 3 (West, 900) and 6 (East, 800) fail the amount test. Row 5 (North) fails the region test.
✕
Do not use AND() or OR() inside FILTER

The AND and OR functions collapse a whole array down to a single TRUE/FALSE value, which FILTER cannot use. You must use * for AND and + for OR inside FILTER's include argument.

Exclusion — the "not" case

Excluding values
' Everything except East
=FILTER(A2:D7, A2:A7<>"East", "No matches")

' Not blank
=FILTER(A2:D7, A2:A7<>"", "No matches")

' Exclude a list of values
=FILTER(A2:D7,
        (A2:A7<>"East") * (A2:A7<>"West"),
        "No matches")
Rows where Region is not East and not WestOrders.xlsx
A
Region
B
Rep
C
Amount
D
Status
5NorthBen700Open
Only North survives. Both East and West are excluded.
05

Real-World Examples

These patterns cover most day-to-day FILTER use. Each one shows the data, the formula, and what the spilled result looks like.

Example 1 — Show all rows for one region

The simplest FILTER: one text condition, all columns returned.

Sales by RegionSales.xlsx
A
Region
B
Rep
C
Sales
E
Result
FG
2EastAna1200EastAna1200
3WestBen900EastCarla1500
4EastCarla1500EastAna1100
5NorthBen700
6EastAna1100
Formula in E2: =FILTER(A2:C6, A2:A6="East", "None")
Three East rows spill into E2:G4. The West and North rows are excluded.

Example 2 — Over a threshold

One numeric condition, all columns returned. Useful for "top of the pile" reviews.

Invoices — filter for amount over 1000Invoices.xlsx
A
Invoice
B
Customer
C
Amount
E
Result
FG
2INV-1042Acme1250INV-1042Acme1250
3INV-1039Bravo820INV-1038Delta3400
4INV-1038Delta3400INV-1028Acme5600
5INV-1035Bravo450
6INV-1028Acme5600
Formula in E2: =FILTER(A2:C6, C2:C6>1000, "None")
Rows 2, 4, and 6 are returned. Rows 3 and 5 fall below the threshold.
Threshold filter formulas
' Every sale over $1000
=FILTER(A2:C100, C2:C100>1000, "No sales above 1000")

' Every sale between $500 and $2000 (AND)
=FILTER(A2:C100,
        (C2:C100>=500) * (C2:C100<=2000),
        "None in range")

Example 3 — Two columns, specific result

When you only want to return part of the source, hand FILTER a narrower array. Choose columns that are not adjacent by using CHOOSE.

Return Rep and Sales only — skip RegionSales.xlsx
A
Region
B
Rep
C
Sales
E
Rep only
F
Sales only
2EastAna1200Ana1200
3WestBen900Carla1500
4EastCarla1500Ana1100
5NorthBen700
6EastAna1100
Formula in E2: =FILTER(CHOOSE({1,2}, B2:B6, C2:C6), A2:A6="East", "None")
The Region column is skipped entirely — only Rep and Sales appear in the spill.
Narrowing the output
' Return only the Rep column for East
=FILTER(B2:B100, A2:A100="East", "None")

' Return Rep and Sales (skip Region) with CHOOSE
=FILTER(CHOOSE({1,2}, B2:B100, C2:C100),
        A2:A100="East",
        "None")

' Return Sales, Rep order instead
=FILTER(CHOOSE({1,2}, C2:C100, B2:B100),
        A2:A100="East",
        "None")

Example 4 — Dates in a range

Date filtering is two AND conditions on the same column — one for the start, one for the end.

Q1 2026 — Jan 1 to Mar 31Dates.xlsx
A
Date
B
Region
C
Amount
E
Result
FG
22026-01-05East12002026-01-05East1200
32026-02-18West9002026-02-18West900
42026-04-02East15002026-03-22East1100
52026-03-22East1100
62026-07-19North700
Formula in E2: =FILTER(A2:C6, (A2:A6>=DATE(2026,1,1))*(A2:A6<=DATE(2026,3,31)), "Nothing in Q1")
Rows from April and July are excluded.
Date ranges
' Q1 2026 only
=FILTER(A2:D100,
        (A2:A100>=DATE(2026,1,1)) * (A2:A100<=DATE(2026,3,31)),
        "Nothing in Q1")

' Year-to-date — from Jan 1 to today
=FILTER(A2:D100,
        (A2:A100>=DATE(YEAR(TODAY()),1,1)) * (A2:A100<=TODAY()),
        "No YTD data")

' Overdue invoices
=FILTER(A2:D100, A2:A100
✕
Text dates silently break FILTER

If the dates are stored as text (left-aligned in the cell, no formatting), the comparison against DATE(...) will never match and the filter returns the if_empty message. Verify with =ISNUMBER(A2).

Example 5 — Text match with wildcards

FILTER does not support wildcards inside =. Use ISNUMBER(SEARCH(...)) for contains, or LEFT/RIGHT for starts-with and ends-with.

Contains "North" anywhereNames.xlsx
A
Account
B
Contact
C
Amount
E
Result
FG
2NorthwindAna1200NorthwindAna1200
3SouthgateBen900NorthlineCarla1500
4NorthlineCarla1500NorthernAna1100
5EastgateBen700
6NorthernAna1100
Formula in E2: =FILTER(A2:C6, ISNUMBER(SEARCH("North", A2:A6)), "None")
Accounts that contain the letters "North" anywhere.
Text patterns
' Contains "North" anywhere (case-insensitive)
=FILTER(A2:C100,
        ISNUMBER(SEARCH("North", A2:A100)),
        "None")

' Starts with "North"
=FILTER(A2:C100, LEFT(A2:A100,5)="North", "None")

' Ends with "Ltd"
=FILTER(A2:C100, RIGHT(A2:A100,3)="Ltd", "None")

' One of three exact values (OR)
=FILTER(A2:C100,
        (A2:A100="North") + (A2:A100="South") + (A2:A100="East"),
        "None")
06

FILTER with SORT, SORTBY, and UNIQUE

FILTER returns rows in the order they appear in the source. That is rarely what a report needs. SORT, SORTBY, and UNIQUE are the three functions you wrap around FILTER most often.

Before and after SORT

Raw FILTER vs SORT(FILTER(...), 3, -1)Sort.xlsx
A
Region
B
Rep
C
Sales
E
Unsorted
FGI
Sorted desc
JK
2EastAna1200EastAna1200EastCarla1500
3WestBen900EastCarla1500EastAna1200
4EastCarla1500EastAna1100EastAna1100
5NorthBen700
6EastAna1100
Left (E:G): raw FILTER output in source order. Right (I:K): the same rows sorted by Sales descending.

SORT vs SORTBY — when each one wins

SORT — when the sort column is in the result
=SORT( FILTER(A2:C100, A2:A100="East"), 3, -1)
Sorts by the third column of the filtered result, descending. Simple and fast.
SORTBY — when the sort column is hidden
=SORTBY( FILTER(CHOOSE({1,2}, B2:B100, C2:C100), A2:A100="East"), FILTER(A2:A100, A2:A100="East"), 1)
Sorts by a column that is not part of the returned array. More verbose but more flexible.

UNIQUE — remove duplicates from a FILTER result

UNIQUE(FILTER(Rep, Region=East))Unique.xlsx
A
Region
B
Rep
E
Filtered
G
Unique
2EastAnaAnaAna
3WestBenCarlaCarla
4EastCarlaAna
5NorthBen
6EastAna
Formula in G2: =UNIQUE(FILTER(B2:B6, A2:A6="East"))
Ana appears twice in the filtered list, once in the unique list.

Counting and summing a FILTER result

Wrapping FILTER in ROWS, SUM, AVERAGE, MAXStats.xlsx
A
Statistic
B
Formula
C
Result
2Count=ROWS(FILTER(A2:C100,A2:A100="East"))3
3Total=SUM(FILTER(C2:C100,A2:A100="East"))3800
4Average=AVERAGE(FILTER(C2:C100,A2:A100="East"))1266.67
5Maximum=MAX(FILTER(C2:C100,A2:A100="East"))1500
The same FILTER can drive multiple statistics. When the source changes, every number updates.
✓
SUM(FILTER(...)) replaces SUMIFS in M365

The two return the same total. SUMIFS is faster on very large ranges and works in older Excel. SUM(FILTER(...)) is easier to read when the condition is complex, and the same FILTER can be reused for COUNT, AVERAGE, MAX, and MIN.

07

FILTER vs Advanced Filter, PivotTable, and XLOOKUP

FILTER is not the only way to extract rows in Excel. It is the cleanest way in most cases, but each alternative has a place where it wins.

Which tool to reach for
ToolBest forWatch out for
FILTERA live, spilled list of matching rowsExcel 2021 / M365 only. Result cells are locked to the formula.
Advanced FilterOne-off extraction to a fixed rangeNot live. Needs a manual re-run when the data changes.
PivotTableInteractive slicing of many dimensions at onceNeeds a refresh. Returns aggregates, not the underlying rows.
XLOOKUPFinding one value for one keyReturns a single match, not a whole filtered list.
SUMIFSA single number from many conditionsAggregates only. No rows returned.
AutoFilterHiding rows in place for inspectionModifies the sheet visually. Not a formula.

The source data all three tools would read

Sales — show every East sale over $1000Compare.xlsx
A
Region
B
Rep
C
Sales
2EastAna1200
3WestBen900
4EastCarla1500
5NorthBen700
6EastAna1100
Expected output: rows 2, 4, and 6 — every East sale above $1000.

The same question, three tools

FILTER

One formula, live, spills rows

=FILTER(A2:C6, (A2:A6="East") * (C2:C6>1000), "None")

ADV

Advanced Filter

Criteria in A1:C2 (East, >1000), source A1:C6, output E1. Re-run manually when data changes.

PIVOT

PivotTable

Region as a filter set to East, Sales as Values, then Value Filter > 1000.

i
FILTER and PivotTable complement each other

Use FILTER for a clean list you want to see or export. Use a PivotTable when you need to slice by many dimensions quickly. Many real dashboards use both — a PivotTable for summary numbers, a FILTER for the detail behind them.

08

Build a Dynamic Report with FILTER

This is the pattern that replaces most "helper column + AutoFilter" workflows. A drop-down in one cell, a FILTER below it, and the report updates as the user chooses.

Step 1 — The source data

Convert the source to a Table with Ctrl+T so it grows automatically. In this example the Table is named Sales.

Source Table — SalesReport.xlsx
A
Date
B
Region
C
Rep
D
Amount
22026-01-05EastAna1200
32026-01-18WestBen900
42026-02-02EastCarla1500
52026-02-19EastAna800
62026-03-08NorthBen700
72026-03-22EastCarla1100
Rename the Table to Sales from the Table Design tab. All formulas below use structured references.

Step 2 — The interactive controls

  1. Region drop-down (cell H1). Data → Data Validation → List → Source: East,West,North,South.
  2. Minimum amount (cell H2). Type a starting number, e.g. 0.
  3. Section heading (row 3). Add labels in E4:G4 for the columns you want to see.

Step 3 — The FILTER formula

One formula in cell E5 does all the work. The whole report appears below it.

The dashboard formula
' In cell E5 — filter rows by region and minimum amount
=FILTER(
  Sales[[Date]:[Amount]],
  (Sales[Region]=H1) * (Sales[Amount]>=H2),
  "No rows match your filters")

What the user sees when they choose East and 1000

Live dashboard — H1 = East, H2 = 1000Report.xlsx
E
Date
F
Region
G
Amount
52026-01-05East1200
62026-02-02East1500
72026-03-22East1100
8
9Count:3
10Total:3800
Change H1 to West or H2 to 1500 and the spill and the summary both update. No refresh needed.

Step 4 — Summary numbers below

Because FILTER spills, the summary cells can reference the spilled range directly with the # operator.

Summary row referencing E5#
' Number of matching rows
=ROWS(E5#)

' Total of the matching rows' Amount column
=SUM(INDEX(E5#, , 3))

' Average amount
=AVERAGE(INDEX(E5#, , 3))

' Highest single amount
=MAX(INDEX(E5#, , 3))
✓
Wrap in SORT for a nicer report

Wrap the FILTER in SORT(..., 1, -1) to show the newest or highest rows first. The spilled range still grows and shrinks correctly, and E5# still references the whole thing.

!
Leave room for the spill

If the FILTER returns 50 rows and you only left 30 rows of blank space below, the range hits your summary cells and returns #SPILL!. Leave a large buffer, or place the summary on a separate sheet.

09

Common Errors and How to Fix Them

Most FILTER problems fall into a handful of patterns. Each one below has a known cause and a known fix.

What the errors look like

Every common FILTER error at a glanceErrors.xlsx
A
Error
B
Cause
C
Fix
2#SPILL!Spill area is blockedClear the cells below/beside
3#CALC!No matches, no if_emptyAdd a third argument
4#VALUE!Row counts do not matchAlign both ranges
5#NAME?Excel 2019 or earlierUpgrade or use a fallback
6EmptyData types do not matchClean with TRIM and VALUE
Each row is a distinct error signature. Match your symptom against column A to find the fix in column C.
✕
#SPILL! — something is blocking the result

This is the most common FILTER error. The formula wants to write into cells that already contain data, or it hits a merged cell, or it goes off the edge of the sheet.

Fix: click the warning icon to see which cells are in the way, then clear them. Also check for merged cells in the spill area — they block spills even when visually empty.

✕
#CALC! — no matches, no if_empty

The filter returned zero rows and you did not supply an if_empty argument. #CALC! is Excel's way of saying "the result would be empty".

Fix: add a third argument: =FILTER(A2:C100, B2:B100="East", "No matches").

✕
#VALUE! — array sizes do not match

The array and include arguments have different row counts. For example =FILTER(A2:C100, B2:B50="East").

Fix: make sure the two ranges span the same rows. If the source is a Table, structured references handle this automatically.

!
#NAME? — Excel does not recognise FILTER

You are using Excel 2019 or earlier, or you opened the file in a version that predates dynamic arrays. FILTER requires Excel 2021 or Microsoft 365.

Fix: upgrade Excel, or use a PivotTable, Advanced Filter, or helper-column approach instead.

!
Result is empty but there should be matches

Almost always a data-type mismatch. Numbers stored as text, dates stored as text, or trailing spaces in text values. The test finds no TRUE rows because nothing actually matches.

Fix: clean the source with TRIM and VALUE, or verify with =ISNUMBER() and =ISTEXT().

!
Cannot edit cells inside the spilled range

That is by design. Excel shows a greyed-out formula in the formula bar for any cell except the top-left of the spill.

Fix: edit the formula in the top-left cell. To delete the whole result, select the top-left cell and press Delete.

What correct vs wrong logic looks like

Right vs wrong in FILTERErrors.xlsx
A
Goal
B
Wrong
C
Right
2ANDAND(B2="East", C2>1000)(B2="East") * (C2>1000)
3OROR(B2="East", B2="West")(B2="East") + (B2="West")
4No matches(blank 3rd argument)"No matches"
Every row is a real mistake people make. Column B breaks FILTER; column C is the working alternative.
Common fixes at a glance
' #CALC! — add if_empty
=FILTER(A2:C100, B2:B100="East", "No matches")

' Wrong: AND() collapses the array
=FILTER(A2:C100, AND(B2:B100="East", C2:C100>1000))

' Right: multiply for AND
=FILTER(A2:C100, (B2:B100="East") * (C2:C100>1000))

' Wrong: OR() collapses the array
=FILTER(A2:C100, OR(B2:B100="East", B2:B100="West"))

' Right: add for OR
=FILTER(A2:C100, (B2:B100="East") + (B2:B100="West"))
10

Best Practices for FILTER

These habits keep dynamic reports reliable as the source data grows.

01

Always supply if_empty

A blank result beats a red #CALC!. Use "" or a short message.

02

Use Tables as the source

Structured references grow automatically and prevent row-count mismatches.

03

Put criteria in cells

A criterion buried in a formula is invisible. Reference a cell instead.

04

Use * for AND, + for OR

Do not use AND() or OR(). They collapse the array and break the filter.

05

Leave room for the spill

Blank rows below and columns to the right. Otherwise #SPILL!.

06

Reference the spill with #

If the formula is in E5, E5# refers to the whole result and grows automatically.

07

Wrap in SORT for reports

Raw FILTER output is in source order. SORT puts the most important rows first.

08

Check data types first

Text numbers and text dates cause FILTER to silently return nothing.

09

Avoid whole-column arrays

A:A forces FILTER to test a million rows. Use bounded ranges or Tables.

10

Document Excel version

FILTER is 2021+ only. If sharing with older Excel users, plan a fallback.

11

Practice Questions

Try these before opening the answers. They use the examples from earlier in the guide.

  1. Q1. What is the syntax of FILTER?
    Answer: =FILTER(array, include, [if_empty]).
  2. Q2. Write a FILTER that returns all rows where column A is "East".
    Answer: =FILTER(A2:C100, A2:A100="East", "None").
  3. Q3. How do you combine two AND conditions in FILTER?
    Answer: Multiply them: =FILTER(A2:C100, (A2:A100="East") * (C2:C100>1000), "None").
  4. Q4. How do you combine two OR conditions?
    Answer: Add them: =FILTER(A2:C100, (A2:A100="East") + (A2:A100="West"), "None").
  5. Q5. What does #SPILL! mean and how do you fix it?
    Answer: The spilled range is blocked. Clear the cells in the way, or move the formula so it has space to expand.
  6. Q6. What happens if FILTER finds no matches and no if_empty is supplied?
    Answer: Excel returns #CALC!.
  7. Q7. How do you sort a FILTER result by its third column descending?
    Answer: Wrap it in SORT with a -1 order: =SORT(FILTER(A2:C100, A2:A100="East"), 3, -1).
  8. Q8. What does the # operator do after a cell reference?
    Answer: It refers to the entire spilled range starting at that cell. If FILTER is in E5, E5# is the whole result.
12

Frequently Asked Questions

What is the FILTER function in Excel?

FILTER extracts the rows from a range that meet a condition and returns them as a spilled array. Its syntax is =FILTER(array, include, [if_empty]).

Which Excel versions have FILTER?

FILTER is available in Excel 2021, Excel for Microsoft 365, Excel for the web, Excel for iPad and iPhone, and Excel for Android. It is not available in Excel 2019 or earlier.

What does #SPILL! mean?

#SPILL! means something is blocking the range where the FILTER result needs to expand. Clear the cells below or beside the formula, and the result appears.

How do I combine multiple conditions in FILTER?

Multiply conditions for AND: (A2:A100="East")*(B2:B100>1000). Add conditions for OR: (A2:A100="East")+(A2:A100="West").

Can FILTER return just one column?

Yes. Set the array argument to a single column: =FILTER(C2:C100, A2:A100="East").

How do I sort a FILTER result?

Wrap FILTER inside SORT: =SORT(FILTER(A2:C100, B2:B100>1000), 1, -1).

Can FILTER return unique values?

Wrap it in UNIQUE: =UNIQUE(FILTER(A2:A100, B2:B100="East")).

What happens when FILTER finds no matches?

Without an if_empty argument, FILTER returns #CALC!. Supply a third argument, e.g. =FILTER(A2:C100, B2:B100>99999, "No matches").

Does FILTER work with dates?

Yes. Use real date values: =FILTER(A2:C100, (A2:A100>=DATE(2026,1,1))*(A2:A100<=DATE(2026,12,31))).

Can FILTER reference another sheet?

Yes. Prefix each range with the sheet name: =FILTER(Jan!A2:C100, Jan!B2:B100="East").

Why does FILTER return #VALUE!?

The array and include arguments have different row counts. Make sure both span the same rows.

Can I edit individual cells in a FILTER result?

No. A spilled range is owned by the top-left formula. To change a value, edit the source data or the FILTER formula itself.

13

Key Takeaways

  • FILTER extracts the rows that match a condition and spills them into the cells below.
  • The syntax is =FILTER(array, include, [if_empty]).
  • FILTER requires Excel 2021 or Microsoft 365.
  • Always supply if_empty — otherwise you get #CALC!.
  • The array and include must have the same number of rows.
  • Use * for AND, + for OR. Do not use AND() or OR().
  • Spilled ranges cannot be edited cell by cell — only the top-left cell holds the formula.
  • Reference a spilled range with the # operator: E5#.
  • Wrap in SORT for reports, SORTBY to sort by a hidden column, UNIQUE for distinct values.
  • SUM(FILTER(...)) replaces SUMIFS when you want the whole list, not just the number.
  • #SPILL! means something is blocking the result — clear the cells in the way.
  • Use real dates and clean text — text numbers and text dates cause silent empty results.
  • Tables make FILTER formulas bulletproof: ranges grow automatically.
  • Leave blank space below the formula so the spill has room to grow.
14

What to Learn Next

FILTER is the gateway to Excel's dynamic array family. Once it is comfortable, the natural next steps are the functions it works with most — the ones that sort, deduplicate, and reshape its output.

Start with the dynamic array functions that pair with FILTER — sort-function, unique-function, and sortby-function.

From there, move into the modern lookup and reshaping tools: xlookup-function, sequence-function, take-function, and drop-function.

Inar Learn
Inar Learnhttps://inarlearn.com
Inar Learn is an innovative online learning platform offering high-quality courses, tutorials, and resources to help learners gain practical skills and grow their knowledge.

Related Articles

LEAVE A REPLY

Please enter your comment!
Please enter your name here