How to Use FILTER in Excel: Syntax, Examples, and Tips

Use the FILTER function in Excel to extract data from a range that meets specific criteria. The syntax is =FILTER(array, include, [if_empty]), where array is the data to filter, include is the condition, and if_empty is optional text if no data matches.

Syntax

The FILTER function has three parts:

• array: The range or array of data you want to filter.
• include: A condition or logical test that decides which rows to keep.
• if_empty (optional): What to show if no data matches the condition.

excel

=FILTER(array, include, [if_empty])

Example

This example filters a list of sales data to show only sales greater than 100.

excel

A1:B5 contains:
Product | Sales
Apple   | 120
Banana  | 90
Cherry  | 150
Date    | 80

In cell D1, enter:
=FILTER(A2:B5, B2:B5>100, "No sales > 100")

Output

Apple	120
Cherry	150

Product	Sales
Apple	120
Banana	90
Cherry	150
Date	80

Common Pitfalls

Common mistakes when using FILTER include:

• Not using a logical condition in the include argument (e.g., writing a number instead of a test).
• Forgetting to handle the if_empty argument, which causes an error if no data matches.
• Using ranges of different sizes for array and include.

excel

Wrong: =FILTER(A2:A5, 100)  (This is not a condition)
Right: =FILTER(A2:A5, A2:A5>100, "No data")

Quick Reference

Part	Description	Example
array	Data to filter	A2:A10
include	Condition to filter by	B2:B10>50
if_empty	Value if no match	"No results"

Key Takeaways

Use FILTER to extract data that meets a condition from a range or array.

The include argument must be a logical test returning TRUE or FALSE for each row.

Add the optional if_empty argument to avoid errors when no data matches.

Ensure array and include ranges are the same size to avoid errors.

FILTER dynamically updates results when source data changes.