FILTER Function – Master Excels Array Tools!

The FILTER function from the Dynamic Array Tools of Excel is the very long-awaited substitute for the conventional filtering functions of Excel.

How would you typically filter data in Excel? Using data filters. Or, if the situation takes a more complex shape, advanced filters. But neither of these tools can auto-update the filtered results when the source data changes.

The FILTER function, however, updates every time your worksheet changes. The article below will help you learn it all.

Do not forget to take a look at the other useful courses offered by us.

Did you know Only 48% of people have ever received any formal Excel training? Put yourself above the average candidate by attending our courses! For more Excel stats, read here!

 

The FILTER Function

The filter function enables Excel users to filter a data range based on a specified criterion.

Knowing how to use filters to find the data you need is an Excel fundamental!

Syntax

The very simple syntax of the FILTER function turns out as follows.

= FILTER (array, include, [if_empty])

Arguments

The above syntax constitutes of three arguments analyzed below.

• • Array – This specifies the array or range of cells containing values to be filtered.
  • Include ­­– This specifies the criteria based upon which the values need to be filtered. It can only be specified as a Boolean array in terms of True or False.
  • If_Empty – This argument is an optional one. It specifies the return value in case none of the values from the range meet the supplied criterion.

Return Value

The Excel FILTER function returns an array/range of filtered values that spill into a range of cells beginning from the first cell where the formula is populated.

Function Library

Find the FILTER function from the Excel Functions Library as follows.

Formulas > Functions Library > Lookup & Reference > FILTER

 

 

Why would anyone want to use it?

Without a second thought, filters are one of the most commonly used functions of Excel. Be it data filters, auto filters, or advanced filters, a variety of Excel users would resort to them for many of their routine and professional tasks.

You must know how Excel can help accounting and bookkeeping purposes.

In addition, the FILTER function can be used to filter out the financial results in different ways. For example, you may want to filter out the financial results of a particular region (through the name of a region) or to compile results for a particular year (through the year) or anything of the like nature.

You can also use filters when you’re looking for outliers in large datasets.

 

Use Cases

Let us think of instances where the FILTER function is all that we need and honestly, the list is endless. Take a look for yourself.

1. As a teacher, you may want to filter and identify students who have constantly been scoring well. The FILTER function can help you do so.
2. A manufacturer may want to filter out slow-moving inventory items to shrink down their production. The FILTER function can identify products manufactured at or before a particular date.
3. The administration of a hospital may want to extract a list of COVID patients for internal reporting. The FILTER function can immediately filter out a list of such patients.

Many more of your daily routine and professional tasks may require using the FILTER function. Using it in pair with the SORT function or the SORTBY functions can further add to its utility.

 

Using FILTER to sort an array – simple example

To see how the filter function practically works, let’s go through the basic FILTER function example stipulated below.

 

 

The image above constitutes students from different sections along with the data on their heights. From this data, you may want to filter out students from the Section ‘Tulips’ only. This can be easily done through the FILTER function as follows.

Step 1:

Activate the cell where you want the filtered results to be populated. Ensure having enough vacant cells on the right and bottom to populate the filtered range.

Next, compose the FILTER function as follows.

= FILTER (A2:C13, B2:B13=”Tulips”, “”)

• • • The first argument specifies the array from where the data is to be filtered. As we have the data populated from Column A to Column C, the array is set as A2:C13 excluding the headers.
    • The second argument specifies the criteria based upon which the data is to be filtered. As we only want students from the Section “Tulips” to be filtered, the criterion is set as B2:B13 = “Tulips”. The word Tulips must be enclosed in double quotation marks for Excel to recognize it.
    • The third argument is set vacant as we want Excel to give nothing back if no results are found.

Step 2:

The formula is all set, hit “Enter” to see the results as follows.

 

 

The filter function filters the names of students who belong to the section “Tulips” along with their height data.

Pro Tip: Excel cannot reproduce the headers like ‘Student Names’, ‘Section’, and ‘Heights’ to the filtered data range. These headers are to be copied and pasted manually.

 

More Examples

Some more examples can help us comprehend the FILTER function in better detail.

 

1. Filtering out vacant cells

We have seen many hacks for filtering out empty cells in Excel. Doesn’t the modern dynamic FILTER function facilitate filtering out empty cells?

It obviously does but only with the use of some logical operators.

The image below represents an unsophisticated set of data with multiple vacant cells.

 

 

If we want to compile a set of data of only those students whose class sections are known, the FILTER function can come in handy as below.

Step 1:

To filter out blank cells, the FILTER function needs to be set up as below.

= FILTER (A2:C9, B2:B9<>””, “All cells are blank”)

• • • The first argument specifies the range that contains the data to be filtered.
    • The ‘include’ argument specifies the criterion using the Logical operator <> that means ‘Not equal to’. We have specified the criterion to filter out cells that are not blank
    • The third argument is specified as “No cell is blank” i.e. if Excel finds none of the cells meeting our criteria, Excel would revert this text in the activated cells.

Step 2:

The formula is all set, time to see the results.

 

 

Viola! Excel returns a dataset that contains sections against the name of each student.

 

A quick brainteaser!

 

How does the third argument work?

