The Excel Filter function filters a range of data based on the criteria specified and returns matching records. It can handle a single or multiple AND/OR conditions or criteria easily. It works exactly like the Excel filter tool works but more.
Before the Excel Filter function, dynamically filtering the data was a tedious task requiring complex formulas. With Excel FILTER function, filtering data is now easy and quick.
In this tutorial:
Consider the following simple illustration where we want to filter the data dynamically on the basis of either product or color:
Even this simple task requires a complex lookup formula to get the results.
Or we simply resort to manually filtering the data with a built-in Excel filter tool. Which is hardly the solution in most cases.
Excel has simplified this ordeal with the Filter() function. It can take filter criteria in an extremely flexible manner and output the results using the power of dynamic arrays.
Filter() Function Syntax
=FILTER(array, include, [if_empty])Filter function has three arguments where the last one is optional.
- array = the data range or values you intend to filter
- include = criteria to be used to filter the data. A boolean array with the same height OR width as the data range to be filtered
- [if_empty] = value to return or task to perform if no matches are found. This is an optional argument and user can leave it empty.
Excel Filter Function Example 1 – Extract part of data
Filter function works exactly like Excel filter tool. To understand the function better lets take both Excel filter tool and Excel function side by side.
Consider the following illustration:
To filter the data using Excel filter tool for only AAA product > click on Product heading > press ALT + Downarrow key on the keyboard to invoke sort and filter dialogue box > from the list only select AAA > press OK.
And here is the result after applying the filter on Product columns to show just AAA products. Notice it only hid the rows out of the view that contained BBB product:
With Excel Filter Function, it is even simpler. Again, it has the following syntax:
=FILTER(array, include, [if_empty]) As our data range is in table format with the name “data”, we can use a structured referencing system to write formulas. Following is the formula:
=FILTER(data,data[Product]="AAA")Similarly, if we require transactions with red color only, we can filter the data using a category column with the following formula:
=FILTER(data,data[Category]=”red”)
Filter Function Example 2 – AND criteria
Similarly, using the Excel filter tool, if we want to show the results with product AAA AND category red, then we will apply the secondary filter on the category column as well:
As you can see it is a two-step process. First, you apply a filter on the product column and then on the category column.
However, with the Excel filter function, you only need to add AND operator, which is an asterisk sign, within the formula as follows:
=FILTER(data,(data[Product]="AAA")*(data[Category]="red"))As we had two conditions i.e. product = AAA AND category = Red. We joined these two conditions together using an asterisk sign to make it an AND criteria.
Filter Function Example 3 – OR criteria
If we want to show all the transactions with product AAA OR category Red? This is where the Excel filter tool itself is not going to help unless we make a workaround.
But it’s super easy with the Excel Filter function using OR operator.
As an asterisk [*] sign is used to make AND criteria, plus sign [+] is used to make OR criteria within the Filter function.
Again, to show all the transactions that contain product AAA or category red, formulas is following:
=FILTER(data,(data[Product]="AAA")+(data[Category]="red"))
Filter Function Example 4 – #CALC! Error
For instances where the function cannot find any results, it will throw a #CALC! error. For example, if I want to show “CCC” product only from the data, which doesn’t exist in the range, the function will return #CALC! error as follows:
Instead of error popping up, we can return a better phrase or statement like “Not Found” with an optional if_empty argument.
We simply need to add “Not matches found” or any desirable text in the formula for the third argument as follows:
=FILTER(data,data[Product]=”CCC”,”Not Matches Found”)
Excel filter functions give us the option to return different messages for each column if the function returns no match.
To do this we will use curly brackets and mention the message text for each column start with the first column:
=FILTER(data,data[Product]="CCC",{"No Product","No Color",0})
