How to Use the Excel FILTER Function

Millions of people use Microsoft Excel everyday to store and analyze a considerable volume of data. With so much information stored within Excel, the question is, how do you find the data required? That’s where the Excel FILTER function comes in. 

FILTER is a powerful dynamic array function and is accessible to those with a Microsoft Office 365 subscription. It helps filter datasets based on specific criteria. The output is returned to the location where the function is entered, but importantly, automatically spills to whatever number of adjacent cells are needed to display the results.

If you want an easy tutorial on how to use the FILTER function, you are in the right place. Download the free file to practice along.

What is the FILTER function?

Simply put, a filter extracts data based on your stated criteria. It takes data from an existing array and results in an output that satisfies the applied filter. This makes it easier to manage and refine large datasets.

So what's the difference between FILTER and a basic Excel auto-filter? The simplest answer is that the FILTER function is dynamic, whereas auto-filters are not. This means the results will automatically update when your data changes, and the output array will resize to accommodate the new return values. The FILTER function is also more versatile in accommodating multiple filter criteria.

Syntax and arguments

The syntax of the FILTER function is as follows:

=FILTER(array, include, [if_empty])

Its three arguments, array, include, and the optional if_empty are defined below.

Array

The first argument of the FILTER function is array. It tells which cells you’d like to extract the data from. Using the array function, you can select the cells that have your required data and the order in which you want it to be displayed. 

It tells Excel that you want to display only a specific part of the data or in a different manner than it’s originally listed in the existing datasets. That’s the purpose of the FILTER function. It helps you sort the data by your defined criteria, instead of extracting the same data, in the same order, in a different place.

Include

The include argument tells Excel the filter or values you want to include in your output. This can either be numeric or text-based. Essentially, include uses certain parameters to locate the data within Excel. 

If_empty

This is an optional argument, which is used when there is a possibility that the formula might not find any values that meet the criteria. The “if_empty” argument allows you to specify an output (such as a custom message or an alternate calculation) if the filter operation results in an empty array. If this argument is omitted, an empty array will return a #CALC! error.

Use cases of the FILTER function

The most common use case of the FILTER function is that if you have a huge dataset and are having trouble extracting your desired information, you can use FILTER. It limits the data visible in the Excel sheet by returning numeric or text values that match your filter parameters. 

For instance, you can execute the FILTER function to locate cells with numeric values between 300 to 800, and the output will generate the list of the cells with these values. 

It’s easy to sort and extract information using FILTER instead of manually searching for your required data. Likewise, entering the text that you’d like to be visible will help you determine whether or not you have already entered certain information and where exactly it is in Excel. 

FILTER function basic example

Let’s check out the example of the FILTER function to get a clear picture of how it works. 

Suppose this is the worksheet and the data is organized in three columns (A to C). There’s the name of the sales rep, the region they work in, and the total sales they have generated. 

Let’s say you want to extract the information about the sales target achieved by individuals within the US in the same worksheet. So, you created three similar columns (E to G). 

Step 1: Select the array

Determine the location of your array, i.e. select the columns and rows you’d like to extract the data from. In this case, it would be the data within A2:C11. 

=FILTER(A2:C11,

Step 2: Determine your filter criteria

Determine what you want kind of data you want to extract, or include, in your output. For instance, in the image below, we’ve selected the region column and entered “US” so that the resulting data will show the names and sales in the US region.

In this example, we will omit the if_empty argument.

=FILTER(A2:C11,B2:B11="US")

Once you hit Enter, all the cells will be filled with the name, region, and sales only for reps from the US region. Here’s how it looks.

Step 3 (optional): What to do if no values meet criteria

You can use the if_empty argument especially if you think there is a chance the dataset may not have the values you are looking for. 

For example, the following formula returns a #CALC! error because no values in the dataset satisfy the include criteria.

=FILTER(A2:C11,C2:C11>20000)

To return a more reader-friendly result, we could update our formula as follows:

=FILTER(A2:C11,C2:C11>20000, “No results”)

Advanced usage: FILTER with multiple criteria

Suppose you want to include two conditions. There are a couple of ways to do this in Excel, but the FILTER function is arguably the best.

Multiple criteria - example 1

Let’s say you want to extract data for sales reps who are in the US and have made sales above 10,000. Only records that satisfy both conditions should be returned. In that case, you must mention both conditions separately using a multiplication operator (*). In the example mentioned above, this is what it would look like.

=FILTER(A2:C11,(B2:B11=”US”)*(C2:C11>10000))

Note that when there are multiple conditions, each condition is enclosed within parentheses. When you hit Enter, here’s what the results this command will produce.

Note that records that satisfy only one of the required conditions are not displayed.

Multiple criteria - example 2

There is also an “or” condition, where you want to return values that satisfy any of the stated conditions. Let’s say you want to display the list of the sales reps and their sales for the US and Canada. Here’s the formula you will use to get the combined data of both countries.

In this case, we will use the plus sign (+) to separate the conditions.

=FILTER(A2:A11,(B2:B11=“US”)+(B2:B11=“Canada”))

This successfully extracts the records of reps who are either in US or Canada.

Troubleshooting: #SPILL! error when using the FILTER function

Since the output of the FILTER function is a dynamic array, it is designed to spill over different cells to cover the entire information relevant to the given criteria. If the output area does not have enough empty cells to accommodate the result, Excel will return a #SPILL! error.

Learn how to fix a #SPILL! error here.

Bottom line

The FILTER function is just one of several features that can turn your ordinary Excel spreadsheet into a data analysis powerhouse. Get more Excel skills to help you improve your overall productivity and efficiency.

Level up your Excel skills

Become a certified Excel ninja with GoSkills bite-sized courses

Start free trial