Filtering Data With FILTER

The FILTER function returns only the rows of a range that meet a condition you set. Instead of manually hiding rows or copying matches, FILTER spills the matching rows automatically.

It is dynamic: when your data changes, the filtered output updates instantly. This makes it perfect for live reports that always show the current matches.

FILTER takes up to three arguments:

=FILTER(array, include, [if_empty])

• array is the range you want to return.
• include is a logical test that produces TRUE or FALSE for each row.
• if_empty is an optional value to show when nothing matches.

The include test must be the same height as the array so each row gets a TRUE or FALSE.

Say A2:A10 holds salesperson names and B2:B10 holds their region. To list only the names in the East region:

=FILTER(A2:A10, B2:B10="East")

The test B2:B10="East" produces a column of TRUE and FALSE values. FILTER keeps the rows where the result is TRUE and spills them.

The array can be more than one column wide. To return both the name and the sales amount for the East region, point the array at the whole block:

=FILTER(A2:C10, B2:B10="East")

FILTER returns every column of the matching rows, spilling a small table. The include test still looks at just the single condition column.

Conditions are not limited to text. To return all rows where sales in C2:C10 are above 500:

=FILTER(A2:C10, C2:C10>500)

Comparison operators like greater-than, less-than, and not-equal all work inside the include argument, just as they do in a normal logical test.

To require two conditions at once, multiply the tests together. Multiplication acts like AND because TRUE is 1 and FALSE is 0, so a row passes only when both are 1.

=FILTER(A2:C10, (B2:B10="East")*(C2:C10>500))

This returns East-region rows that also have sales above 500. Wrap each test in parentheses.

To pass when either condition is true, add the tests together. Addition acts like OR because the sum is at least 1 whenever any test is TRUE.

=FILTER(A2:C10, (B2:B10="East")+(B2:B10="West"))

This returns rows from either the East or the West region. A row scoring 1 or 2 is kept; a row scoring 0 is dropped.

If no row meets the condition, FILTER returns a #CALC! error by default. The optional third argument prevents that by giving a friendly message instead:

=FILTER(A2:C10, B2:B10="South", "No matches found")

If the South region does not exist, the cell shows the text instead of an error. Always add an if_empty value in real reports.

For an interactive report, compare against a cell instead of a hard-coded value. If E1 holds the region a user picks:

=FILTER(A2:C10, B2:B10=E1, "No matches")

Change E1 to West and the spilled list instantly refreshes to West rows. This is the foundation of a dashboard driven by a dropdown.

FILTER returns matches in their original order. To sort them, wrap FILTER inside SORT. To show East-region rows sorted by sales descending:

=SORT(FILTER(A2:C10, B2:B10="East"), 3, -1)

SORT orders the spilled table by its third column, with -1 meaning descending. Combining spill functions like this is common and very powerful.

FILTER works in both Excel 365 and Google Sheets, with nearly identical syntax. In Google Sheets you can pass several conditions as separate arguments rather than multiplying them:

=FILTER(A2:C10, B2:B10="East", C2:C10>500)

Sheets treats each extra argument as an AND condition. In Excel you stick with the multiply-for-AND, add-for-OR approach inside the single include argument.

Test your understanding of FILTER.

You learned to return only matching rows with FILTER:

• Syntax is =FILTER(array, include, [if_empty]).
• The include test must match the array height.
• Multiply tests for AND, add them for OR.
• Add an if_empty message to avoid #CALC! errors.
• Wrap FILTER in SORT to order the results.

Next, you will extract a distinct list of values with UNIQUE.