Google Sheets FILTER Function: Basics and Examples

Google Sheets’ FILTER function returns only the rows or columns that meet conditions you specify, while leaving the source data in place. With records in A2:D100 and each record’s region in column B, =FILTER(A2:D100, B2:B100="West") returns the rows for the West region; the result updates when the source data changes.

What the FILTER function does

FILTER creates a formula-driven view of matching data in another part of the spreadsheet. It does not hide nonmatching source rows or remove them. That makes it useful for reports, summaries, and work areas where you want to show a subset without changing the original table.

The function can filter either rows or columns. Most people use it to return complete rows from a table based on one or more fields—for example, orders from one region, tasks that are still open, or products above a price threshold. The returned results occupy cells starting at the formula cell and expand as needed.

FILTER is a Google Sheets function, not a command in the Data menu. It works in the Sheets web browser, and changes to qualifying source values are reflected in the results. The formula returns cell values; it does not reproduce the source table’s formatting as a complete duplicate, so you may need to format the result area separately.

Syntax and how conditions work

The syntax is FILTER(range, condition1, [condition2, ...]). The range is the data to return. Each condition is a row or column of TRUE/FALSE tests that determines which part of the range qualifies. Square brackets in the syntax indicate an optional argument; do not type those brackets into a formula.

range: The cells to return. It can contain multiple columns if you want matching records to stay together.
condition1: A test such as a region equaling West, a number exceeding a limit, or a cell not being blank.
Additional conditions: Optional tests that must also be true for a row or column to appear.

For row filtering, each condition must have the same number of rows as the range. For column filtering, each condition must have the same number of columns. A condition can be a range comparison, such as a column being equal to a text value, rather than a single TRUE or FALSE value. Keep the condition aligned with the records it tests.

Basic example: filter rows by one condition

Suppose a sales table has customer names in column A, regions in B, sales amounts in C, and order status in D. The data starts on row 2, with headers in row 1. To show all four fields for West-region orders, use this formula in an empty area of the sheet:

With records in A2:D100 and regions in B2:B100, enter =FILTER(A2:D100, B2:B100="West"). It returns every matching row across columns A through D. If there are 12 West records, the result is 12 rows by 4 columns.

To filter on a numeric threshold instead, use the sales column as the condition. With the same table layout and sales amounts in C2:C100, enter =FILTER(A2:D100, C2:C100>5000). This returns records with sales greater than 5,000; a value equal to 5,000 does not qualify because the operator is greater than, not greater than or equal to.

Common comparison operators include = for equal to, <> for not equal to, > for greater than, >= for greater than or equal to, < for less than, and <= for less than or equal to. Text values and text conditions need straight double quotation marks in formulas.

Combine conditions with AND or OR logic

Separate condition arguments act like AND: a row must pass every condition to appear. This is convenient for questions such as “show West-region orders with sales above 5,000.” With the sales table in A2:D100, region in B2:B100, and sales in C2:C100, use =FILTER(A2:D100, B2:B100="West", C2:C100>5000). The result includes only rows meeting both tests.

To match either of two alternatives, combine Boolean tests with addition. In Google Sheets, TRUE and FALSE values in array calculations behave as 1 and 0, so a positive sum means at least one test passed. With regions in B2:B100, this formula returns rows for either West or East:

With the same A2:D100 table and region values in B2:B100, enter =FILTER(A2:D100, (B2:B100="West")+(B2:B100="East")). Parentheses make each comparison clear and ensure the tests are combined as intended. For more complicated rules, test a few records manually so you can confirm that the logic matches the question.

A condition can also exclude incomplete records. With names in A2:A100 and regions in B2:B100, =FILTER(A2:D100, A2:A100<>"", B2:B100="West") returns West-region records only when the name cell is not blank. This can prevent empty rows within a larger input range from appearing in the output.

Filter columns instead of rows

FILTER can also select columns when the condition runs horizontally across the range. This is useful when a worksheet has dates, months, or categories as column headings and you need to display only certain columns. In that case, the condition must have one test for each column in the range, rather than one test for each row.

For example, imagine a small matrix in B2:E5 with month names in B1:E1. A horizontal condition based on those headings can return the columns whose month matches a test. The same principle applies: the range being returned and the condition must line up along the dimension being filtered. If you need to filter rows and columns at once, it is usually clearer to nest functions or create a separate step for each direction rather than trying to pass mismatched conditions to one FILTER.

Handle no matches and common errors

