MrExcel Publishing
Your One Stop for Excel Tips & Solutions

FILTER Excel Data with a Function!


September 26, 2018 - by Bill Jelen

FILTER Excel Data with a Function!

FILTER is a brand new Dynamic Array function in Excel. Announced at Ignite 2018, the function is one of several new Excel 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:

Name, Team, and Score
Name, Team, and Score

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

FILTER syntax
FILTER syntax

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.


One FILTER formula returns all Red team members
One FILTER formula returns all Red team members

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

Change F1 to change the results
Change F1 to change the results

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.

f
Specify what to return if nothing is found
Specify what to return if nothing is found

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})

Will the third argument accept an array? Yes.
Will the third argument accept an array? Yes.

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

Download Excel File

To download the excel file: filter-function-in-excel.xlsx

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Omit needless formats"

Title Photo: Farsai C. on Unsplash


Bill Jelen is the author / co-author of
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.