FILTER Excel Data with a Function!

September 26, 2018 - by Bill Jelen

FILTER is a brand new Dynamic Array function in Excel. Announced at Ignite 2018, the function is one of several new Excel functions:

• Monday covered the new =A2:A20 formula, the SPILL error, and the new SINGLE function required in place of Implicit Intersection
• Tuesday covered SORT and SORTBY
• This article covers FILTER
• Thursday will cover UNIQUE
• Friday will cover SEQUENCE and RANDARRAY functions

FILTER will accept an array, keep the rows you specify, and return the results to a spill range.

For this article, you will be using this data set:

The syntax of FILTER is FILTER(array, include, [if empty])

First example: You want to retrieve all rows from the data set where the team is Blue. Put Blue in cell F1. The formula entered in E4 is =FILTER(A4:C16,B4:B16=F1,"None Found").

Notice that you don't have to use F4 or Dollar Signs in the formula. It is a single formula that returns multiple results. There is no need to copy this anywhere, so there is no need for absolute references. Also, you do not have to press Ctrl + Shift + Enter.

Change F1 to Blue and you get all of the Blue team members.

The third argument of FILTER is optional and is illustrated here. [if_empty] specifies the text to return in case there were no results. If you change F1 to Lime, you will get the text specified in the formula.

The result above is not very complete. What if you have a formula that really needs a numeric answer in the 3rd column? You can specify an array constant as the [if_empty] argument: =FILTER(A4:C16,B4:B16=F1,{"None Found","No Team",0})

The more you use these new Dynamic Array formulas, the more amazing they appear.

In his post about FILTER, the Excel team's Smitty Smith has a great example doing an AND inside the new FILTER function.

Watch Video

Title Photo: Farsai C. on Unsplash