Google Sheets FILTER Function: How to Filter Arrays

In Google Sheets, the FILTER function returns only the rows or columns that meet conditions you specify. It creates a live array result, so the displayed list updates when the source data changes; use a built-in filter instead when you want to temporarily hide rows in place.

What FILTER does in Google Sheets

FILTER is useful when you need a separate, formula-driven view of part of a table. For example, a sales sheet can show West-region orders over a chosen amount on another tab, while leaving the original table intact. The output is not a copied set of independent records: it is a calculated result linked to the source range.

The function’s syntax is FILTER(range, condition1, [condition2, ...]). The range is the data to return. Each condition is a one-row or one-column set of TRUE/FALSE values that identifies which rows or columns to include. Conditions must align with the dimension being filtered: a row filter needs a condition with one value for each row in the source range.

FILTER returns the matching values in their original order. It does not sort the results, change the source data, or automatically copy the source cells’ formatting. Format the destination area separately if you want the displayed results to have a particular number, date, or color style.

How the array result works

Google Sheets expands an array result into neighboring cells. If a formula returns five matching records across four columns, it needs an unobstructed five-by-four area. The formula itself sits in the top-left output cell; the other values appear in the cells below and to the right. You do not need to fill the formula down row by row.

For a concrete example, suppose a table has headers in row 1 and records in A2:D100: order ID in column A, region in B, order date in C, and sales amount in D. Put the formula in an empty cell or on a separate sheet. With those records in A2:D100, regions in B2:B100, and amounts in D2:D100, this returns West orders with sales greater than 500:

=FILTER(A2:D100,B2:B100="West",D2:D100>500)

The formula returns all four columns for each row that satisfies both tests. The separate condition arguments work as an AND: a row must be West and have sales above 500. If three records meet both conditions, the result contains those three rows, in the same order as the source.

Because the output is an array, existing content in its expansion area can prevent the results from appearing. Clear the cells where the result needs to expand, or move the formula to a larger blank area. A formula can also appear to return fewer records than expected if the source range stops before the last row of data.

Build a FILTER formula step by step

Start with the exact rectangular range you want returned, including all the columns needed in the result. Then create a condition range that has the same number of rows as the data range. For example, A2:D100 contains 99 rows, so a condition based on column B should also start in row 2 and end in row 100.

