FILTER Function – Master Excels Array Tools!

 

The FILTER function enables Excel users to filter data ranges.

You can set your own criteria, and pull out only the information you need!

The FILTER Function

Learning to use filters is a basic Excel skill and covered during our beginner’s Excel course as it allows you to find only the data you need.

Syntax

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

 

Arguments

The above syntax consists 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.

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.

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.

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.

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 together SORT and SORTBY can add even more 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.

 

• • 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 removing blank 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.

 

• • 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.

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.

 

• • The array is set to A2:C10 that consists 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.

 

• • 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.

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.

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.

If you are still having issues, try using formula auditing to help diagnose the issue!

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!