Google Sheets FILTER Function: Practical Examples and Setup

In Google Sheets, use FILTER to show only rows that meet one or more conditions. Enter a formula such as =FILTER(A2:D100,D2:D100="Open") in an empty cell to return rows whose status is Open. Before you start, identify the data range and the column or columns that contain the conditions; the formula returns a live result that updates when the source data changes.

What You’ll Need

A Google account and a spreadsheet open in Google Sheets in a web browser.
A table with consistent columns and a header row. For the examples below, assume column A contains order IDs, B contains regions, C contains amounts, and D contains statuses.
A blank area for the results. FILTER spills matching rows into nearby cells, so the output area must be clear.

These examples use rows 2 through 100 for data and row 1 for headings. Adjust the ranges to fit your sheet. Google Sheets uses commas between formula arguments in the examples; some spreadsheet locales require semicolons instead.

Step-by-Step

Check and organize the source data. Put one record on each row, with a heading at the top of each column. Avoid merged cells in the data area, and keep values in a column consistent: for example, enter amounts as numbers rather than mixing numbers with text such as “$500”. Check that the rows you intend to filter are inside your chosen range. For a simple example, an order might have an ID in A, a region in B, an amount in C, and a status in D.
Choose where the filtered results should appear. Click an empty cell outside the source table, such as F1. The result will include the columns in the range you specify, not just the column used for the condition. Leave enough empty columns and rows for every matching record. If an existing value or merged cell blocks the output, Sheets cannot expand the result.
Filter by one condition. With order details in A2:D100 and statuses in D2:D100, enter this in F1 to show open orders:

=FILTER(A2:D100,D2:D100="Open")

The first argument is the range to return. The second argument is a condition that evaluates each corresponding row as true or false. The condition range must cover the same number of rows as the data range. The result contains all four columns for each row whose D-column value exactly matches Open. Text comparisons are not case-sensitive in Google Sheets, but extra spaces or different wording can still prevent a match.

Add more conditions for an AND result. Separate conditions with commas when a row must meet every condition. With order details in A2:D100, regions in B2:B100, and statuses in D2:D100, use this formula to show open orders from the West:

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

Both checks must be true for a row to appear. You can add further conditions, such as an amount threshold, as long as each condition range lines up with the same source rows. For example, with amounts in C2:C100, add C2:C100>=500 as another comma-separated condition to require an amount of at least 500.

Use OR logic when either condition is acceptable. To return orders from either the West or East region, combine the two tests with a plus sign. With regions in B2:B100 and the full order table in A2:D100, enter:

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

The plus sign works as OR here: a row is included if either test is true. Keep each test in parentheses so the intended comparisons are clear. For a condition that must meet two requirements at once, multiplication can represent AND; separate condition arguments, as in the previous step, are often easier to read.

Filter with a number or date condition. Comparisons can test numeric values as well as text. With amounts in C2:C100 and the full order table in A2:D100, this returns rows with amounts of 500 or more:

=FILTER(A2:D100,C2:C100>=500)

For dates, make sure the source cells contain actual date values, not text that only looks like a date. With order dates in A2:A100 and details in A2:D100, you could compare against a date entered in F1 using A2:A100>=F1 as the condition. Format F1 as a date and enter a valid date value there; this makes the cutoff easy to change without editing the formula.

Handle the no-match case. If no rows meet the condition, Google Sheets returns #N/A. To show a helpful message instead, wrap the filter in IFNA. With order details in A2:D100 and statuses in D2:D100, enter:

=IFNA(FILTER(A2:D100,D2:D100="Open"),"No matching rows")

This catches the no-match result while allowing other formula errors to remain visible. Put the formula in the same clear output area as before. If you prefer to see #N/A while setting up the condition, leave the formula unwrapped until you have confirmed it works.

Review and reuse the results. Confirm that the returned rows meet the criteria and that the selected output range includes all columns you need. FILTER creates a live result, rather than a separate editable copy: changes to the source data or condition cells can change the displayed rows. If you need a static snapshot, copy the results and use Edit > Paste special > Values only in the destination area.

Google Sheets also has a menu-based filter: select the table and choose Data > Create a filter. That lets you hide nonmatching rows in the original table and is useful for quick inspection. The FILTER function is better when you want a separate, formula-driven result or want to combine conditions in a reusable cell. Filter views are available through Data > Filter views > Create new filter view when you want a saved view without changing what other collaborators see.

Common Mistakes

