Microsoft has been releasing some major updates for Excel recently, such as support for custom data types. Today, another one is making its way to Office Insiders in the Beta channel. Microsoft has announced LAMBDA, a new feature in Excel that allows users to create custom functions based on Excel formulas.
Until now, it was possible to create custom functions in Excel, but only by using other languages such as JavaScript. LAMBDA allows functions to be created using the Excel formula language, and they can then be saved under custom names, making them reusable. On top of that, the functions support recursion, meaning that a function can call itself in its definition. This was only possible in Excel by using scripts, such as VBA or JavaScript.
There are a few benefits to this approach, according to Microsoft. For one thing, using a saved custom function means that, should you find an error in the formula, you can fix the function, and that will fix the result for every cell where the function is used. It can also be easier to read for those that didn"t create the formulas, because functions can be identified by names, thus it"s easier to understand their purpose.
Recursion is also useful because it allows formulas to automatically update when necessary. A function can call itself if a change is detected in the data, updating the result of the function based on those changes. As an example, Microsoft uses the following GIF to demonstrate how recursion keeps the resulting cells updated to offer the desired result.
Of course, these formulas support more than just text and numbers, and they can be used with data types like locations, and they also support dynamic arrays.
This feature is only available in the Beta channel of the Office Insider program for now, and it"s not clear when it will roll out to more users.