Skip to content

The FILTER Function in Excel 365

The FILTER function is one of the newest functions introduced to Excel in Office 365!

It’s super cool and not too tough to use. If you’d like to learn about this new and amazing function, then keep on reading!

The FILTER Function

The FILTER function is a new function in Excel that allows you to filter a dataset based on a specific value or several values.

For example, let’s say you have the following data.

Now let’s say that you only want to view the foods that are colored green. You can use the FILTER function to go through the data and return only the records that contain green foods.

How the FILTER Function Works

The FILTER function accepts three arguments.

=FILTER(Array, Include, If_Empty)
  • Array – This is the array or range of cells you want to be filtered. (Required)
  • Include – This is a Boolean array that must have an equal height or width as the original array. A Boolean array is a range with a comparison operator such as equals, greater than, or less than. An example would be A2:D5=F4. (Required)
  • If_empty – This is the value the function will return if the filter returns nothing. (Optional)

To better understand how these arguments work, take a look at the examples below.

Example 1 – Filter on a Single Value

Here is the original dataset.

The goal is to filter the foods based on the color Green.

Here is how to apply the FILTER function to filter the data by the color Green.

The first argument references the range of data you want to filter.

The second argument references the second column of the data range (which is the same height as the original array) and tests to see which cells contain a value equal to the value in cell E2.

The third and final argument is set to “No Data Found” and will return this value if the filter returns no rows that meet the condition.

The FILTER function then returns only those records containing the color Green in the second column.

Example 2 – Filter If Two Conditions Are Both True (AND)

You can also use the filter function to filter the data based on two conditions.

In other words, you can get the filter function to return only the records that meet both condition 1 AND condition 2.

To get this to work, the second argument will have two Boolean arrays each surrounded by parentheses and separated by an asterisk.

It will look like this.

=FILTER(Array, (Boolean1) * (Boolean2), If_error)

Here is an example.

We have the same dataset as before.

This time, we want to filter by Green and only want records with a price greater than 0.50.

So we set up the FILTER function like this to filter the data.

The first argument references the dataset, and the second argument has two Boolean arrays within parentheses separated by an asterisk.

The first Boolean array tests for the color Green and the second tests for a price greater than 0.50.

As a result, three records that meet both conditions are returned by the FILTER function.

Example 3 – Filter If Any Condition is True (OR)

Appropriately enough, you can use the FILTER function to filter values that meet any of the conditions.

For example, you can get the FILTER functions to return all records that meet one condition OR another.

To get this to work, the second argument will have two Boolean arrays each surrounded by parentheses and separated by a plus sign.

=FILTER(Array, (Boolean1) + (Boolean2), If_error)

Here is an example.

We have the same dataset as before.

This time, we want to filter the foods that are either Green or an Apple.

So we set up the FILTER function like this to filter the data.

The first argument references the dataset, and the second argument has two Boolean arrays within parentheses separated by a plus sign.

The first Boolean array tests for the color Green and the second tests for the food Apple.

As a result, the four records that meet condition 1 are returned, and the one record that meets condition 2 is also returned.

As you can see, the FILTER function is very powerful and is a wonderful new function to have available in Microsoft Excel!

This Post Has 0 Comments

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top