Google Sheets FILTER Formula: How Do You Use It?

The Google Sheets FILTER formula returns only the rows or columns that meet conditions you specify. For example, if product names are in A2:A6 and statuses are in C2:C6, use =FILTER(A2:A6,C2:C6="Open") to list products whose status is Open; the results update as the source data changes.

What the FILTER formula does

FILTER is a function for creating a live subset of a range. Instead of hiding records in the original table, it displays matching data in the cell where you enter the formula and in the cells below or beside it. That makes it useful for summaries, working lists, dashboards, and reports that should respond automatically to edits.

The source range can contain one column, several columns, or a horizontal row. A condition tells Sheets which entries to keep. FILTER does not rearrange or modify the source data, and it does not copy values into a fixed, manually selected result area. Its output is an expanding array: when more records match, the result can grow; when fewer match, it can shrink.

For instance, a sales sheet could contain dates, representatives, and amounts. A formula can show only sales above a threshold, or only records for one representative. The original table remains intact, so other formulas and collaborators can continue to use it.

Syntax and how conditions work

The general syntax is FILTER(range, condition1, [condition2, ...]). The range is the data to return. Each condition is a row or column of TRUE/FALSE results indicating which entries qualify. Square brackets in the syntax description mean an argument is optional; do not type those brackets into a formula.

For a vertical source range, each condition must have the same number of rows as the range. For a horizontal range, the conditions must have the same number of columns. A mismatch commonly produces a range-size error. You can return a multi-column range while testing just one column, as long as the condition range lines up with the source rows.

When you provide more than one condition argument, FILTER keeps entries that satisfy every condition: this is AND logic. To express OR logic, combine tests into one condition, commonly by adding their TRUE/FALSE results and checking whether the sum is greater than zero.

Google Sheets uses commas between arguments in the examples here. A spreadsheet’s locale can require semicolons instead, and some locales use different conventions for decimal separators. If a valid-looking formula is rejected, check the spreadsheet’s locale under File > Settings and use the separator expected by that locale.

A basic example with a complete result

Suppose A1:C6 contains a table with the headers Product, Region, and Status. Rows 2–6 are Notebook, East, Open; Pen, West, Open; Folder, East, Closed; Marker, East, Open; and Eraser, West, Closed. To return all columns for open items, enter the formula in an empty area outside the source table.

With headers in A1:C1, product data in A2:C6, and statuses in C2:C6, use =FILTER(A2:C6,C2:C6="Open"). The result is three columns wide and contains the rows for Notebook, Pen, and Marker, in their original order. The formula does not include the header row; add a header separately if you want one above the output.

For a numeric condition, the same pattern can select values greater than a threshold. With transaction details in A2:C100 and amounts in C2:C100, enter =FILTER(A2:C100,C2:C100>100) to return rows whose amount exceeds 100. The number is an illustrative cutoff, not a special FILTER setting. Use the appropriate comparison operator for your task: >, >=, <, <=, or <> for not equal.

Combining conditions for AND and OR

Separate condition arguments make an AND rule easy to read. For example, you might need open orders from the East region, but not open orders from every region. Each test checks the corresponding row, and a row appears only if both tests are TRUE.

With product, region, and status data in A2:C100, where region is in B and status is in C, use =FILTER(A2:C100,B2:B100="East",C2:C100="Open"). This returns rows where the region is East and the status is Open. If no row meets both tests, FILTER returns an error rather than an empty result.

For OR, such as East or West, add the two comparison arrays and test whether their sum is positive. To require an open status as well, combine that OR test with the status condition.

With region values in B2:B100 and status values in C2:C100, use =FILTER(A2:C100,((B2:B100="East")+(B2:B100="West"))>0,C2:C100="Open"). The result includes open rows from either region. Parentheses matter: they keep the addition of the two region tests together before the greater-than comparison.

Matching text, excluding blanks, and sorting results

For an exact text criterion such as Open, an equality test is usually clearest. If you want to find a word or phrase anywhere inside a cell, combine SEARCH with ISNUMBER. SEARCH returns a position when it finds the text and an error when it does not; ISNUMBER turns that result into TRUE or FALSE for FILTER. SEARCH is not case-sensitive.

