Function reference

FILTER in Excel & Google Sheets

FILTER is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.

1 intermediateBuild a custom FILTER formula →

When to use FILTER

Extract all orders for a specific region, all tasks assigned to one person, or all products above a price threshold into a separate area automatically.

Worked examples (1)

Array Formulasintermediate

Filter rows by a condition with FILTER

=FILTER(A2:C100,B2:B100="East","No results")

Open formula FILTER reference

Common errors with FILTER

FILTER is not available

FILTER requires Excel 365 or Excel 2021. Use Advanced Filter or a VLOOKUP workaround for older versions.

SPILL error

Clear the cells below and to the right of the formula — FILTER needs empty space to spill its results.

Multiple conditions

Use * for AND logic — (B2:B100="East")*(C2:C100="Active") — and + for OR logic — (B2:B100="East")+(B2:B100="West").

Frequently asked questions

How do I filter by multiple conditions?

Multiply the condition arrays for AND logic — =FILTER(A2:C100,(B2:B100="East")*(D2:D100>1000),"No results") returns rows where region is East AND amount exceeds 1000.

Need a custom variation?

Describe your specific FILTER problem and get a formula written for your exact data.

Open generator with FILTER