Excel Function Translator Logo
    The Excel Translator

    FILTER

    Lookup & Reference

    Filters a range of data based on criteria you define. Returns an array of matching rows.

    Translations
    EnglishFILTER
    FrenchFILTRE
    SpanishFILTRAR
    GermanFILTER
    ItalianFILTRO
    PortugueseFILTRAR
    DutchFILTER
    PolishFILTRUJ
    RussianФИЛЬТР
    TurkishFİLTRE
    CzechFILTER
    HungarianSZŰRŐ
    SwedishFILTER
    DanishFILTER
    FinnishSUODATA
    Syntax
    FILTER(array, include, [if_empty])

    Arguments

    • arrayThe range to filter
    • includeBoolean array of same height (TRUE keeps row)
    • if_emptyValue to return if no results(optional)
    Examples
    =FILTER(A1:C10,B1:B10>100)
    Rows where B>100

    Filters rows by condition

    =FILTER(A1:C10,A1:A10="Active")
    Active rows

    Filters by text match

    =FILTER(A:C,(A:A="Sales")*(B:B>1000))
    Multiple conditions

    Uses * for AND logic

    Tips & Best Practices
    • •Returns a dynamic array that spills
    • •Use * for AND conditions, + for OR
    • •Combine with SORT for sorted results
    Common Mistakes
    • •Forgetting that FILTER requires the array and the include condition to have matching dimensions - a mismatch produces a #VALUE! error rather than a partial result
    • •Not supplying the optional if_empty argument, so FILTER returns a #CALC! error when no rows match the condition instead of a clean fallback value like 0 or an empty string
    • •Using FILTER in a version of Excel that doesn't support dynamic arrays (older desktop or non-Microsoft 365 builds), where it simply isn't available at all rather than degrading gracefully
    Related Functions
    SORTOften chained with FILTER to both narrow down and reorder the same dataset in one spilled formula.
    UNIQUEFrequently combined with FILTER to extract a distinct, filtered subset of a list.
    IFCan replicate simple single-cell filtering logic, but lacks FILTER's ability to spill an entire filtered array automatically.
    Frequently Asked Questions

    Why does FILTER return a #CALC! error?

    That means no rows matched your condition and you haven't supplied an if_empty argument - add one, like FILTER(range,condition,"No matches"), to avoid the error.

    Why do I get a #VALUE! error with FILTER?

    The array and the include condition don't have matching dimensions - both need to cover the same number of rows (or columns) for FILTER to compare them correctly.

    Is FILTER available in all versions of Excel?

    No, it requires Microsoft 365 or Excel 2021 and later - older perpetual versions like Excel 2019 or 2016 don't support it.

    Need to translate a formula using FILTER?

    Use our translator to convert your complete formula