With descriptions in A2:A100 and related row data in A2:C100, use =FILTER(A2:C100,ISNUMBER(SEARCH("urgent",A2:A100))) to return rows whose description contains urgent, including descriptions such as Urgent: call supplier. The source and condition ranges need matching row counts.

To leave out blank names or identifiers, test the relevant column against an empty string. With records in A2:C100 and required identifiers in A2:A100, use =FILTER(A2:C100,A2:A100<>""). This is useful when a source range is deliberately larger than the current dataset. It filters blank identifiers, though it does not automatically remove rows with blanks in other columns.

FILTER preserves the source order. To order its output, wrap it in SORT. With records in A2:C100 and a filter condition in C2:C100, use =SORT(FILTER(A2:C100,C2:C100="Open"),2,TRUE) to return open rows sorted by the second column of the returned range in ascending order. Change the sort-column number to match the column you want, and use FALSE for descending order.

Handling no matches and output errors

If no entries satisfy the condition, Google Sheets returns #N/A. That is expected behavior, not necessarily a sign that the formula is malformed. If a blank display is more suitable for a report, wrap FILTER in IFNA. With data in A2:C100 and status in C2:C100, use =IFNA(FILTER(A2:C100,C2:C100="Open"),"No matching rows"). The message appears when FILTER returns #N/A; IFNA does not hide unrelated errors.

Another common issue is that the output array cannot expand because cells in its destination area are occupied. Clear the cells that would be part of the result, or move the formula to a larger empty area. A blocked array commonly produces a #REF! message explaining that the result was not automatically expanded. Also check that the formula is not placed inside the range it is trying to return, which can create a circular reference.

If the formula returns unexpected rows, verify the exact source and condition ranges, look for extra spaces in text, and confirm that the comparison matches the values in the sheet. For numeric comparisons, confirm that the values are actually stored as numbers rather than text. A condition range that starts one row above or below the source can produce an error or misleading results.

FILTER formulas versus the built-in filter

A FILTER formula and the Google Sheets filter controls solve related but different problems. A formula creates a separate result that can feed another calculation or appear in a report. The built-in filter hides nonmatching rows in the existing table for people viewing that table. To use the built-in option in a browser, select the data and choose Data > Create a filter. You can then use the filter controls in the header row.

Choose a formula when the result needs to update in a separate location, when you want to combine conditions with other functions, or when another part of the sheet should reference the returned records. Choose a built-in filter when you or a collaborator want to inspect and temporarily narrow the original table. Filter views, available from Data > Filter views, can be useful when people need different saved views without changing the same visible filter for everyone.

Excel also has a FILTER function, but it is not identical in every detail. In Excel, FILTER includes an optional argument for a value to return when there are no matches; Google Sheets handles the no-match case with an error, which you can manage with IFNA. The examples and menu paths in this article are for Google Sheets in a web browser, not Excel.

Frequently Asked Questions

Can FILTER return multiple columns? Yes. Use a multi-column source range, such as A2:C100, while applying a condition to a matching-height column such as C2:C100. The formula returns the selected rows across all three columns.

How do I make FILTER show nothing when there are no matches? Wrap the formula in IFNA and use an empty string as the fallback. With records in A2:C100 and status in C2:C100, enter =IFNA(FILTER(A2:C100,C2:C100="Open"),""). This displays a blank when FILTER returns #N/A.

Can I use more than one condition in Google Sheets FILTER? Yes. Add each AND condition as another argument, such as a region test followed by a status test. For OR conditions, combine tests in one Boolean expression, for example by adding the results and checking whether the sum is greater than zero.

Why does FILTER return #N/A? The usual reason is that no rows meet the condition. Check for misspelled criteria, unexpected spaces, incorrect ranges, or a comparison against text that is stored differently. If no match is a normal possibility, handle it with IFNA.

Can I use FILTER with checkboxes? Yes. Google Sheets checkboxes normally produce TRUE when checked and FALSE when unchecked. With records in A2:C100 and checkboxes in C2:C100, use =FILTER(A2:C100,C2:C100=TRUE) to return rows with checked boxes. Make sure the checkbox cells and records align row by row.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.