Google Sheets FILTER Function: A Practical Tutorial

In Google Sheets, use the FILTER function to return only the rows or columns that meet conditions. If your records are in A2:D100 and the region is in column B, this formula returns the East records: =FILTER(A2:D100,B2:B100="East").

What the FILTER function does

FILTER creates a separate, live result from data already in your sheet. It does not hide rows in the original table or change their order. Instead, it displays matching values in the formula cell and the cells below or beside it. When the source data changes, the results update automatically.

This is useful for building a focused list without deleting, copying, or repeatedly sorting the source data. For example, you can display open orders, records for one region, or transactions inside a date range. The source table remains intact, so the same data can support several different filtered views.

FILTER is different from the filter controls in the Sheets menu. A menu filter hides nonmatching rows in the original table for people viewing or editing it. A FILTER formula produces a new result area that can feed another formula, chart, or report. Choose based on whether you want to interact with the original table or create a reusable output.

How the formula works

FILTER takes a range to return and one or more conditions that say which rows or columns to keep. The first condition must correspond to the direction of the data: for a row-by-row table, it usually has one TRUE or FALSE result for every source row. The condition range must have the same number of rows as the data range.

Here is a small example table layout: A1:D1 contains the headers Name, Region, Status, and Sales. Rows 2–6 contain Ana, East, Open, 120; Ben, West, Closed, 80; Cara, East, Open, 150; Dev, North, Open, 90; and Eli, West, Open, 110. With those records in A2:D6, filter for the East region using:

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

The result contains Ana’s and Cara’s full rows, including their status and sales values. The header row is not included because the formula starts at row 2. If you want a heading above the output, type one in a cell above the formula, or include a separate header as part of a larger report design.

The condition can also compare numbers, dates, or cell values. For example, with sales in D2:D6, the condition D2:D6>=100 keeps rows whose sales are at least 100. A condition can refer to a cell instead of a fixed value, which makes it easy to change the filter without editing the formula.

Filter by multiple conditions

Put additional conditions after the first one, separated by commas. In FILTER, multiple condition arguments are combined as AND: a row must satisfy every condition to appear. With the sample data in A2:D6, the following returns open records with sales of at least 100:

=FILTER(A2:D6,C2:C6="Open",D2:D6>=100)

The result is Ana, Cara, and Eli. Dev is open but has sales below 100; Ben has sufficient data in the table but is closed. Each condition range has the same five rows as the source range. A mismatch in range size is a common cause of errors.

For OR logic, add TRUE/FALSE conditions inside parentheses. With the same table, this returns records from either East or West:

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

In this kind of array condition, adding the comparisons makes a row qualify if at least one comparison is true. The result includes Ana, Ben, Cara, and Eli, but not Dev. Parentheses matter: they keep each comparison grouped before the addition is evaluated. If you need a more complex mix of AND and OR, build and test the condition carefully against a few known records.

Use a cell to control the filter

A cell reference makes a report interactive. Suppose F1 contains a region name and the data is in A2:D100, with region values in B2:B100. Use:

=FILTER(A2:D100,B2:B100=F1)

Changing F1 from East to West changes the output without changing the formula. You can type the value directly or create a dropdown with Data validation so a reader can choose from approved region names. A dropdown also helps prevent an accidental extra space or spelling difference from producing an empty result.

The same approach works for date limits. If dates are in C2:C100, the start date is in F1, and the end date is in G1, use:

=FILTER(A2:D100,C2:C100>=F1,C2:C100<=G1)

This keeps rows whose dates fall on or after the start date and on or before the end date. Enter actual date values in F1 and G1, not text that merely looks like a date. If the source column contains timestamps with times as well as dates, the end-date condition may exclude entries later in the final day; in that case, account for the time component in the criteria.

Handle no matches and organize results

When no records meet the conditions, FILTER returns a #N/A error. That usually means the formula ran but found nothing, rather than that the function is broken. If a friendly message is preferable, wrap the formula in IFNA. With the sample table in A2:D6, this looks for the nonexistent South region:

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

The displayed result is No matching records. IFNA catches the no-match #N/A result while allowing other problems, such as a range-size error, to remain visible. That distinction makes it easier to diagnose a genuinely broken formula.

Filtered results spill into neighboring cells automatically. Leave enough blank space below and to the right of the formula. If another value or formula occupies a cell the results need, Sheets reports that the array result could not expand. Move the blocking content or put the FILTER formula in a clear area rather than deleting data blindly.

