XXLFormula AI

Excel

FILTER Function in Excel & Google Sheets

FILTER returns the rows or columns that match one or more conditions. It is a dynamic array function that spills results automatically, replacing manual copy-paste or helper columns.

Syntax

=FILTER(array, include, [if_empty])

Parameters

  • arrayRequired

    The range or array to return values from.

  • includeRequired

    A Boolean array whose height or width matches the array.

  • if_emptyOptional

    Value to return if no values meet the condition(s).

Examples

Filter rows greater than a value

=FILTER(A2:D100, C2:C100 > 1000)

Returns all rows from A2:D100 where column C is greater than 1000.

Multiple conditions

=FILTER(A2:D100, (B2:B100="West") * (C2:C100 > 1000), "No results")

Filters rows where column B is West and column C is above 1000, showing 'No results' if empty.

Common Errors

  • #CALC!

    Cause: No results match the condition and if_empty is not provided.

    Fix: Add a third argument as a fallback, e.g. 'No results' or 0.

  • #VALUE!

    Cause: The include array dimensions don't match the array dimensions.

    Fix: Ensure the condition range has the same number of rows (or columns) as the array.

Best Practices

  • Use FILTER inside SUM or COUNT to aggregate matching rows without extra helper columns.
  • Combine multiple conditions with multiplication (*) for AND logic and addition (+) for OR logic.
  • Wrap with SORT if you need the filtered results in a specific order.

Frequently Asked Questions

Need a custom version of this formula?

Describe what you need in plain English and get a ready-to-use formula in seconds.

Generate this formula with XLFormula AI