Select a blank output cell with enough room below and to the right for the result.
Enter =FILTER( and select or type the data range to return.
Add a comma, then enter a condition range and a test, such as a region column equal to a region name.
Add more comma-separated conditions if every condition must be true, then close the parentheses.
Press Enter and check that the resulting rows and columns match the criteria.

With product names in A2:A50 and stock counts in B2:B50, for example, this returns names for products with fewer than 10 units. The formula assumes both ranges cover the same 49 data rows:

=FILTER(A2:A50,B2:B50<10)

To return complete rows, make the first argument the whole table rather than just the product-name column. That distinction matters: the first argument controls what appears, while the condition argument controls which records qualify.

Combine conditions, alternatives, and dates

Use additional condition arguments when every test must be met. For example, a report might include only orders from a particular region with a value above a threshold. The row alignment must remain consistent across all ranges; using B2:B100 with D3:D101 shifts the tests and can produce incorrect results or a size mismatch.

To allow either of two values in one column, combine the TRUE/FALSE tests with addition and test whether the result is greater than zero. For rows in A2:D100 and region names in B2:B100, this returns orders from either West or East:

=FILTER(A2:D100,((B2:B100="West")+(B2:B100="East"))>0)

The addition acts as OR here: at least one comparison must be true. You can combine that grouped test with another condition argument when, for instance, those regions must also meet a sales threshold.

Date criteria work like other comparisons, provided the source cells contain real date values rather than text that only looks like a date. With records in A2:D100 and dates in C2:C100, this returns dates in January 2026:

=FILTER(A2:D100,C2:C100>=DATE(2026,1,1),C2:C100<DATE(2026,2,1))

The start is inclusive and the next-month boundary is exclusive, which also works when date cells contain times. Change the year and month in the two DATE calls to suit the period you need.

Handle no matches and common errors

If no records meet the criteria, Sheets returns #N/A rather than an empty result. That is a normal outcome, but it can be confusing in a dashboard. You can replace that specific no-match error with a readable message. With data in A2:D100, a region in B2:B100, and a criterion in F1, use:

=IFNA(FILTER(A2:D100,B2:B100=F1),"No matching rows")

IFNA handles the no-match #N/A result while leaving other kinds of errors visible for investigation. Avoid using a broad error handler simply to hide problems: a misspelled range or a broken reference should be fixed, not disguised as an empty result.

Common problems to check include:

Different range sizes: The condition must have the correct number of rows or columns for the dimension being filtered.
Blocked output: Clear cells in the area where the array needs to expand.
Unexpected blanks: Check for blank cells in the criteria column, extra spaces in text, and rows outside the selected source range.
Text instead of dates or numbers: Values stored as text may not compare as intended with numeric thresholds or dates.
Wrong orientation: A condition for selecting rows should correspond to the source rows; a condition for selecting columns should correspond to its columns.

Sort or refine the filtered output

FILTER preserves source order. If you want the matching rows sorted, wrap the result in SORT. The sort index is counted within the filtered result, not by the original sheet’s column letter. With source data in A2:D100 and the region in B2:B100, this sorts matching West rows by the fourth returned column, sales amount, from largest to smallest:

=SORT(FILTER(A2:D100,B2:B100="West"),4,FALSE)

The formula assumes A:D are returned in that order, so 4 refers to column D of the result. In SORT, FALSE specifies descending order; use TRUE for ascending order. If there may be no matching rows, consider wrapping the formula in IFNA as well so the dashboard displays a message instead of an error.

You can also make criteria editable by referring to a cell instead of typing a fixed value into the formula. For instance, entering a region name in F1 lets someone change the selection without editing the formula. This is especially useful for reports shared with people who need to choose a category but should not alter the source table.

FILTER versus built-in filters and filter views

A formula filter and a sheet filter solve related but different problems. A formula returns a separate array of matching values. A built-in filter hides rows in the original table for viewers of that sheet. To create one in the Google Sheets browser, select the table and choose Data > Create a filter. Use the filter controls in the header row to show or hide values.

For saved or personal filtering arrangements, Google Sheets also offers filter views. They are useful when several people need different ways to inspect the same table without repeatedly changing the visible filter for everyone. In the browser, look under Data > Filter views to create or manage a view. Menu labels and availability can change as Google updates Sheets, but these are Sheets features, not Excel ribbon commands.

Choose FILTER for a live, formula-driven report in another area or sheet.
Choose a regular filter to interactively hide rows in the current table.
Choose a filter view when you want a reusable view of the source data without building a separate result table.

Excel for Microsoft 365 also has a FILTER function, but its syntax and edge behavior are not identical in every respect. In Excel, the function includes an optional argument for what to show when there are no results; Google Sheets commonly handles that case with IFNA. A formula intended for one program should be checked before being moved to the other.

When QUERY or another approach may fit better

For straightforward row selection, FILTER is often easier to read than a query string. Google Sheets’ QUERY function is an alternative when a report also needs operations such as selecting particular columns, grouping rows, or aggregating values. Its query language has its own syntax and rules, so it may be more complex than FILTER for a simple condition.

For a one-off inspection, the built-in filter is usually simpler than adding a formula. For a reusable result that must update automatically, FILTER is a good fit. If the task requires a summary such as total sales by region rather than a list of qualifying orders, a pivot table or QUERY may be more appropriate. Choose based on whether you need filtered records, an interactive view, or an aggregation.

Frequently Asked Questions

Does Google Sheets have a FILTER function?
Yes. The Google Sheets FILTER function returns rows or columns that meet one or more conditions. It is available in Sheets and produces an array result that expands from the formula cell.

How do I filter multiple conditions in Google Sheets?
Add each required condition as another argument, such as a region test and a sales threshold. Separate condition arguments require all the tests to be true. For alternatives within one column, combine comparisons with addition and test whether the sum is greater than zero.

Why does FILTER return #N/A?
In Google Sheets, #N/A usually means that no rows or columns matched the conditions. If that is an expected possibility, wrap the FILTER expression in IFNA and provide a message such as “No matching rows.”

Why is my FILTER result not expanding?
Check for existing values or formulas in the cells where the result should appear. Also confirm that the condition range has the right size and orientation. The output needs a clear area large enough for all matching rows and returned columns.

Can I use FILTER to return just one column?
Yes. Set the first argument to the column you want returned and use an aligned condition range. If you want several columns, use a rectangular range as the first argument and apply conditions that select the appropriate rows.

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.