Excel FILTER Function: Extract Matching Rows Instantly

What does FILTER do?

FILTER is a dynamic array function that looks through a range and returns only the rows that satisfy a test you define. You give it the data, a condition that evaluates to TRUE or FALSE for each row, and optionally a message to show when nothing matches. The result spills into the cells below and to the right of the formula, so you get a live, filtered copy of your data without sorting or hiding anything.

Before FILTER, extracting matching rows meant advanced filters, helper columns, or copying and pasting. FILTER does it with one formula that updates automatically when the source data changes. It is especially useful for dashboards, reports, and any situation where you need a clean subset of a larger table.

Imagine you have a sales table with region, product, and amount. You want a list of all orders from the North region, or all orders above a certain value. With FILTER you write one formula and the matching rows appear instantly. If you add new orders to the source table, the filtered list grows or shrinks to match.

When to use FILTER

• Pull all records for a specific customer, region, or category from a master list.
• Show only rows where a value is above or below a threshold, such as sales over 1000.
• Create a dynamic report that updates as new data is added to the source table.
• Combine with SORT to display filtered results in a chosen order.
• Extract rows that meet two or more conditions using multiplication or the AND logic.
• Return a friendly message like 'No results' when nothing matches instead of an error.

FILTER syntax

What it returns: FILTER returns a dynamic array of the rows that meet the include condition. If no rows match and if_empty is provided, it returns that value; otherwise it returns #CALC!.

FILTER examples

Common FILTER errors and how to fix them

Tips for using FILTER

• Use multiplication (*) for AND conditions and addition (+) for OR conditions inside the include argument.
• Wrap text criteria in double quotes, for example "North", and use cell references for flexible criteria.
• Combine FILTER with SORT to present results in a specific order, for example =SORT(FILTER(...)).
• Always provide an if_empty message to avoid #CALC! errors in reports.
• Remember that FILTER returns a dynamic array; leave enough empty cells for the result to spill.
• Use entire column references like A:A only when necessary, as they can slow down large workbooks.

Frequently asked questions

Related functions