How to Use FILTER Function in Google Sheets Easily

Use the FILTER function in Google Sheets to extract rows or columns from a range that meet one or more conditions. The syntax is =FILTER(range, condition1, [condition2, ...]), where range is the data to filter and condition is a logical test applied to that data.

Syntax

The FILTER function has this structure:

• range: The cells you want to filter.
• condition1, condition2, ...: One or more conditions that decide which rows or columns to keep.

Each condition must be a logical expression that returns TRUE or FALSE for each row or column in the range.

plaintext

=FILTER(range, condition1, [condition2, ...])

Example

This example filters a list of sales data to show only rows where the sales amount is greater than 100.

google_sheets

=FILTER(A2:B6, B2:B6 > 100)

/*
Data in A2:B6:
A2: Product, B2: Sales
A3: Apples, B3: 120
A4: Bananas, B4: 90
A5: Cherries, B5: 150
A6: Dates, B6: 80
*/

Output

Apples  120
Cherries 150

Common Pitfalls

• Make sure the range and all condition ranges have the same size, or you will get an error.
• If no rows meet the conditions, FILTER returns an error. Use IFERROR to handle this gracefully.
• Conditions must be logical expressions like B2:B6>100, not just values.

google_sheets

=FILTER(A2:B6, B2:B6 > 100)  

/* Wrong: =FILTER(A2:B6, 100) - This will cause an error because 100 is not a condition */

=IFERROR(FILTER(A2:B6, B2:B6 > 100), "No results")

Quick Reference

Part	Description	Example
range	Cells to filter	A2:B6
condition1	First filter condition	B2:B6 > 100
condition2	Optional second condition	A2:A6 = "Apples"

Key Takeaways

FILTER extracts data rows or columns that meet specified conditions.

Conditions must be logical tests matching the size of the range.

Use IFERROR to handle cases where no data matches the filter.

FILTER updates automatically when source data changes.

Multiple conditions can be combined to refine results.