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...