Google Sheets FILTER Function: Examples, Syntax, and Tips

In Google Sheets, the FILTER function returns only the rows or columns from a range that meet conditions you specify. For example, =FILTER(A2:E6,B2:B6="West") returns records whose Region value is West; the source data stays unchanged, and the results update when the data or conditions change.

What the FILTER function does

FILTER is a formula for creating a live subset of a table. Instead of hiding rows in the original sheet, it displays matching values in the cells where you enter the formula. This is useful for a report that should show only one region, a list of orders above a threshold, or a date-specific extract that changes as the underlying records are edited.

FILTER returns the selected cells in their original order. It does not sort the results, remove duplicate records, or change the source table. If a matching record is edited so it no longer meets the condition, it disappears from the output; if a different record begins to match, that record appears. This makes FILTER different from copying and pasting a one-time selection.

For a concrete example, assume row 1 contains the headers Date, Region, Product, Units, and Revenue, and rows 2 through 6 contain these records:

2026-01-05, West, Tea, 12, 36
2026-01-12, East, Coffee, 8, 24
2026-02-03, West, Coffee, 15, 60
2026-02-10, North, Tea, 5, 15
2026-02-18, East, Tea, 10, 30

The examples below use that layout, with the headers in A1:E1 and the data in A2:E6. The dates should be actual spreadsheet dates, rather than text that merely looks like a date.

Syntax and range alignment

The function takes a range to return, followed by one or more conditions. In practical terms, the structure is FILTER(range, condition1, condition2, …). Each condition is an array of TRUE or FALSE results. A row-oriented filter needs one result for each row in the selected range; a column-oriented filter needs one result for each column.

For example, if the range is A2:E6, it has five rows. A condition such as B2:B6=”West” also has five rows, so Sheets can test each record against its region. A condition that refers to B2:B4 has only three results and does not line up with the five-row data range. Matching dimensions are a frequent source of FILTER errors.

When more than one condition is supplied as separate arguments, a row must satisfy every condition. Conditions are not typed as quoted words like “AND”; each is a separate argument. Conditions can use comparisons such as equals, greater than, or less than, and can also be produced by functions that return arrays of TRUE and FALSE.

Filter rows by one condition

With the example data in A2:E6, use this formula to return every record for the West region:

With headers in A1:E1 and the five records in A2:E6, enter =FILTER(A2:E6,B2:B6="West") in an empty cell.

The result contains the January 5 Tea record and the February 3 Coffee record, including all five columns. FILTER returns the matching rows in the same order as the source. The formula excludes the header because its return range begins at row 2; to label the result, put suitable headers above the formula output or include headers separately.

Conditions can also compare numbers. With units in D2:D6 and the full records in A2:E6, this formula returns records with at least 10 units:

With headers in row 1, dates in A2:A6, and units in D2:D6, enter =FILTER(A2:E6,D2:D6>=10).

It returns the first, third, and fifth records: 12, 15, and 10 units meet the threshold. The comparison is applied to the corresponding row, so the output still includes each matching record’s date, region, product, and revenue.

Combine conditions and create OR logic

To require two conditions at once, add a condition argument for each one. With Region in B2:B6 and Units in D2:D6, this returns West records with at least 10 units:

With the example records in A2:E6, enter =FILTER(A2:E6,B2:B6="West",D2:D6>=10).

Both West records satisfy the units threshold, so the result contains the January 5 Tea entry and the February 3 Coffee entry. This is AND logic: a row must pass the region test and the units test. If you want West records regardless of units, omit the second condition rather than changing its value.

For OR logic, combine Boolean tests with the plus operator. In Sheets, TRUE and FALSE values in this kind of array calculation act like 1 and 0, so a sum greater than zero means at least one test passed. With regions in B2:B6, use:

With the example records in A2:E6, enter =FILTER(A2:E6,(B2:B6="West")+(B2:B6="East")).

This returns the two West records and the two East records, in their original order. Parentheses make clear that each comparison is evaluated before the addition. For a long list of alternatives, other approaches such as REGEXMATCH may be more compact, but the pattern must be designed carefully if the values contain special regular-expression characters.

Filter by dates, partial text, or columns

To select a date interval, use a lower and upper bound as separate conditions. Because the example dates are in column A, this formula returns records from January 2026, including January 1 but excluding February 1:

With actual date values in A2:A6 and records in A2:E6, enter =FILTER(A2:E6,A2:A6>=DATE(2026,1,1),A2:A6<DATE(2026,2,1)).

