# How to use the FILTER Function in Excel?

Last Updated on 25/02/2024

The FILTER function is one of the new dynamic array functions added to Excel in September 2018. You can find them in Excel Online and Microsoft 365.

## Presentation of the FILTER function in Excel

Filtering your data is one of the most common tools everyone uses with Excel.

Now, instead of selecting each time a new value to visualize the result in your spreadsheet, you do the same task but with a formula. This is the purpose of the FILTER function.

## FILTER function parameters

The FILTER function only needs 2 parameters to return a result.

1. The column(s) to return

You can select one or more than one column to return

2. The criteria

The second argument is the logical test to filter, or not, your data.

3. [Optional] Manage no result

If your criterion doesn't return any result, you can return a default value

=FILTER(array,criteria,no result)

## Example #1: Extract rows for the customer "Peter"

For example, we want to find information about the customer Peter.

1. First of all, we select all the data of our document (here the name of the Table )

=FILTER (tbl_Sales,

1. Select the column where is the value of your criterion
2. Indicate the value of your criterion

=FILTER(Tbl_Sales,Tbl_Sales[Customers]="Peter")

• All the rows where the customer name is "Peter" are returned by the function

## Example #2: Extract rows that are not "Apple"

The FILTER function accepts all types of logical testsThe FILTER function returns the rows when the result is TRUE.

For example, to select all fruits except apples, we just have to write the following formula

= FILTER(Tbl_Sales; Tbl_Sales[Fruits]<>"Apple")

## Example #3: Extract rows with a quantity greater than 20

Here, we wnat to select all the quantities greater than 20. The formula is:

= FILTER(Tbl_Sales; Tbl_Sales[Quantity]> 20)

## Example #4: Message when there is no result

If the FILTER function doesn't return any row, you can customize the message to avoid an error message.

=FILTER(Tbl_Sales;Tbl_Sales[Customers]="John";"No Result")

## Example #5: Return a column different of the criteria

It is important to know that the columns to return can be different from the columns as the filter. For instance, if we want to return only the fruits' names bought by Peter, we will write this simple formula.

=FILTER(Tbl_Sales[Fruits];Tbl_Sales[Customers]="Peter")

• We have selected for the result only the column of the Fruit's names
• The Customer column is used for the filter

# How to use the FILTER Function in Excel?

Last Updated on 25/02/2024

The FILTER function is one of the new dynamic array functions added to Excel in September 2018. You can find them in Excel Online and Microsoft 365.

## Presentation of the FILTER function in Excel

Filtering your data is one of the most common tools everyone uses with Excel.

Now, instead of selecting each time a new value to visualize the result in your spreadsheet, you do the same task but with a formula. This is the purpose of the FILTER function.

## FILTER function parameters

The FILTER function only needs 2 parameters to return a result.

1. The column(s) to return

You can select one or more than one column to return

2. The criteria

The second argument is the logical test to filter, or not, your data.

3. [Optional] Manage no result

If your criterion doesn't return any result, you can return a default value

=FILTER(array,criteria,no result)

## Example #1: Extract rows for the customer "Peter"

For example, we want to find information about the customer Peter.

1. First of all, we select all the data of our document (here the name of the Table )

=FILTER (tbl_Sales,

1. Select the column where is the value of your criterion
2. Indicate the value of your criterion

=FILTER(Tbl_Sales,Tbl_Sales[Customers]="Peter")

• All the rows where the customer name is "Peter" are returned by the function

## Example #2: Extract rows that are not "Apple"

The FILTER function accepts all types of logical testsThe FILTER function returns the rows when the result is TRUE.

For example, to select all fruits except apples, we just have to write the following formula

= FILTER(Tbl_Sales; Tbl_Sales[Fruits]<>"Apple")

## Example #3: Extract rows with a quantity greater than 20

Here, we wnat to select all the quantities greater than 20. The formula is:

= FILTER(Tbl_Sales; Tbl_Sales[Quantity]> 20)

## Example #4: Message when there is no result

If the FILTER function doesn't return any row, you can customize the message to avoid an error message.

=FILTER(Tbl_Sales;Tbl_Sales[Customers]="John";"No Result")

## Example #5: Return a column different of the criteria

It is important to know that the columns to return can be different from the columns as the filter. For instance, if we want to return only the fruits' names bought by Peter, we will write this simple formula.

=FILTER(Tbl_Sales[Fruits];Tbl_Sales[Customers]="Peter")

• We have selected for the result only the column of the Fruit's names
• The Customer column is used for the filter