Hello Excellers, welcome to another #Excel #FormulaFriday blog post in my 2021 series. Today let’s explore the Filter function in Excel. We can output only the positive values from a column of data using this function. This newly released function is part of the DYNAMIC Array functions announced by Microsoft back in 2018. At that time these array functions were only beta release available to a portion of Office insiders. Microsoft announced these awesome new Excel functions are now available to the Excel monthly Channel. So without any delay let’s get down to using the FILTER function.
The FILTER function is a really simple way to solve an issue sent to me by one of my subscribers. They needed to output only the positive values from a column of data into another area of the worksheet. The sample before and after data looked something like this below.
So, the user in this instance was using Office 365. This was helpful. The FILTER function (one of the more recent array functions available) would be the the most efficient way to output all of the positive values from the column of data. It will allow us to filter a range of data based on specified criteria outputting the results of the filter to an array, or new data set. Neat stuff.
So, FILTER Function is one of the more recent functions released as part of the DYNAMIC Array functions announced by Microsoft back in 2018. At that time these array functions were only beta release available to a portion of Office insiders. These Excel functions are now available to the Excel monthly Channel.
FILTER Function Syntax.
The Syntax of FILTER is as follows.
=FILTER (array, include, [if_empty])
Where
array = Range or array to filter.
include = Boolean array, supplied as criteria.
if_empty = [optional] Value to return when no results are returned.
Let’s get started with our example.
The first part of the formula or argument is the array we want to filter. In this example it is B3:B12. The second argument is the array criteria. In this example is it B3:B12>0. This means that the criteria are cells in the array B3:B12 greater than zero. I do not need to use the optional if_empty argument in this instance.
Extract The Positive Values.
Once you hit ok the formula then ‘spills’ the results of the filter into the Excel worksheet. How cool is that?
This function is also dynamic. So, if the original data changes, the filter array will also automatically update. Nice job Excel.