# The FILTER Function in Excel

Last Updated on 14/05/2024

The FILTER function is one of the greatest functions added to 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 a new value to visualize the result in your spreadsheet each time, 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 from the criteria

It is important to know that the columns to return can differ from those as the filter. For instance, if we want to return only the names of the fruits 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

# The FILTER Function in Excel

Last Updated on 14/05/2024

The FILTER function is one of the greatest functions added to 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 a new value to visualize the result in your spreadsheet each time, 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 from the criteria

It is important to know that the columns to return can differ from those as the filter. For instance, if we want to return only the names of the fruits 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