FILTER function in Excel

FILTER Function

Filtering is a common everyday action for most Excel users.  Whether using AutoFilter or a Table, it is a convenient way to view a subset of data quickly.  Until the FILTER function came to Excel, there was no easy way to achieve this with formulas.  When Microsoft announced the changes to Excel’s calculation engine, they also introduced a host of new functions.  One of those new functions is FILTER, which returns all the cells from a range that meet specific criteria.

At the time of writing, the FILTER function is only available to those on a Microsoft 365 subscription.  It will not be available in Excel 2019 or earlier versions.

Download the example file
I recommend you download the files which support this post, as you’ll be able to work along with examples.  This is the best way to learn.  You’ll be able to see the solutions in action, plus the file will be useful for future reference.  The support files are available for FREE to newsletter subscribers.

Click below to subscribe and gain access to the subscriber area.  You will also receive:

  • My favorite tips and tricks direct to your inbox
  • Exclusive content (which is only available to subscribers)
  • FREE tools and downloads

Download Icon

If you’re already a subscriber, click here to log-in to the subscriber downloads area.

The filename for this post is 0035 FILTER function in Excel.zip

Watch the video:

Watch the video on YouTube

Arguments of the FILTER function

Before we look at the arguments required for the FILTER function, let’s look at a basic example to appreciate what it does.

FILTER Function Basic Usage

Here the FILTER function returns all the values in cells B3-B10 where the number of characters is greater than 15.  Not a scenario that many of us will need, but it demonstrates the power of the new FILTER function.

FILTER has three arguments:

=FILTER(array, include, [if_empty])
  • array: The range of cells, or array of values to filter.
  • include: An array of TRUE/FALSE results, where the TRUE values will be retained in the filter.
  • [if_empty]: The value to display if no rows are returned.

Download the 100 Excel Macros ebook

100 Excel Macros Book

  • Contains 100 Excel VBA macros
  • Learn VBA by following along with the example codes
  • Apply to your macros, automate Excel, save time.

Download the ebook today!

Click the button below to subscribe, you’ll gain access to the subscriber area in which you can download the ebook.

Download Icon (on mid-green background)

Examples of using the FILTER function

The following examples illustrate how to use the FILTER function.

Example 1 – FILTER returns an array of rows and columns

In this example, cell F3 contains a single formula, but this formula returns an array of values into the neighboring rows and columns.

FILTER Function retuns array of rows and columns

The formula in cell F3 is:

=FILTER(B3:D10,C3:C10>100)

This single formula is returning 2 rows and 3 columns of data where the values in C3-C10 are higher than 100.

Example 2 – #CALC! error caused by the FILTER function

The screenshot below displays what happens when the result of the FILTER function has zero results – we get the new #CALC! error.

FILTER #CALC! Error

The formula in cell F3 is:

=FILTER(B3:D10,C3:C10>200)

As no rows meet the criteria of Invoice Value being higher than 200), the FILTER cannot return a value, so the #CALC! error is displayed.

Thankfully, Microsoft has given us the if_empty argument, which displays a message if there are no rows returned.

FILTER Function with if_empty argument

The formula in cell F3 is:

=FILTER(B3:D10,C3:C10>200,"No Results")

In the screenshot above, “No Results” displays instead of the #CALC! error.

If we wanted to display a result in each column, we could include a constant array within the if_empty argument.  The following would show n/a in the Invoice Value and Days Due columns.

=FILTER(B3:D10,C3:C10>200,{"No Results","n/a","n/a"})

This formula would result in the following:

nction no results, multiple if_empty values

Example 3 – FILTER expands automatically when linked to a table

This example shows how the FILTER function responds when linked to an Excel table.

FILTER expands automatically with table

New records added to the table which meet the criteria are automatically added to the spill range of the function.  Amazing stuff!

Example 4 – Using FILTER with multiple criteria.

Example 4 shows how to apply FILTER with multiple criteria.

FILTER with multiple AND conditions

The formula in cell F3 is:

=FILTER(B3:D10,(C3:C10>50)*(D3:D10>30))

For anybody who has used the SUMPRODUCT function, this method of applying multiple conditions will be familiar.  Multiplication creates AND logic (i.e., all the criteria must be TRUE).  The example above shows where the Invoice Value is greater than 50 and the Days Due is greater than 30.

Addition creates OR logic (i.e., any individual condition can be TRUE).

FILTER with multiple OR conditions

The formula in cell G3 is:

=FILTER(B3:D10,(C3:C10>50)+(D3:D10>30))

The example above shows where the Invoice Value is greater than 50 or the Days Due is greater than 30.

Example 5 – Using FILTER for dependent dynamic drop-down lists

Drop-down lists are a data validation technique.  Dependent drop-down lists are an advanced technique where the lists change depending on the result of another cell.  For example, if the first drop-down list displays country names, the second drop-down list should only display cities that exist in that country.  There are currently very tedious methods to achieve this effect, but the new FILTER makes this super easy.

FILTER - Dependent drop-down lists

The formula in cell H3 is:

=UNIQUE(B3:B10)

The UNIQUE function creates a unique list to populate the drop-down in cell F4.

The formula in cell I3 is:

=FILTER(C3:C10,B3:B10=F4)

Depending on the value in cell F4, the values returned by the FILTER function change.  The second drop-down in cell F6 changes dynamically based on the value in Cell F4.

Example 6 – Using FILTER with other functions

In this final example, FILTER is nested inside the SORT function.

FILTER and SORT together

The formula in cell F3 is:

=SORT(FILTER(B3:D10,D3:D10<=30))

First, the FILTER function returns the cells based on the Days Due being less than or equal to 30.  The SORT function then puts the Customers into ascending alphabetical order.

Want to learn more?

There is a lot to learn about dynamic arrays and the new functions.  Check out my other posts here to learn more:

Don’t forget:

If you’ve found this post useful, or if you have a better approach, then please leave a comment below.

Do you need help adapting this to your needs?

I’m guessing the examples in this post didn’t exactly meet your situation.  We all use Excel differently, so it’s impossible to write a post that will meet everybody’s needs.  By taking the time to understand the techniques and principles in this post (and elsewhere on this site) you should be able to adapt it to your needs.

But, if you’re still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic.  You will benefit much more by discovering your own solutions.
  2. Ask the ‘Excel Ninja’ in your office.  It’s amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community.  Remember, the people on these forums are generally giving their time for free.  So take care to craft your question, make sure it’s clear and concise.  List all the things you’ve tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner.   They help by providing solutions to smaller Excel problems.

What next?
Don’t go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

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