Cellwise Try it free

Dynamic arrays function

Excel FILTER function

FILTER returns only the rows of a range that meet a condition, and it updates as the data changes. Use it to pull open orders, one region's sales, or anything over a target into its own live list.

=FILTER(array, include, [if_empty])

Arguments

array
The range to return rows from.
include
A TRUE/FALSE test the same height as the range, like B2:B7="Shipped".
if_empty
Optional. What to show when nothing matches.

FILTER examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCD
1OrderStatusRepTotal
2SO-301ShippedAna420
3SO-302PendingBen180
4SO-303ShippedCy95
5SO-304PendingAna610
6SO-305ShippedAna240
7SO-306CancelledBen75
  1. =FILTER(A2:D7,B2:B7="Pending")

    Result: spills 2 rows × 4 columns

    SO-302PendingBen180
    SO-304PendingAna610

    Spills every pending order with all four columns.

  2. =FILTER(A2:A7,(C2:C7="Ana")*(D2:D7>200))

    Result: spills 3 rows × 1 column

    SO-301
    SO-304
    SO-305

    Multiply tests for AND: Ana's orders over 200.

  3. =SUM(FILTER(D2:D7,B2:B7="Shipped"))

    Result: 755

    Feed the result into SUM to total shipped orders.

  4. =FILTER(A2:A7,B2:B7="Returned","None")

    Result: None

    No order is Returned, so the if_empty text appears.

  5. =FILTER(A2:A7,B2:B7="Returned")

    Result: #CALC!

    Without if_empty, no matches gives #CALC!.

How to use FILTER

  1. Click the top-left cell of an empty area.
  2. Type =FILTER( and select the data rows.
  3. Type a comma and a test on one column, like B2:B7="Pending".
  4. Add a message for no matches, then press Enter.

Common FILTER mistakes

No matches gives #CALC!

Add a third argument, like "None", so an empty result shows a message.

Using AND() or OR() inside FILTER

They collapse to one TRUE or FALSE. Multiply tests for AND and add them for OR.

Ranges of different heights

The include test must have the same number of rows as the array, or you get #VALUE!.

FILTER FAQ

How do I filter data with a formula in Excel?

Use =FILTER(A2:D50,C2:C50="West","None"). It returns every matching row and updates when the data changes.

How do I use FILTER with multiple conditions?

Multiply the tests for AND, like (B2:B9="Shipped")*(D2:D9>100). Add them for OR, like (C2:C9="Ana")+(C2:C9="Ben").

Why does FILTER return #CALC!?

Nothing matched and there was no if_empty argument. Add a third argument such as "No results".

Practice FILTER in these lessons

Guides that use FILTER

Related functions

Updated