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 24 September 2018 there was no way to achieve this with formulas. On that day, Microsoft announced some new functions which make use of the new calculation engine. One of those new functions is FILTER, which returns all the cells from a range which meet specific criteria.

At the time of writing, Microsoft has only made this new functionality available to Office 365 subscribers on the Insider channel. The updates will be rolled out to all Office 365 subscribers at a future point, but first Microsoft need to assess the impact the changes to the calculation engine will have. The new dynamic array functions and features are not available in Excel 2019 or previous versions, so it is definitely worth getting an Office 365 subscription, otherwise, you will be waiting until Excel 2022, and that’s just too long to wait!

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

Here the FILTER function is returning all the values in Cells B3-B10 where the number of characters is greater than 15. Not a scenario many of us will need, but it does show the power of the new FILTER function.

## Arguments of the 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.

## 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 single formula returns an array of values into the neighboring rows and columns.

The formula in Cell G3 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 greater 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.

The formula in Cell F3 is:

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

As there are no rows meeting the criteria (Invoice Value greater 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 default message if there are no rows returned.

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 within the *if_empty* argument. The following would display *n/a* in the Invoice Value and Days Due columns.

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

### Example 3 – FILTER expands automatically when linked to a Table

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

This FILTER function is using a Table called *tblInvoices* as its source. 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.

The formula in Cell F3 is:

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

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

Addition creates OR conditions (i.e., any value in the row can be TRUE).

The formula in Cell G3 is:

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

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

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

Drop-down lists are a data validation technique. Dependent drop-down 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 display only cities which exist in that country. There are currently only very tedious methods to achieve this effect, but the new FILTER makes this super easy.

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.

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 array functions. Check out my other posts here to learn more:

- Introduction to dynamic arrays – learn how the excel calculation engine has changed.
- UNIQUE – to list the unique values in a range
- SORT – to sort the values in a range
- SORTBY – to sort values based on the order of other values
- FILTER – to return only the values which meet specific criteria
- SEQUENCE – to return a sequence of numbers
- RANDARRAY – to return an array of random numbers

Also, here are some other resources you might find useful:

- Ebook – Dynamic arrays straight to the point – By Bill Jelen (free until December 2018)
- Video – Comprehensive dynamic array formulas: The power of dynamic arrays
- Blog Post – Dynamic array formulas & spill ranges

I have an office 365 subscription and I have tried this on both the desktop version and the online version of Excel and neither of them recognize the FILTER function as being a valid function. So I’m not sure if this has been scrapped by MS and not implemented or what, but it does not work.

As at this current time, the new Dynamic Array formulas are only available for 365 subscribers on the Insider Channel. Hopefully Microsoft will release them to all users soon.

Any subscriber can join the Insider Channel, so you could join and try out these new formulas for yourself.

How do I join the Insider Channel you mention – I really need this filter for my file or I’m going to loose my head scrolling up 300+ unique variables every time I have to enter this information.

I figured it out but it might be helpful to let others know how to join.

Thank you for this post!

Hi Jennifer – Thank you for the suggestion.

Joining the Insider Channel is not something I would recommend for most Excel users working in a business. The insider channel is where Microsoft releases versions which are still in development. It can make Excel unstable and may include features which are changes/updated on a regular basis.

For any Excel geeks out there who want to play with the newest tools and are willing to take the risk, then you can join by going to File -> Account and find the relevant options there. You will need to be an Excel 365 subscriber to change to the Insider Channel.

Users can join with a different insider version that only updates monthly and is more stable.