Understanding Filter Function in Excel

The Filter function in Excel 365/2021 is one of the best alternatives to the conventional filtering methods (Auto Filter & Advanced Filter). Once you apply the filter using Filter function in Excel, you don’t need to worry about the data changes. Excel handles everything automatically and recalculate the formula and show the result on latest available data.

Excel 365 Filter Function

The FILTER function in Excel 365 allows us to filter a range of data based on a Boolean (True/False) array provided as a parameter in function. Here, Boolean array works like criteria to filter the data.

Note:

  • Filter function belongs to the category of Dynamic Array function family where the result is array of the values which spills into a range of cells.
  • A Boolean array is a sequence of values that can only hold the values of true or false i.e. Boolean data type.

Syntax of Filter function

=FILTER (array, include, [if_empty])

Parameters & description:

  • Array (mandatory) – the array or range of cells that you want to filter.
  • Include (mandatory) – this is the criteria supplied to filter function as Boolean array (True/False).  While executing the formula, Filter function checks the criteria and match it with the value available in respective cell. If the given criteria meets, then It shows True otherwise False in Boolean array and then filters only those records where value is True.

Note: The height (when data is in columns) or width (when data is in rows) of Boolean array (include parameter) must be equal to the height/width of the array argument values of true or false i.e. Boolean data type.

  • If_empty (optional) – the value to return if all values in the included array are empty (filter does not return anything).

Notes:

  • Array is a range of cells available in a row, a column or a combination of rows and columns of values in Excel. Example, range A2:A5, A2:G2 or A2:G5.
  • The Filter function returns the output as an array, which spills the result of the formula in dynamically created appropriate sized array range.
  • If you think that the result of Filter could be empty value, then use the 3rd argument ([if_empty]). Otherwise, a #CALC! error will be the result as Excel does not support empty arrays.  
  • If any value of the Boolean array (include argument) is an error value e.g., #N/A, #Value, etc.  or the given condition can’t be converted to a Boolean then Filter function will return an error as output.
  • If Array is available in different workbook and you are using Filter function in other workbook, then both workbooks must be opened at the same time. If you close the source workbook, then Filter function will return a #REF! error when it will be refreshed.

Usage of Filter function

To understand the different usages of the Filter function, we will use the below table.

CityStateCountry
BengaluruKarnatakaIndia
MangaluruKarnatakaIndia
HubliKarnatakaIndia
BallariKarnatakaIndia
HassanKarnatakaIndia
UdupiKarnatakaIndia
MumbaiMaharashtraIndia
PuneMaharashtraIndia
NagpurMaharashtraIndia
ThaneMaharashtraIndia
NasikMaharashtraIndia
AurangabadMaharashtraIndia
PeshawarKhyber PakhtunkhwaPakistan
MardanKhyber PakhtunkhwaPakistan
MingoraKhyber PakhtunkhwaPakistan
KohatKhyber PakhtunkhwaPakistan
KarachiSindPakistan
SukkurSindPakistan
LarkanaSindPakistan
HuntsvilleAlabamaUnited States
MonroeLouisianaUnited States
RaleighNorth CarolinaUnited States
JacksonvilleFloridaUnited States
Los AngelesCaliforniaUnited States
CranstonRhode IslandUnited States
MilwaukeeWisconsinUnited States
Virginia BeachVirginiaUnited States
New York CityNew YorkUnited States
LouisvilleKentuckyUnited States

Basic Filter function

In the following example, we have used the formula =FILTER ($A$2:$C$30, $B$2:$B$30=”Karnataka”, “No Result”) to return all the records for Karnataka as state available in source data (array). If there are no records for Karnataka, then function will return a string “No Result”.

Unlike Auto Filter/Advanced Filter features in Excel, the Filter Function does not make any changes to the source data. It filters the records basis the condition passed to the Include parameter and spill the result array into the range (E5:G10 in the screenshot below), beginning in the cell where the formula is entered.

In the above example, we have passed a string value ‘Karnataka’ to the second parameter ‘Include’. You can also pass the cell reference in place of string. Please see the below formula where we are filtering the county ‘United States’.

