How to use the FILTER function (basic)
The FILTER function comes from the dynamic array functions’ family of Excel.
The FILTER function is only available in Microsoft 365 and Excel 2021. The older versions of Excel do not have this function.
It will not make changes to the original data. Instead, it will filter out data (based on the criteria you supply) in a separate location. Let’s test out a basic example to see how it works💡
Here we have a dataset that includes a list of people, their countries, and months of residence in there.
From this data, let’s filter out people who reside in Ireland only🌎
- Write the FILTER function as follows:
- The first argument specifies the array from where the data is to be filtered.
We will specify this range as A2:C8.
= FILTER (A2:C8,
If you don’t want all the details to be filtered out. For example, if you only want the names of the people who live in Ireland filtered out (and not the country and residency months), specify the array as A2:A8 only📜
- The second argument of the FILTER function is the include argument. This is where we define the criteria.
Column B contains the country, and as we want to filter out the people who live in Ireland, so our criterion would be B2:B8=”Ireland”.
We have Ireland written in cell C10 so, we are referring to it instead of hardcoding Ireland👇
= FILTER (A2:C8, B2:B8=C10,
- The last argument is if_empty. This will be returned if the FILTER function fails to find values that fit the specified criterion.
You can leave it blank or set it to any desired value. We are setting it to “Not Found”.
= FILTER (A2:C8, B2:B8=C10, “Not Found”)
And there you go! The FILTER function filters out all the data for people who live in Ireland. And it is filtered out to a separate location (where the formula was typed).