Excel FILTER Function
This is part of a multi-part discussion of some of the new features introduced in Excel in 2019 and 2020. This post is about one of the new dynamic array functions. For a discussion of dynamic arrays, see the intro to this series.
Usage
FILTER is used to return a subset from a larger set of data based on specific criteria. It can even be used to return non-adjacent columns from the source set.
FILTER is a more flexible alternative to a pivot table, and can handle exceptions in a more robust manner than VLOOKUP.
Arguments
The arguments are as follows.
| FILTER(array, include, [if_empty]) |
|---|
| array = Columns included in the result. Can be entire or part of source data set. |
| include = Test to define what is included in result |
| if_empty = Result to return if filter returns no results. Default is at #CALC! error. |
Examples
These examples are all in the FILTER demo workbook available for download. Note that data in the samples is fictional but is set in the context of a post-secondary institution.
Simple Subset
In this first example, we are looking for a list of students who have received awards greater than $3,000 and the name of the award they received.
The entire list of awards is in the range B2:D120.
We type the following formula in cell F8:
=FILTER(B2:D120,D2:D120>3000,"None")
This returns only the columns we need with the student name, name of award, and amount of award where the amount of the award (in D2:D12) is greater than 3000. In the event that no awards exceed 3000, the formula would simply return the word “None”.
Of course, ‘>3000’ in the formula could be replaced with a reference to a specific cell, such as ‘>$G$5’ which could contain 3000 or any other value on which the return set should be filtered.
Multiple Filter Criteria
It is also possible to filter on more than one criteria. In this example, we would like to see awards with amounts between 3000 and 5000. This can be accomplished by placing the criteria in brackets and multiplying them.
=FILTER(B2:D120,(D2:D120>3000) * (D2:D120<5000),"None")
Multiplying the criteria combines these in a logical AND relationship. Adding the criteria would be the same as setting up OR criteria.
If we are looking for outlier points in our dataset of student awards, perhaps those with awards less than $200 or greater than $7,000, we would add the two criteria.
=FILTER(B2:D120,(D2:D120<200) + (D2:D120>7000),"None")
The result below then show only the small and the large awards, based on the criteria we specified.
Return Non-Adjacent Columns
By nesting one FILTER formula inside another, it is possible to return non-adjacent columns. In this final example, we would like to know which students received awards greater than $7,000, but we are not concerned with the type of award. Since the student name and award amount are not in adjacent columns, this poses a challenge since we can not specify multiple ranges in the first FILTER argument.
However, we can achieve our objective by nesting one FILTER formula inside another.
First we use FILTER to return columns B to D, filtering on the amount in column D.
Then we filter that result to return only the first and third columns, using an array of 1’s and 0’s to indicate which columns should be returned.
Note also that we have replace the third argument inside the inside FILTER function with an array of 3 values, {“None”,0,0}. Should there be no results greater than $7,000, our inside FILTER will return a result array with a width of 3 columns, suitable for the outside FILTER function to parse. Otherwise the outside FILTER function would return a #VALUE! error.
=FILTER(FILTER(B2:D120,D2:D120>7000,{"None",0,0}),{1,0,1})
Looking Ahead
This wraps up our quick introduction to the FILTER function. In future posts we’ll discover how combining FILTER with other new functions such as SORT and UNIQUE opens up intriguing new possibilities in Excel.
Download the FILTER demo workbook.
Here’s a link to the Microsoft documentation for the FILTER function.