How to use the FILTER function in Excel?

How to use the FILTER function in Excel?
Last Updated on 04/07/2023
Reading time: 2 minutes

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 tasks with Excel. And this task is very simple when you click on the little arrow in the header.

Arrow to filter your columns

Now, with the dynamic array functions, it is possible to create a workbook where you can filter your data automatically in a function of the values of your criteria.

This function allows you to return several rows whereas the VLOOKUP function does not allow it.

FILTER function parameters

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

  • The column or columns to be returned filtered
  • The filter (or the test)
  • [Optional] The message when the result is empty

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

Other examples with the FILTER function

The FILTER function accepts all types of logical tests. The 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
  • You can also create a filter greater or lower than a value. Here, we select all the quantity greater than 20.

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

Filter on the quantity

Result to display if 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

Return any column, not necessarily the one with the criterion.

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

Leave a Reply

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

How to use the FILTER function in Excel?

Reading time: 2 minutes
Last Updated on 04/07/2023

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 tasks with Excel. And this task is very simple when you click on the little arrow in the header.

Arrow to filter your columns

Now, with the dynamic array functions, it is possible to create a workbook where you can filter your data automatically in a function of the values of your criteria.

This function allows you to return several rows whereas the VLOOKUP function does not allow it.

FILTER function parameters

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

  • The column or columns to be returned filtered
  • The filter (or the test)
  • [Optional] The message when the result is empty

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

Other examples with the FILTER function

The FILTER function accepts all types of logical tests. The 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
  • You can also create a filter greater or lower than a value. Here, we select all the quantity greater than 20.

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

Filter on the quantity

Result to display if 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

Return any column, not necessarily the one with the criterion.

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

Leave a Reply

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