Google Sheets FILTER Function Syntax, Examples, and Tips

Google Sheets uses the FILTER function to return rows or columns that meet one or more conditions. Its syntax is =FILTER(range, condition1, [condition2, ...]); for example, =FILTER(A2:D6,B2:B6="East") returns the rows in A2:D6 whose column B value is East.

What the FILTER Function Does

FILTER creates a results array from a range, showing only the entries that satisfy the conditions you specify. It does not delete, hide, or rearrange the source data. The source remains unchanged, while the formula displays a live result elsewhere in the sheet. If the source data changes, the returned results update automatically.

This is useful when you need a reusable list, such as open tasks, sales for one region, or records above a chosen threshold. Unlike a manually applied filter, a formula result can feed another formula or appear in a separate report area. It is also possible to combine conditions, so a result can show, for example, East-region sales above a particular amount.

For the examples below, imagine a small table with headers in row 1 and five records in rows 2–6: Mia, East, North, 1200; Noah, West, South, 950; Ava, East, South, 1500; Liam, West, North, 1100; and Zoe, East, North, 800. The columns are Name, Team, Region, and Sales, in that order.

Syntax and Argument Meaning

The function signature is =FILTER(range, condition1, [condition2, ...]). The square brackets indicate that the additional conditions are optional; do not type the brackets into a working formula. Each argument has a specific role:

range is the data to return. It can contain one column, several columns, one row, or several rows.
condition1 is a Boolean test, such as a comparison that produces TRUE or FALSE values.
condition2 and later conditions are optional additional tests. Each must align with the rows or columns being filtered.

When filtering rows, the condition ranges must have the same number of rows as the source range. If the source is A2:D6, a row condition such as B2:B6 has five entries, one for each source row. A condition range with four or six entries does not line up and causes a size mismatch error. For filtering columns instead, the condition must correspond to the number of columns in the range.

Basic Example: Filter Rows by a Value

With names in A2:A6, team names in B2:B6, regions in C2:C6, and sales in D2:D6, enter this formula in an empty cell to return all columns for the East team:

=FILTER(A2:D6,B2:B6="East")

The result contains three rows, in their original order: Mia, Ava, and Zoe, along with their region and sales values. The comparison checks each cell in B2:B6, then returns the corresponding row from A2:D6 whenever the comparison is TRUE. Text comparisons in Sheets are generally not case-sensitive, so matching the capitalization of East is not usually essential.

The returned array expands from the formula cell into neighboring cells. Leave enough empty space to the right and below the formula for all matching columns and rows to appear. If you want only the names rather than the complete records, use A2:A6 as the source range while keeping B2:B6 as the condition range.

Combine Conditions for AND and OR

Separate condition arguments work as an AND test: a row must satisfy every supplied condition. With the same table layout, this formula returns records for the East team with sales greater than 1000:

=FILTER(A2:D6,B2:B6="East",D2:D6>1000)

The matching results are Mia and Ava. Mia has sales of 1200 and Ava has 1500; Zoe is excluded because her sales are 800. Each condition range matches the five rows in the source.

To express OR, add Boolean tests with the plus operator. With the same layout, the following returns records where the team is West or sales are at least 1200:

=FILTER(A2:D6,(B2:B6="West")+(D2:D6>=1200))

The result is Mia, Noah, Ava, and Liam, in source order. A row that meets either test is included; if it meets both, it still appears once. Parentheses make it clear which comparisons are being combined. For more complicated rules, consider adding a helper column that spells out the test, which can make a large formula easier to inspect.

Filter Columns, Blanks, and Numeric Criteria

Although many examples filter rows, the function can also filter horizontally. Suppose a monthly summary has month labels in B1:F1 and totals in B2:F2. To return only the columns whose totals exceed 1000, use the totals as the condition and include the corresponding month labels or values in the source range. The source and condition must align horizontally, just as they align vertically when filtering rows.

Blank checks and numerical comparisons work as conditions too. With nonblank names in A2:A6, this formula returns only records whose name cell is not empty:

=FILTER(A2:D6,A2:A6<>"")

In this example, all five records are returned because every name cell is populated. The operator <> means “not equal to.” Other common tests include greater than, less than, equal to, and comparisons against a cell containing a selected criterion. For dates, compare the date column with a real date value or a cell containing one; ensure the cells are stored as dates rather than text if comparisons behave unexpectedly.