The result is the January 5 and January 12 entries. Using a less-than boundary for the first day of the next month avoids having to guess the last date of a month. DATE constructs real date values, and the year, month, and day are supplied as numbers. If dates are stored as text, date comparisons may not behave as expected; convert the source values to dates first.

For partial text matches, combine FILTER with SEARCH and ISNUMBER. SEARCH finds a text string without distinguishing uppercase and lowercase letters; ISNUMBER turns a successful position into TRUE and a not-found result into FALSE. With product names in C2:C6, the following returns rows whose product contains “tea”:

With product values in C2:C6 and full records in A2:E6, enter =FILTER(A2:E6,ISNUMBER(SEARCH("tea",C2:C6))).

It returns the three Tea records. For a case-sensitive partial match, use FIND instead of SEARCH. If the search term is in a cell, replace the quoted word with that cell reference; this makes it easier to change the term without editing the formula.

FILTER can also return selected columns rather than selected rows. The condition then needs to line up horizontally with the columns in the range. To return just the Revenue column from the example table, including its header:

With the complete table in A1:E6 and its headers in row 1, enter =FILTER(A1:E6,A1:E1="Revenue").

This returns the Revenue header and the five values below it. The condition has five cells, matching the five columns in A1:E6. This column-oriented use is less common, but it can help build a compact report from a wide table.

Where results appear and why formulas fail

FILTER produces an array result that expands into neighboring cells. Enter the formula in the top-left cell where the output should begin, and leave enough empty space below and to the right. Existing values, merged cells, or other obstructions in the output area can prevent the result from expanding. Clear the obstructing cells or move the formula to a larger empty area.

If no records match, FILTER returns a #N/A error. You can provide a friendly message with IFNA. With the example data in A2:E6 and regions in B2:B6, this searches for a region that is not present:

With the example table in A2:E6, enter =IFNA(FILTER(A2:E6,B2:B6="Central"),"No matching rows").

Since none of the five records is from Central, the result is “No matching rows.” IFNA is usually more targeted than IFERROR because it handles #N/A specifically. Still, wrapping a formula can conceal an unexpected #N/A that originates in the source data, so investigate errors when the message appears unexpectedly.

Other useful checks include confirming that the return range and condition ranges start and end on corresponding rows, checking for extra spaces or inconsistent labels such as “West ”, and verifying that numbers and dates are stored in the intended data types. FILTER does not automatically treat text “12” as the number 12 in every calculation. Also check that the formula’s output area is clear before assuming the condition itself is wrong.

FILTER compared with menu filters and QUERY

A formula filter and a menu filter solve related but different tasks. To interactively hide rows in a table, select the data and use Data > Create a filter. Filter controls let a person choose values or conditions directly in the source view. A filter view, available through Data > Filter views, can preserve a particular view without making the same visible-row selection for everyone. These tools are useful for browsing and editing records in place.

FILTER is preferable when the results should appear as a separate, formula-driven list, feed another calculation, or update automatically from criteria held in cells. It does not hide or rearrange the source rows. Remember, however, that the formula output is a result range, not a second editable copy of the source table; edit the original records if you want the underlying data to change.

QUERY is another Google Sheets function for selecting and transforming data. Its query language can express filtering, sorting, grouping, and selected columns in a single query, which is useful for more report-like operations. FILTER is often easier to read when the task is simply “return these rows if these tests pass.” Choose based on the operation: simple row criteria suit FILTER, while grouping or a more elaborate query may make QUERY a better fit. Excel has a FILTER function in newer versions, but its availability and some surrounding spreadsheet features differ from Google Sheets; the examples here are specifically for Sheets in a browser.

Frequently Asked Questions

Does FILTER change or delete the original data?
No. It displays matching values in the formula’s output area and leaves the source range intact. Changes to the source can change the displayed results, but editing the displayed array is not the same as editing the original records.

How do I show a message when FILTER finds nothing?
Wrap FILTER in IFNA and provide a text result, such as “No matching rows.” Use ranges with matching dimensions, and remember that IFNA can also catch a #N/A error coming from inside the data or calculation.

Can FILTER use more than one condition?
Yes. Add each required test as another condition argument; all must be true for a row to appear. For alternatives, combine Boolean tests with plus signs, using parentheses around each test.

How do I keep the column headings in the results?
The simplest approach is to place the headings in cells above the formula output and filter only the data rows. If you include the header row in the return range, your condition must also be designed to match it appropriately; otherwise it may be excluded or evaluated differently from the records.

Why does FILTER show #REF! or fail to expand?
A result array may be blocked by content in cells where it needs to expand. Clear those cells, check for merged cells, or move the formula to an empty area. A dimension mismatch between the returned range and a condition can cause a separate error, so verify the ranges too.

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.