=FILTER($A$2:$C$30,$C$2:$C$30=$J$1, “No Result”)

If the given condition (in Include parameter) does not find any records in source data (array) then, the Filter function returns the value we have passed to the last parameter if_empty. In our example, we have passed “No Result” hence, Filter function will show No Result as output of formula.

Please see the below example where we are looking for country as a France but in source data, France is not available. So, the output would be ‘No Result’.

=FILTER($A$2:$C$30,$C$2:$C$30=$F$1, “No Result”)

Here, in place of “No Result”, you can also use an empty string (“”) for the last argument if_empty.

Filter Function for horizontally structured data table/source

If our data table is horizontally structured unlike vertical in previous examples, then Filter function also works without any issue. All the conditions and rules will remain same as Vertical table except the width of Boolean array passed as a parameter for Include should have same width of Array, the first argument.  

Please see the below example where data is organized horizontally from left to right and we are using Filter function to get the records where State is Maharashtra.

=FILTER($B$1:$N$3,$B$2:$N$2=$B$6,”No Result”)

Advanced use of Filter function

In previous example, we have seen the Filter function with simple condition. Let’s understand how we can pass multiple criteria in Filter function.

Filter with multiple criteria using AND logic

To filter data with multiple conditions, we can use two or more Boolean arrays with valid Boolean operator (* and +) for the Include parameter.

Before writing the Filter function with AND logic, let’s understand the Boolean arrays for Include parameter in multiple criteria.

As we know, Filter function converts Include parameter value to True and False basis the criteria and then it shows the records having True value only.  The same process happens with multiple criteria too, but the condition is all the multiple criteria should get converted into a single Boolean array, where TRUE equates to 1 and FALSE to 0.

So, if we are using two Boolean arrays for the Include parameter then we can use asterisk (*) to multiply the result of first Boolean array value (True and False) to the second Boolean array value and get a single i.e., the final Boolean array (1 and 0) for Filter function. Final Boolean array result will be based on the below Boolean calculation logic.

TRUEXTRUE=TRUE
TRUEXFALSE=FALSE

So, considering the calculation logic, asterisk (*) will be used as AND operator in multiple criterial in Filter function.

Boolean Value -1Boolean Value – 2Final Boolean Value
TRUETRUE1
FALSEFALSE0
TRUETRUE1
TRUEFALSE0
FALSEFALSE0
FALSEFALSE0
FALSETRUE0
TRUETRUE1
TRUETRUE1

Let’s apply the same logic to pass multiple filter criteria with AND Boolean operator.

In below formula, we are applying Filter function to get the records where country is India and state is Karnataka.

=FILTER($A$2:$C$30,($C$2:$C$30=$F$1)*($B$2:$B$30=$F$2), “No Result”)

Filter with multiple criteria using OR logic

OR logic with multiple criteria is totally different than AND. Unlike AND, either of any criteria will have TRUE value from multiple Boolean arrays then the final Boolean array value will be TRUE.

So, if we are using two Boolean arrays for the Include parameter then we can use plus (+) to sum the result of first Boolean array value (True and False) and the second Boolean array value and get a single i.e., the final Boolean array (True and False) for Filter function. Final Boolean array result will be based on the below Boolean calculation logic for OR.

TRUE+TRUE=TRUE
TRUE+FALSE=TRUE
FALSE+FALSE=FALSE

Let’s apply the same logic to pass multiple filter criteria with OR Boolean operator.

In below formula, we are applying Filter function to get the records where country is either India or United States.

=FILTER($A$1:$C$29,($C$2:$C$30=$F$1)+($C$2:$C$30=$F$2), “No Result”)

Filter with multiple criteria using AND & OR logic both

In previous two examples, we have seen the Filter function usage on multiple criteria using AND & OR separately. Let us understand how we can use AND & OR both in multiple criteria of Filter function. The Boolean calculation logic for AND & OR will remain same as we have seen in previous example. The final True and False value will be based on the criteria grouped with brackets ().

So, if multiple criteria for OR has been grouped in one bracket then Filter function will execute the grouped criteria for OR and convert it to a new Boolean array and after that it executes the AND criteria similar to OR and create an additional new Boolean array and then finally evaluate the criteria with available AND/OR.

