FILTER returns the subset of rows that satisfy one or more conditions. It is the most natural way to build a dynamic, formula-driven view of a larger dataset — and it pairs beautifully with SORT, UNIQUE, and ARRAYFORMULA. This tutorial covers the syntax, multi-condition filters, OR logic, and the most common failure modes.

Single condition

=FILTER(A2:F, B2:B = "Active")

FILTER takes a return range and one or more condition arrays of the same length. The condition is evaluated row by row; rows where it is TRUE survive. Use whole-column ranges (A2:F) so the formula keeps working as new data arrives, and lock the conditions to the same height (B2:B).

Multiple conditions (AND)

=FILTER(A2:F, B2:B = "Active", C2:C >= DATE(2025,1,1))

Each extra condition argument is AND-ed with the previous ones. Conditions can mix comparison, range tests, REGEXMATCH, ISNUMBER — anything that produces a boolean per row. Performance is generally great even on tens of thousands of rows.

OR logic with addition

=FILTER(A2:F, (B2:B = "Active") + (B2:B = "Trial"))

FILTER does not have a built-in OR, but adding two boolean arrays gives you OR (TRUE+FALSE = 1, which FILTER reads as truthy). Wrap in parentheses so the addition happens before FILTER consumes the result. For three or more alternatives, switch to REGEXMATCH(B2:B, "^(Active|Trial|Hold)\$") — much cleaner than chaining additions.

Empty results

When no rows match, FILTER returns #N/A with the message "No matches". Wrap in IFERROR to substitute a friendly empty state: =IFERROR(FILTER(...), "No matching rows"). For data feeding another formula, return an empty array instead: =IFERROR(FILTER(...), "").

Worked examples

Active customers signed up this year

=FILTER(Customers!A:E, Customers!B:B = "Active", YEAR(Customers!D:D) = 2025)

Combines an equality and a derived comparison. Both filter the same Customers range.

Status in a small list

=FILTER(A2:F, REGEXMATCH(B2:B, "^(Active|Trial|Hold)\$"))

Cleaner than addition once the alternatives go past two.

Friendly empty state

=IFERROR(FILTER(...), "No matching rows")

Suppresses the default #N/A and keeps downstream formulas calm.

Common mistakes (and how to avoid them)

  • Mismatched range heights. The return range and every condition range must be the same height — otherwise #N/A.
  • Using AND or OR functions inside the condition. Both collapse the array to a single TRUE/FALSE. Use multiplication or addition on arrays.
  • Filtering on a column that has been imported as text when you expected numbers. ISNUMBER/comparison conditions return FALSE for the whole column.
  • Building a dashboard that depends on FILTER never returning empty. Always handle the no-match case explicitly.

Tips that pay off later

  • Wrap FILTER in SORT to deliver an already-sorted view.
  • FILTER plus UNIQUE deduplicates dynamically without copying data.
  • For dropdown-driven dashboards, use a single FILTER call referencing the dropdown cell — the whole report updates with one click.

When to reach for something else

If your filter logic is complex (multiple OR groups, computed buckets), QUERY usually expresses it more cleanly than nested FILTER conditions.

Functions used in this tutorial

FILTER → SORT → SORTN → UNIQUE → QUERY → ARRAYFORMULA →