When no rows or columns meet the conditions, FILTER returns a #N/A error. That result means there were no matches; it does not necessarily mean the formula is broken. If a friendly message is more useful in a report, wrap the filter in IFNA.

With the data in A2:D100 and the region in B2:B100, use =IFNA(FILTER(A2:D100, B2:B100="West"), "No matching records"). When matches exist, the rows appear normally. When none exist, the formula displays “No matching records.” IFNA is preferable when you specifically want to handle the no-result error; wrapping every formula in a broad error handler can hide unrelated problems.

A common cause of other errors is a size mismatch. For example, a range running from row 2 to row 100 has 99 rows, so its row condition must also cover 99 rows. A condition that starts at row 1 or stops at row 99 will not line up. Check that each condition refers to the same records and the same number of rows or columns as the filtered dimension.

Also check for a blocked output area. The results need empty cells into which they can expand. Text, merged cells, or other content in that area can prevent the array result from displaying. Put the formula in a clear area, and avoid placing it inside the source range it is filtering, since that can create a circular reference.

Sort or further refine filtered results

FILTER returns matching records but does not promise to sort them in a new order; by default, results follow the order of the source range. To sort the matching results, nest FILTER inside SORT. The sort-column number is relative to the range being sorted, and the final argument controls ascending or descending order.

With the four-column table in A2:D100, sales in its third column, and West-region records selected by B2:B100, enter =SORT(FILTER(A2:D100, B2:B100="West"), 3, FALSE). It returns the filtered records ordered by sales from highest to lowest. In SORT, FALSE specifies descending order; TRUE specifies ascending order.

You can also combine FILTER with other functions when the goal is a distinct list or a calculation. For example, UNIQUE can remove repeated values from a filtered result. Keep in mind that each added function changes the shape or ordering of the output, so choose a formula that matches whether you need a full record list, a sorted report, or a set of distinct values.

FILTER formulas versus the Data menu filter

The formula and the built-in filter solve related but different problems. Use Data > Create a filter when you want to interactively hide rows in the original table, for example while inspecting or editing a shared dataset. The filter controls appear on the header row, and other people viewing the same sheet may be affected by the filter state.

Use Data > Filter views > Create new filter view when you want a saved filtering view that can be used without changing the view for everyone else. Filter views are useful for collaborative sheets and ad hoc exploration, but they do not create a separate formula output.

Choose the FILTER function when you need a live result in a designated area, want to use that result in another formula, or need a report that updates from conditions stored in cells. Choose a menu filter when you want to examine or edit the original records directly. Neither method deletes the rows it excludes.

Google Sheets FILTER compared with Excel

Excel also has a FILTER function in current versions, but its syntax is not identical to Google Sheets. Excel uses an include array and an optional value for the no-match case, while Google Sheets accepts one or more condition arguments. As a result, a Google Sheets formula that supplies separate conditions may need to be rewritten when moved to Excel.

For example, in Sheets, separate conditions naturally express AND logic. Excel formulas commonly combine Boolean arrays inside the include argument to express multiple tests. Do not assume that copying a formula between the two products will preserve its behavior, especially for multiple conditions and no-match handling. Check the function help for the spreadsheet application and locale in use.

In some Google Sheets locales, formulas use semicolons instead of commas between arguments. If a formula with otherwise valid syntax is rejected, the spreadsheet’s locale may be the reason. The function name and logic remain the same, but the argument separator can differ.

Frequently Asked Questions

Does FILTER change or delete my original data?

No. FILTER displays matching values in the formula’s output area and leaves the source records where they are. Editing a source value can change whether its row appears in the output, but the formula itself does not delete nonmatching records.

Why does FILTER show #N/A?

In Google Sheets, #N/A commonly means that no entries met the conditions. If that is a normal possibility, use IFNA to show a message instead. If you expected matches, check spelling, extra spaces, condition ranges, and whether numeric or date values are stored in the format you expect.

Can FILTER return more than one column?

Yes. Make the first argument a multi-column range, such as A2:D100, and use a condition aligned to its rows to return complete matching records. The output expands horizontally as well as vertically, so keep both dimensions of the destination area clear.

Can I use a cell as the filter condition?

Yes. Instead of typing a fixed value such as West into the condition, refer to a cell containing the selected region. This lets a reader change the selection without editing the formula. Make sure the referenced cell contains a value that matches the source data consistently.

Why is my FILTER result not expanding?

The output may be blocked by existing content or merged cells in the cells where it needs to appear. Move the formula to a blank area or clear the obstructing cells. Also check that the formula is not inside its own source range, which can cause a circular reference.

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.