Skip to content
ExcelExcel 2021+

FILTER function in Excel

Filters a range of data based on criteria you define

=FILTER(array, include, [if_empty])

FILTER syntax and parameters

Three arguments, two required.

  • arrayarrayRequired

    The array, or range to filter

  • includearrayRequired

    A Boolean array whose height or width is the same as the array

  • if_emptyanyOptional

    The value to return if all values in the included array are empty (filter returns nothing)

FILTER examples

Formulas you'll actually reuse.

  1. Deals over $1,000, with a friendly message instead of #CALC!:

    =FILTER(A2:C200, C2:C200 > 1000, "No deals over $1,000")

  2. AND two conditions by multiplying them — West AND over $1,000:

    =FILTER(A2:C200, (B2:B200 = "West") * (C2:C200 > 1000))

FILTER in Google Sheets

Same name — your formula ports as-is.

Try FILTER in the playground

Edit the example — nothing to install.

Preloaded with the FILTER formula from Example 1 — change anything and watch it respond.

Loading the editor…

FILTER errors

What they mean — and the fixes.

  • #CALC!

    Nothing matched and no if_empty was given — add the third argument for an empty-state message.

Related functions

More ways Excel gets this done.