How to Use the Excel Dynamic Array FILTER Function

The new FILTER formula is like the automatic version of Excel's advanced filter feature but even better.

Basically the Excel Filter function filters your data based on criteria you define. It then returns all the matching records based on other conditions.

Why should you use the Excel FILTER function?

1. FILTER returns multiple match results for more than one LOOKUP value.
2. Unlike advanced filter in Excel, the FILTER function allows you to filter without having to refresh your filter.
3. FILTER can be nested in other functions such as putting filters inside the AND, COUNTA, OR, or SUM functions.
4. It is super easy to write one: =FILTER(array, include, [if_empty])

FILTER is only available in the latest version of Excel, so you will need an Office 365 subscription to use it.

How does it work?

The FILTER function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your dataset has the potential of returning an empty value, then use the 3rd argument ([If empty]). Otherwise, a #CALC! error will result.

FILTER Function Used to Return One Criteria

In the following example we used the formula =FILTER(A5:G21,D5:D21=L2,"") to return all records for Violin, as selected in cell L2, and if there are no Violins, return an empty string ("").

FILTER Function Used to Return Multiple Criteria

In this case, we're using the multiplication operator (*) to return all values in our array range (A5:G21) that have Violin AND are in the Retail channel: =FILTER(A5:G21,(D5:D21=L2)*(B5:B21=L1),"")

Want to learn more?

If you want to become an expert in Excel, creating complex formulas or doing more complex analysis, you'll definitely need to learn more about dynamic array functions. Join us on our Expert Excel Core Level 4 course to learn more online via Zoom on 20th February 2024.