The FILTER Function in Excel

The FILTER Function in Excel
Last Updated on 14/05/2024
Reading time: 3 minutes

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.

How to use filter in 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,

Select your data with the function FILTER
  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")

FILTER of the data on the name 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")

Filter all the fruits except 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)

Filter on the quantity

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")

Message when there is no row to return

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")

The function FILTER can return a result without the column of criteria
  • We have selected for the result only the column of the Fruit's names
  • The Customer column is used for the filter

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

The FILTER Function in Excel

Reading time: 3 minutes
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.

How to use filter in 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,

Select your data with the function FILTER
  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")

FILTER of the data on the name 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")

Filter all the fruits except 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)

Filter on the quantity

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")

Message when there is no row to return

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")

The function FILTER can return a result without the column of criteria
  • We have selected for the result only the column of the Fruit's names
  • The Customer column is used for the filter

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *