Skip to content
Klargrid

The FILTER function

The rows of a table that meet a condition, copied in one go and kept up to date: every North order, every late one.

Syntax

=FILTER(array, include, [if_empty])

In a French Excel: =FILTRE(tableau; inclure; [si_vide]).

Arguments

  • array: the range to filter.
  • include: one condition per row, B2:B100="North"; * for AND, + for OR.
  • if_empty: what to show if no row matches.

An example

=FILTER(A2:D100,(B2:B100="North")*(D2:D100>1000),"None")

North orders over 1,000, spilling below the formula.

Pitfalls

  • No row and no third argument: #CALC!.
  • Occupied cells below the formula: #SPILL!.
  • Missing from Excel 2019 and earlier.

Going further

Frequently asked questions

How do I filter on two conditions?
Multiply the conditions for an AND, (B2:B100="North")*(C2:C100="Delivered"), add them for an OR.