How to Use the FILTER Function in Excel and Google Sheets

The FILTER function pulls the rows that meet a condition out of a table and writes them somewhere else, while leaving the original data untouched. In Excel the pattern is =FILTER(array, include, [if_empty]). In Google Sheets it is =FILTER(range, condition1, [condition2, ...]). Both return a block of results that spills into the cells below and to the right of the formula.

That makes it different from the Filter button on the Data tab, which hides rows in place. With a formula, the result updates by itself when the source changes, and you can point other formulas at it.

1. A sample table

The examples below use this small list, in A2:C7 with headers in row 1.

2. One condition

To list every row where the product is Apples:

=FILTER(A2:C7, B2:B7="Apples")

The second argument is a column of TRUE and FALSE values, one per row. Microsoft describes it as a Boolean array whose height or width matches the array being filtered. Rows with TRUE come through. Here that is four rows: East 30, West 12, South 25 and East 41.

The condition range must be exactly as tall as the data range. Google's documentation states that condition arguments must have exactly the same length as the range. Keep the condition column the same size as the table, and extend both together when you add data.

3. Two conditions: AND

In Excel, multiply two conditions to require both. Microsoft's example uses the multiplication operator for an AND:

=FILTER(A2:C7, (B2:B7="Apples")*(A2:A7="East"))

Only two rows pass: East Apples 30 and East Apples 41. A row needs a 1 from both tests to produce 1 in the product, so a single FALSE removes it.

In Google Sheets, extra conditions go in as additional arguments, for example =FILTER(A2:C7, B2:B7="Apples", A2:A7="East"). Each extra condition is another TRUE/FALSE column, and the documentation's own sample uses two of them.

4. Two conditions: OR

In Excel, add the conditions instead of multiplying them. Microsoft's example for OR uses the addition operator:

=FILTER(A2:C7, (B2:B7="Apples")+(A2:A7="East"))

A row passes if either test is TRUE, so the sum is 1 or 2. Five of the six rows come back: all four Apples rows plus East Pears. Only West Pears is dropped, because it is neither Apples nor East. That is five rows, against two for the AND version, which is the quickest way to check you picked the right operator.

5. Sorting the result

Wrap FILTER in SORT to order what comes back. Microsoft shows this form, where the number is the column to sort by and -1 means descending:

=SORT(FILTER(A2:C7, (B2:B7="Apples")*(A2:A7="East")), 3, -1)

The two East Apples rows come back as 41 first, then 30. For the OR version the five rows sort as 41, 30, 25, 18, 12.

6. What happens when nothing matches

This is where the two apps differ, and it is the most common reason a FILTER formula looks broken.

• Excel returns a #CALC! error when no rows match, because it does not support empty arrays. The third argument fixes that: =FILTER(A2:C7, B2:B7="Grapes", "No matches") shows the text instead.
• Google Sheets returns #N/A when no values satisfy the conditions. There is no third argument for this, so wrap the formula: =IFNA(FILTER(A2:C7, B2:B7="Grapes"), "No matches").

Both help pages are explicit about these behaviors. If you wrap a whole FILTER in IFERROR to cover the empty case, you also hide real mistakes like a mismatched range length, so the narrower options above are safer.

7. Spilling

A FILTER result spills into neighboring cells. Excel's documentation says it creates the right-sized array when you press Enter, so keep the area below and beside the formula free for the result.

If the source data is an Excel table and you use structured references, the result resizes as rows are added or removed. Microsoft also notes that dynamic arrays between workbooks only work while both files are open; close the source file and the linked formula returns #REF! on refresh.

Google adds one more rule: FILTER works on either rows or columns at one time. To filter both, use the output of one FILTER as the range of another.

8. When to use something else

If you only need a total for a condition, SUMIFS or COUNTIFS return one number without building a list. FILTER earns its place when you need the matching rows themselves, for a summary tab, a picklist, or a printout for one region.

Behavior above follows the Microsoft and Google help pages as read in October 2026.

9. Key takeaways

• FILTER copies matching rows to a new place and leaves the source alone.
• Excel: multiply conditions for AND, add them for OR. Google Sheets: extra conditions are extra arguments.
• Condition ranges must be the same length as the data range.
• No match gives #CALC! in Excel (use the third argument) and #N/A in Google Sheets (wrap with IFNA).
• Leave room below and beside the formula for the result to spill.