Sort or Label the Filtered Results

FILTER preserves the order of the matching rows in the source range; it does not sort them. You can wrap the result in SORT when a ranked or alphabetized list is more useful. With the same A2:D6 table, this returns East-team records sorted by the fourth column, Sales, from largest to smallest:

=SORT(FILTER(A2:D6,B2:B6="East"),4,FALSE)

The order is Ava (1500), Mia (1200), then Zoe (800). In this SORT call, 4 refers to the fourth column of the filtered result, and FALSE requests descending order. The formula is useful for dashboards because it produces a changing, sorted output without changing the source table.

A common related need is replacing the no-match message. If a condition finds nothing, Sheets returns #N/A. With the same table layout, this formula displays a readable message instead:

=IFNA(FILTER(A2:D6,B2:B6="Central"),"No matching records")

Since no row has Central in the Team column, the result is the single message No matching records. IFNA handles the no-match error without hiding every possible error; that is often preferable to wrapping the formula in a broad error handler that could conceal a misspelled range or another problem.

Common Errors and How to Fix Them

When a FILTER formula does not work, check alignment and output space before rewriting the logic. These are the most common causes:

#N/A: No entries met the condition. Check spelling, spaces, comparison values, and whether the source actually contains a match. Use IFNA if a friendly no-match message is appropriate.
Range size mismatch: A condition range has a different number of rows or columns from the dimension being filtered. For A2:D6, a row condition should cover five rows, such as B2:B6.
Array could not expand: Cells where the result needs to appear are occupied, or another layout constraint is blocking the output. Clear the obstructing cells or move the formula to an open area.
Unexpected missing matches: The values may contain extra spaces, be stored as different data types, or use a different spelling. Check the underlying cell contents and make sure number or date comparisons use compatible values.

Also check whether the formula is placed inside the very range it needs to spill into. Keeping the results in a separate area or on a report sheet avoids collisions with the source data and makes it easier to add new records.

FILTER Function vs. the Sheet Filter Tool

The function and the built-in filter tool share a name but do different jobs. A FILTER formula returns a separate, formula-driven result. The sheet filter tool lets a person temporarily hide source rows that do not meet selected conditions. In Google Sheets, the menu path for applying a standard filter is Data > Create a filter. Filter views, available from the Data menu, let users save or use a view without changing what other collaborators see in the same way as a shared standard filter.

Use the formula when you need a live output range for a report, a separate list, or further calculations. Use the menu filter when you want to inspect the original data and interactively hide rows. Neither approach is automatically a substitute for the other: formulas leave source rows visible, while a view does not create a new output table.

How Google Sheets Differs from Excel

Do not assume that the two spreadsheet programs use identical FILTER arguments. Google Sheets uses one condition argument followed by optional additional conditions, as in =FILTER(range,condition1,condition2). Excel for Microsoft 365 uses a different function signature, including an optional if_empty argument. That means an Excel formula intended to supply a custom no-match value cannot necessarily be copied unchanged into Sheets; in Sheets, a common approach is to wrap FILTER in IFNA.

In Google Sheets in a web browser, formulas normally use commas between arguments, as in the examples here. Depending on the spreadsheet’s locale settings, the argument separator may instead be a semicolon. If a formula is rejected despite correct ranges and logic, check the spreadsheet locale under File > Settings and use the separator expected by that locale.

Frequently Asked Questions

What is the basic Google Sheets FILTER formula?
Use =FILTER(range,condition1) to return entries that meet a condition. Add more condition arguments when every condition must be true for a row or column to be included.

How do I use OR conditions in FILTER?
Combine Boolean tests with the plus operator inside the condition expression, such as (B2:B6="East")+(D2:D6>1000). A row is included if either test is true. Use parentheses around each comparison to keep the logic readable.

Why does FILTER show #N/A?
In Google Sheets, #N/A commonly means the filter found no matches. Confirm that the condition range is aligned and that the values really match, or use IFNA to show a message when no rows qualify.

Can FILTER return only one column?
Yes. Set the source range to that column, such as A2:A6, and keep the condition range aligned to the same rows. The formula then returns only the matching values from that column.

Does FILTER change or hide the original data?
No. It produces a separate result array and leaves the source records in place. To hide rows in the original sheet for interactive inspection, use Data > Create a filter instead.

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.