Dynamic Array Functions and Lambda Functions

Functions are a very important part of Excel and, recently, Microsoft has revamped the way functions work in Excel. In 2018, Microsoft introduced what it termed dynamic array formulas, which eliminated the need to use Ctrl + Shift + Enter for formulas that need to output values across multiple cells. It was an update of the formula engine in Excel to allow calculations to spill into as many cells as the output requires. In the Excel versions before this update, you needed to know how many cells and the dimensions (rows and columns) for formulas that output to multiple cells. Then, you would select the cells upfront, type in the formula, and use Ctrl + Shift + Enter to commit the formula. The formula couldn’t spill beyond the initially selected cells.

The introduction of dynamic array formulas in Excel has made it possible to have formulas that can spill across cells without the need to use Ctrl + Shift + Enter. Also, they can shrink...

Dynamic array functions

There are over 22 dynamic array functions in Excel 365 and more are being added almost quarterly. We will, however, focus on the ones you can readily use in your reports and dashboards:

• UNIQUE
• FILTER
• SEQUENCE
• SORT
• SORTBY

Let’s start with UNIQUE, which is a very popular dynamic array function.

UNIQUE

UNIQUE is the formula version of Excel’s Remove Duplicates tool, which goes through a selection of Excel cells or tables and removes duplicate entries. It takes in a range and outputs a distinct version of that range. You can use it on a one-dimensional range – a list of values on a single row or single column. You can also use it on a table – a two-dimensional range. Unlike the Remove Duplicates tool, it is dynamically linked to the source range.

The function syntax is UNIQUE(array,[by_col],[exactly_once]). The array, as mentioned earlier, can be one-dimensional or two-dimensional. by_col is optional...

Lambda functions

Microsoft created the Lambda function in Excel as a way for people to create user-defined functions without doing any programming, just by using the same Excel formulas they do within cells. In December 2020, when the Lambda function was released for Excel 365 and Excel on the web, many people saw it as a weak attempt to replicate the Visual Basic for Applications (VBA) user-defined functions and pointed out some limitations it has. Limitations such as it needs to be put in a named range to be able to call it like a proper function. But Microsoft was just getting started then and the Lambda function is just one of many functions it released to empower users to create truly powerful custom functions.

This section is all about Lambda and the functions that are built on it. Or, should I say, that require it. Altogether, I call them Lambda functions. We will be covering them individually, and they are as follows:

• LAMBDA
• BYCOL
• BYROW
• MAKEARRAY
...

Summary

This chapter has been a very interesting one as we covered some of the new types of functions in Excel. The dynamic array functions were game-changing when they were released. They made a lot of computations and reports easy that were previously only possible via VBA and a complex setup of a pivot table. Also, the formula engine update that came along with it, giving all formulas in Excel the ability to spill into multiple cells, was in itself a bigger deal than the new dynamic array functions. Now, we can use old formulas such as SUM and COUNTIF in a way that outputs into multiple cells. And more recently, another type of function has surfaced in Excel: Lambda functions. They allow Excel users to create custom functions and do things that used to be only possible with VBA or a dizzying cocktail of functions. On the surface, they look simple and are easily replaced by other existing functions, but when you leverage the custom function capabilities of Lambda and the dynamic...