Please see the calculation logic as mentioned below.

Boolean calculation with AND & OR both

Let us apply the same logic to pass multiple filter criteria with AND & OR both Boolean operators.

In below formula, we are applying Filter function to get the records where country is India and state is Karnataka or country is Pakistan and state is Khyber Pakhtunkhwa.

=FILTER($A$2:$C$30,(($C$2:$C$30=$F$1)*($B$2:$B$30=$F$2))+(($C$2:$C$30=$G$1)*($B$2:$B$30=$G$2)),”No Result”)

Filter on Numeric or Date Range

In all the above example, we have considered the criteria as string value. You can use any numeric value range or date range for Boolean array and apply the criteria with below mentioned comparison.

Comparison OperatorRemarks
Greater Than
Less Than
>=Greater Than or Equal To
<=Less Than or Equal To
<> Not Equal To
=Equal To

To understand this, let’s take the sales data with Order Date and Sales amount column.

Order DateCustomer Name Sales Amount
1-Jun-22Aadi Srivastava $        261.96
2-Jun-22Aadi Srivastava $        731.94
3-Jun-22Aarav Arya $          14.62
4-Jun-22Aarnav Gupta $        957.58
5-Jun-22Aarnav Gupta $          22.37
9-Jun-22Aayush Kumar $          15.55
10-Jun-22Abdul Kumar $        407.98
12-Jun-22Abeer Kumar $            2.54
13-Jun-22Abhimanyu Kumar $        665.88
14-Jun-22Abhiram Pandey $          55.50
15-Jun-22Aditya Arya $            8.56
16-Jun-22Aditya Arya $        213.48
17-Jun-22Aditya Arya $          22.72
18-Jun-22Advaith Pandey $          19.46
20-Jun-22Advay Srivastava $          71.37
21-Jun-22Advik Gupta $     1,044.63
22-Jun-22Agastya Gupta $          11.65
…..

In below formula, we are applying Filter function to get the records where Order Date is less than or equal to 17th June 2022 and Sales Amount is less than or equal to $25.

=FILTER($A$2:$C$51,($C$2:$C$51<=$F$1)*($A$2:$A$51<=$F$2))

Filter function – miscellaneous scenario

How to filter duplicates in Excel

If you want to filter duplicate records, then you can leverage the COUNTIFS function and used in FILTER to get the desired output. Just apply the FILTER function as mentioned below.

=FILTER (array, COUNTIFS (column1, column1, column2, column2)>1, “No results”)

Here, we are using all column in COUNTIFS function. If you want to avoid multiple column in COUNTIFS function then you can create a Key column.

How to filter out blanks in Excel

If you want to filter all the records having at least one blank value, then you can use the below mentioned FILTER function to filter out the blank value.

=FILTER(array, (column1<>””) * (column2=<>””), “No results”)

In above formulas, to identify non-blank cells, we have used “not equal to” operator (<>) together with an empty string (“”) to filter out the records with blank value for at least one column.

How to use FILTER function in place of SUMIFS, AVERAGEIFS, COUNTIFS, MINIFS, MAXIFS, etc.?

Using FILTER in place of SUMIF/SUMIFS

=SUM(FILTER (Array, Boolean_array=criteria, “No results”))

Here, Array is a single dimension array (one column/row range) with numerical data which can be summarized and Boolean_array is the array on which we need to apply the criteria. Once, FILTER function will show the result then sum will provide the sum of output array.

Using FILTER in place of AVERAGEIFS/AVERAGEIF

=AVERAGE (FILTER (Array, Boolean_array=criteria, “No results”))

Using FILTER in place of COUNTIFS/COUNTIF

=COUNT (FILTER (Array, Boolean_array=criteria, “No results”))

You can use the same logic for other functions e.g., MAXIFS, MINIFS, etc. …

We can use the FILTER function in different scenarios. Using other Excel functions, you can extend the power of FILTER function.

If you have any questions or need support on this function, then please post your comments and we will get back to you.

Please download the Excel file from the below link.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here