Using ranges of different sizes. The rows in the returned range and every condition range must correspond one-to-one. If the data range is A2:D100 but a condition is D2:D99, the sizes do not match and FILTER returns an error. Keep start and end rows aligned.
Including the header in only one range. A condition range that starts at row 1 while the returned range starts at row 2 is misaligned. Usually keep headings out of the formula and place them separately above the output, or include matching dimensions consistently.
Expecting one condition argument to mean OR. Multiple comma-separated condition arguments require all conditions to be true. Use a plus between parenthesized tests for OR logic, and check the result against a few known records.
Typing a criterion that does not match the cell. “Open ” with a trailing space is not the same text as “Open”. Inconsistent labels, invisible spaces, or numbers stored as text can also lead to unexpected results. Inspect a source cell and standardize the values when possible.
Putting the formula inside the source range or blocking its output. The result needs room to expand. Place it outside the table and clear any cells where the returned rows and columns would appear.
Assuming the output is an editable copy. A formula result is controlled by the formula and source data. Edit the original records or criteria to change the result; copy and paste values if you need a standalone snapshot.

Tips

Use a visible criteria cell. Instead of typing a fixed label into the formula, refer to a cell users can change. With details in A2:D100, statuses in D2:D100, and a chosen status in F1, use =FILTER(A2:D100,D2:D100=F1). Add a label such as “Status to show” next to F1 so the control is obvious.
Keep formulas readable. Put each test on its own line while editing, if helpful, and use clear headings near the result. For complicated logic, first test each condition in a spare column or in a smaller sample of the data.
Use bounded ranges for a large sheet. A range such as A2:D1000 is easier to inspect than entire columns and can reduce unnecessary calculation. Increase the end row when the source table grows, or choose a range sized for expected additions.
Plan for changing data. If records are frequently added, set the range end far enough ahead and keep the unused rows within the source range empty. Avoid including unrelated notes beneath the table, since they could be treated as records.
Distinguish a formula filter from a view filter. Choose FILTER to build a separate result that can feed another calculation. Choose Data > Create a filter for interactively showing and hiding rows in the original table. Choose a filter view when collaborators need their own saved view.
Remember the Excel difference. Excel for Microsoft 365 also has a FILTER function, but its arguments and empty-result behavior differ: Excel includes an optional if_empty argument, while Google Sheets uses separate condition arguments and returns #N/A when there are no matches. Do not copy a formula between the apps without checking its syntax and behavior.

Troubleshooting

The formula shows #N/A. In Google Sheets, this usually means no rows matched the conditions. Check spelling, spaces, the selected status or region, and whether values are stored as text or numbers. Temporarily simplify the formula to one condition to locate the mismatch. If no matches are a normal outcome, use the IFNA approach above to show a message.

The formula reports mismatched range sizes. Compare the first and last row of the return range with every condition range. For example, A2:D100 pairs with B2:B100, not B1:B100 or B2:B99. If you inserted or deleted rows recently, adjust the ranges together.

The result will not expand or shows an expansion error. Clear cells beneath and to the right of the formula where the returned table should appear. Check for merged cells in that area. If you need existing information there, move the formula to a larger blank area rather than deleting data you want to keep.

Some expected records are missing. Check whether the source contains extra spaces, inconsistent punctuation, unexpected capitalization, text-formatted numbers, or date text rather than true dates. Compare the displayed value with the exact criterion, and test a condition against one known row. If a date cutoff is held in a cell, verify that the cell is recognized as a date.

The output changes unexpectedly. FILTER recalculates as its source data and criteria change. Check whether a collaborator edited the source or criteria cell, and confirm that the formula references the intended range. If you want a fixed report, copy the output and paste values only into a separate location.

Frequently Asked Questions

Can FILTER return more than one matching row?
Yes. It returns every row that meets the condition, not just the first match. The output expands to fit the number of matching rows and the columns in the return range.

Can I filter by several conditions at once?
Yes. Add each condition as another argument to require all of them. Use plus signs between parenthesized tests when any one of several alternatives should qualify.

How do I include the column headings in the result?
Keep the headings in a separate row above the formula output, or include a matching header row through a separate setup. The simplest approach is to type or reference the headings above the result and filter only the data rows.

Why does FILTER show #N/A when nothing matches?
That is Google Sheets’ no-match result. Confirm that the criteria are correct, or wrap FILTER in IFNA to display a message such as “No matching rows.”

Does FILTER change or delete the original data?
No. The function displays matching records in its output area and leaves the source table intact. Editing the source can update the result, but the formula does not remove hidden or nonmatching source rows.

Can I use FILTER on a phone or in Excel?
FILTER is available in Google Sheets on the web and in the Sheets mobile app, though entering and managing larger formulas is often easier in a browser. Excel has a FILTER function too, but its syntax and no-match behavior differ, so check the formula before transferring it.

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.