To see how the third argument works, let’s remove all the values from cells B2:B9 as follows.

 

 

Now, apply the same formula as composed above to see the results as follows.

 

 

Excel applied the criterion specified by us. It searched the cells B2:B9 to see if any cell was not blank i.e. <> “”.

However, as all cells are blank, the specified criterion didn’t meet for any of the cells and this is when the third argument is triggered. Excel returns the value specified by us as the If_Empty argument.

What if the third argument is omitted?

When the criteria are not met, the third argument is triggered. In such a situation, if the third argument is omitted, the results would be as follows.

 

 

In such situations, Excel reverts the #CALC! error as it doesn’t support empty strings.

Learn more about data analysis in Excel here.

2. Setting up Multiple OR criteria

The FILTER function can be used for multiple criteria at once. To do so, the plus operation can be used. In simpler terms, multiple criteria specified using a plus operator work as an OR operator.

Excel filters those values that meet either of the specified criteria. See the example below.

The image below represents different subscribers of an entertainment channel from different parts of the world.

 

 

If you want to filter out all subscribers that are either from China or Australia, how can this be done using the FILTER function?

Step 1:

Select the cell where you want the filtered results to be populated and compose the FILTER function as follows.

=FILTER (A2:C10, (B2:B10=”China”) + (B2:B10=”Australia”), “”)

• • • The array is set to A2:C10 that constitutes of the subscribers’ detail.
    • The second argument includes consists of multiple criteria specified through the plus operation. The first criterion are set equivalent to China. Whereas, the second argument is set equivalent to Australia. The plus operation indicates an ‘OR’ condition.
    • The third argument is specified as vacant i.e. to leave the cells empty if none of the values match the specified criteria.

Step 2:

The formula is all set, hit “Enter” to see the results as follows.

 

 

It is to be noted that Excel has only filtered out results where the subscribers meet either of the conditions. All subscribers who are either from China or Australia are filtered out by Excel.

 

3. Setting up Multiple AND Criteria

Multiple criteria that must all be met for a value to be filtered can also be set up for the FILTER function. This can be done through the application of a simple multiplication operation that works as the AND criteria.

Less discussion, more application – let’s dive into an example to see how this works.

Continuing the same example as above, if you want to filter out all subscribers from China that subscribed for 3 months or more, how can this be done using the FILTER function?

This involves filtering out subscribers that meet two criteria simultaneously i.e. belonging to China and having subscribed for 3 months or more. The FILTER function can be used to help the said cause as follows.

 

Step 1:

Select the cell where you want the filtered results to be populated and compose the FILTER function as follows.

=FILTER (A2:C10, (B2:B10=”China) * (C2:C10>=3), “”)

• • • The array argument is set to A2:C10 that constitutes of the subscribers’ detail.
    • The second argument consists of multiple criteria specified through a multiplication operation. The first criterion is set equivalent to China. Whereas, the second argument is set equivalent to or more than 3.
    • The third argument is specified as vacant i.e. to leave the cells empty if none of the values match the specified criteria.

Step 2:

The formula is all set, hit “Enter” to see the results as follows.

 

 

It is to be noted that Excel has only filtered out results where the subscribers meet both the conditions. For instance, Mr. G is from China but has subscribed for only 2 months. He is, therefore, not included in the filtered results.

 

Points to note

To ensure the FILTER function works efficiently, take note of the following.

1. The range or array defined as the ‘Include’ argument must be of the same size (height or width) as the range to be filtered.
2. The FILTER function will automatically update the filtered data for any changes to the source data. However, in case of any additions to the source data, the array may not automatically resize itself. For the array to automatically resize and take into account any data additions, turn the source data into an Excel table.
3. The return value of the FILTER function spills into a range of cells. Ensure you have a sufficient number of cells on the right, left, or bottom to the first cell of the destination range to populate the filtered results, Elsewise, Excel would fail to filter the results and give back the #SPILL! error.

FILTER Troubleshooting

The FILTER function can be of great use only if you are well aware of the following common trouble areas encountered by users.

 

1. Case Insensitivity

The Excel FILTER formula in its standard form is case insensitive. It will therefore not differentiate between lowercase and uppercase characters. See below.

 

 

As can be seen above, Excel filters out cellphone models irrespective of the case of characters. However, what if you only want to filter out the cellphone model that has a capital ‘A’ to its name i.e. ‘A51’ only and not ‘a51’?

This can be done using the EXACT function as follows.

=FILTER (A2:B4, (EXACT (B2:B4, “A51”), “”)

Excel now filters out results as follows.

 

 

2. #CALC! error

You will confront this error with the FILTER function when you’ve omitted the third optional argument i.e. If_Empty.

If Excel finds no results to put out i.e. none of the values meet the specified criterion, and the if_empty argument is omitted, Excel would revert the #CALC! error.

It is, therefore, important that you specify some value as the third argument or place empty double quotation marks if nothing.

 

Conclusion:

Gone are the days when you had to take the long complex routes to have your data filtered. With the dynamic FILTER function, filtering data now takes new avenues that you must explore.

Try now for yourself!

Need an overview of everything Arrays can do in Excel? Check out our guide to Excel array formulas here.