Excel FILTER Function Examples: Formulas for Common Tasks

Excel’s FILTER function returns only the rows or columns that meet criteria you choose, so your results can update as the source data changes.

These examples show how to filter a table by one condition, combine rules, and handle cases where no records match.

FILTER function syntax

Use FILTER with a source range, a condition that evaluates to TRUE or FALSE, and an optional value for when nothing matches:

=FILTER(array, include, [if_empty])

• array is the range or array of values to return.
• include is a Boolean test with a compatible number of rows or columns.
• if_empty is optional text or another value to return when the condition finds no matches.

For example, if A2:C6 contains Name, Region, and Sales, a condition based on B2:B6 returns matching rows from all three columns.

FILTER produces a dynamic array, so the results spill into nearby empty cells.

Sample data for the examples

The formulas below use this small dataset in cells A1:C6:

Filter rows by one condition

To return every record from the East region, enter:

=FILTER(A2:C6,B2:B6="East","No matches")

The expected result contains Ana and Chloe, including their sales values.

Text criteria need quotation marks.

A cell reference can make the condition easier to change: if E2 contains East, use =FILTER(A2:C6,B2:B6=E2,"No matches").

Filter numbers using greater than or equal to

To find sales of at least 400, use:

=FILTER(A2:C6,C2:C6>=400,"No matches")

The expected rows are Ana, Dev, and Eli.

The comparison operator belongs inside the formula; the HTML representation >= displays as the Excel operator >=.

You can also put the threshold in a cell, such as E3, and write =FILTER(A2:C6,C2:C6>=E3,"No matches").

Combine conditions with AND logic

Multiply conditions to return rows that satisfy every rule.

To find East region sales of at least 300:

=FILTER(A2:C6,(B2:B6="East")*(C2:C6>=300),"No matches")

Both tests must be TRUE for a row to be included.

With the sample data, Ana matches; Chloe is excluded because her sales are below 300.

Combine conditions with OR logic

Add conditions to return rows that satisfy at least one rule.

This example returns records from either East or West:

=FILTER(A2:C6,(B2:B6="East")+(B2:B6="West"),"No matches")

The expected result includes Ana, Ben, Chloe, and Eli.

Addition represents OR logic in this array condition, while multiplication represents AND logic.

Filter using a partial text match

FILTER does not have a separate “contains” argument, but SEARCH can test whether text appears within a cell.

To return rows where the name contains the letter “a,” use:

=FILTER(A2:C6,ISNUMBER(SEARCH("a",A2:A6)),"No matches")

SEARCH is not case-sensitive, so names with either uppercase or lowercase “a” can match.

For case-sensitive matching, consider FIND instead.

If your source cells may contain errors, those errors can affect the condition and should be handled separately.

Return only selected columns

FILTER can return a single column by using that column as the array.

To list names in the West region, enter:

=FILTER(A2:A6,B2:B6="West","No matches")

For a nonadjacent set of columns, combine the desired ranges into an array first.

In Microsoft 365, for example, =FILTER(CHOOSE({1,2},A2:A6,C2:C6),B2:B6="West","No matches") returns Name and Sales for matching records.

Array constants can use different separators in some regional settings, so check your Excel list and array separators if the formula is rejected.

Filter and sort the results

Wrap FILTER in SORT to order the matching records.

To show all West region rows sorted by Sales from lowest to highest:

=SORT(FILTER(A2:C6,B2:B6="West","No matches"),3,1)

The second argument, 3, sorts by the third column of the returned array; 1 means ascending order.

Use -1 for descending order.

If the filtered result may be empty, consider how the text fallback interacts with SORT, since it is not a table of three columns.

Handle no matching rows

Without the third argument, FILTER returns a #CALC! error when there are no matches.

Supply a readable fallback, such as "No matching records", or return a blank string with "".

For example:

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

The fallback is returned as a single value; it does not create an empty table with the same dimensions as the source range.

Formulas that expect a specific number of columns may need separate handling for the no-match case.

Common FILTER errors and edge cases

• #SPILL!: One or more cells in the spill area are occupied, merged, or otherwise unavailable.
  
  Clear the output area and check for merged cells.
• #VALUE!: The include condition may have incompatible dimensions.
  
  For a vertical source with five rows, the condition should generally have five corresponding entries.
• #CALC!: No rows matched and no if_empty value was supplied.
• Unexpected blanks or errors: Blank source cells can satisfy some conditions, and errors in the source or condition can propagate.
  
  Narrow the source range or add checks such as ISNUMBER where appropriate.
• Changing source size: Fixed ranges like A2:C6 do not automatically include rows added below row 6.
  
  Convert the data to an Excel Table and use structured references, or adjust the ranges as the dataset grows.

Excel version compatibility

FILTER is a dynamic array function available in Microsoft 365 and Excel 2021 or later, including supported Excel for the web versions.

Excel 2019 and earlier generally do not support it as a native worksheet function.

In older versions, use alternatives such as AutoFilter, Advanced Filter, or a combination of INDEX and helper formulas.

Google Sheets also has a FILTER function, but its argument conventions and behavior can differ, so verify formulas when moving between spreadsheet apps.