You can sort the matching rows by wrapping FILTER in SORT. With the example records in A2:D6, the following sorts East-region rows by the fourth column, Sales, from highest to lowest:

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

The result lists Cara before Ana because 150 is greater than 120. SORT operates on the filtered result; it does not rearrange the original table. For a report that should stay in a particular order, this combination is often clearer than sorting the source data manually.

Common problems and practical checks

Different range sizes: The source and condition ranges need matching dimensions. If the source is A2:D100, a row condition such as B2:B100 is aligned; B2:B99 is not.
Headers included in the condition: Starting a condition at row 1 while the data starts at row 2 can misalign the ranges. Use the same starting row for both.
No exact text match: Extra spaces, inconsistent spelling, or a different value can leave no matches. Check the source cells or use a dropdown for criteria.
Output area is occupied: A spill result needs empty cells. Move the formula or clear only the cells that are safe to clear.
Number or date stored as text: A comparison such as greater than 100 may not behave as intended if the values are text. Check the cell formatting and underlying values.
Formula separators differ: The examples use commas. Depending on spreadsheet locale settings, Sheets may require semicolons between function arguments. Use the separator accepted by your file.

Text comparisons in Sheets are generally not case-sensitive, so a simple comparison usually does not require a separate lowercase conversion. For partial text matching, SEARCH can locate a word or phrase within a cell. For example, if region descriptions are in B2:B6, the formula below keeps rows containing the word east:

=FILTER(A2:D6,ISNUMBER(SEARCH("east",B2:B6)))

SEARCH returns a position when it finds the text and an error when it does not; ISNUMBER turns those results into TRUE or FALSE values for FILTER. This is helpful for longer descriptions, but exact comparisons are generally safer when the column is intended to contain a fixed category.

FILTER formulas versus menu filters and Excel

To filter the source table interactively in Google Sheets, select the data and choose Data > Create a filter. The column controls let a user show or hide values in that table. This method is convenient for exploring records, but it is not a separate formula-driven result and can affect what collaborators see in the same sheet.

Use FILTER when you need a live output in a different area, want to combine conditions with other calculations, or need a report to update as records change. Use the menu filter when someone needs to inspect the original table and temporarily hide rows. For collaborative analysis, consider whether individual viewers need different views before choosing a shared filter on the source.

Google Sheets and Excel both have a function called FILTER, but their argument conventions are not identical. Excel’s FILTER includes an optional argument for what to display when there are no results. Google Sheets instead returns #N/A for no matches, so the IFNA wrapper shown above is a common way to provide a fallback message. Do not assume a formula copied between the programs will handle empty results in exactly the same way.

Frequently Asked Questions

Why does FILTER return #N/A?
Usually, none of the rows meet the condition. Check the criteria value and source data, then use IFNA if you want a message instead of the error. A mismatched range size causes a different error.

Can FILTER return only one column?
Yes. Use the column you want as the first range and a same-height condition range. For example, if names are in A2:A6 and sales are in D2:D6, use =FILTER(A2:A6,D2:D6>=100) to return names for records with sales of at least 100. With the example data, the results are Ana, Cara, and Eli.

Does FILTER change or delete the original data?
No. It displays matching values in the formula’s output area and leaves the source rows in place. Editing or deleting the source data can change the result, but the filter formula itself does not remove records.

How do I filter for either of two values?
Use OR logic by adding parenthesized comparisons, such as =FILTER(A2:D6,(B2:B6="East")+(B2:B6="West")). Check that each comparison covers the same rows as the data range.

Why will the results not expand?
One or more cells in the required output area are occupied. Move the formula to a clear region or remove the blocking content only if it is safe to do so. FILTER needs room for every matching row and column.

Choosing a reliable setup

Start with a clearly structured source table: one header row, consistent values in each column, and no merged cells inside the data. Keep the FILTER formula outside that source range so its results cannot overwrite the records it depends on. When possible, use cell references for criteria, label the output, and leave generous room for the result to grow as new rows are added.

For a quick test, verify one condition against a few records you can check manually, then add further conditions. Confirm the ranges line up, decide how the sheet should behave when nothing matches, and test the formula after changing the criterion. These small checks make a FILTER-based report easier to maintain and much less likely to confuse its next reader.

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.