Excel FILTER Function: pull matching rows with a formula

The Filter button hides rows. The FILTER function (Excel 365/2021) copies matching rows to another place — and updates the moment the data changes.

The formula

=FILTER(array, include, [if_empty])

• array — the whole table to return.
• include — a TRUE/FALSE test, one per row.
• if_empty — what to show when nothing matches.

One condition

=FILTER(A2:D100,C2:C100="North","No rows")

Every North row, all four columns, spilled below the formula.

Condition in a cell

=FILTER(A2:D100,C2:C100=G1)

Put a drop-down list in G1 and you have a simple report.

AND: multiply the tests

=FILTER(A2:D100,(C2:C100="North")*(D2:D100>1000))

OR: add the tests

=FILTER(A2:D100,(C2:C100="North")+(C2:C100="South"))

Contains a word

=FILTER(A2:D100,ISNUMBER(SEARCH("pen",B2:B100)))

Sorted results

=SORT(FILTER(A2:D100,C2:C100="North"),4,-1)

Sorted by column 4, largest first. See SORT.

Errors

• #SPILL! — cells below the formula are not empty.
• #CALC! — nothing matched and you left out if_empty.
• #VALUE! — the include range is a different height from the array.

No 365? Use the Advanced Filter or a normal filter.