How do I return only the rows of a range that meet a condition, without manually filtering?

FILTER returns only the rows of a range where a condition is true, spilling the matching rows into the cells below and to the right automatically: =FILTER(A2:C100, B2:B100="Open").

Formula (Excel 365 and Excel 2021 or newer, and Google Sheets. Older, non-subscription versions of Excel do not support FILTER; use the AutoFilter or Advanced Filter menu tool instead.)

=FILTER(A2:C100, B2:B100="Open")

FILTER takes a range and a condition, and returns only the rows where that condition is TRUE, automatically spilling the results into as many cells as needed below and to the right of the formula. =FILTER(A2:C100, B2:B100="Open") returns every row of A2:C100 where the matching cell in column B says "Open", and updates automatically if the source data changes.

Because the result spills, you do not need to know in advance how many rows will match; FILTER fills exactly as many rows as it finds. If nothing matches, FILTER returns an error depending on the app, so many people add a third argument as a fallback: =FILTER(A2:C100, B2:B100="Open", "No matches").

Step by step

1
Select the full range you want to filter from
Include every column you want returned, e.g. A2:C100.
2
Write the condition as a second argument
e.g. B2:B100="Open" tests each row in column B.
3
Leave room below and to the right for the spill
Do not put other data directly below or beside the formula cell, or FILTER will show a #SPILL! error.
4
Add a fallback for no matches
=FILTER(range, condition, "No matches") avoids an error when nothing meets the condition.

Common questions

Why does FILTER show a #SPILL! error?

Something is blocking the cells FILTER needs to spill its results into. Clear the cells below and to the right of the formula, or move the formula somewhere with more open space.

Does FILTER work in every version of Excel?

No. FILTER requires Excel 365 or Excel 2021 and newer. In older Excel, use the AutoFilter or Advanced Filter tool from the Data menu instead.

Related formulas

How to List Distinct Values with the UNIQUE Function

How to Count Rows That Match Multiple Criteria with COUNTIFS

How to Use SUMIF and SUMIFS in Excel and Google Sheets

From the blog

How to Make Sense of Your Spreadsheet Data Without Being an Analyst

Want a formula written for your exact spreadsheet, not a generic example?

Try the free formula generator