Microsoft’s LAMBDA language allows new functions to be written in Excel

Microsoft is attempting to make Excel a more powerful application. According to the company, Excel formulas are written by a significantly larger number of people than all C, C++, C#, Java and Python developers combined. It is the world’s most powerful programming language, but it has two main shortcomings.

One shortcoming is that the Excel language only supported scalar values like numbers, strings, and Booleans. The second is that Excel doesn’t let users define new functions. With new developments from Microsoft, both of these issues are being addressed. 

The first shortcoming is addressed by Microsoft’s Calc Intelligence project, which was developed at Microsoft Research Cambridge. The project is aimed at transforming “spreadsheet formulas into a full-fledged programming language,” Andy Gordon, senior principal research manager at Microsoft, and Simon Peyton Jones, senior principal researcher at Microsoft, wrote in a post. According to Microsoft, at the 2019 ACM SIGPLAN Symposium on Principles of Programming Languages, it introduced two new developments: data types that allow cells to contain first-class records, and dynamic arrays that allow ordinary formulas to compute whole arrays that spill into adjacent cells, Microsoft explained.

The second shortcoming is addressed by the LAMBDA programming language, which was first introduced in December 2020. LAMBDA allows users to define new functions. According to Microsoft, these functions can call other LAMBDA functions, even recursively. 

“With LAMBDA, Excel has become Turing-complete. You can now, in principle, write any computation in the Excel formula language,” Gordon and Peyton Jones wrote. 

An example of what LAMBDA is capable of is the ability to recursively reverse a string using a fixed-point combinator. This is something that previously could only be accomplished with an external language like Visual Basic or JavaScript. “One of the big missing pieces in Excel formulas has been the ability to loop… to repeat over a set of logic at a dynamically defined interval. There are ways that you can manually configure the interval at which Excel recalculates to mimic this to an extent, but it’s not inherent to the formula language. That changes with LAMBDA,” Brian Jones, head of product for Excel at Microsoft, wrote in a post.   

In this example, a function called REVERSE is defined and uses functions HEAD and TAIL to compute the first character and everything but the first character. It can use this REVERSE function to give what the reverse of an entered word, number, or phrase is. It can also reverse the output of that to give back what the input originally was. 

LAMBDA is currently available to members of the Insiders: Beta program. Its current release has a few implementation restrictions that Microsoft expects to lift in the future. 

“It will be interesting to see how users continue to experiment with and apply not only LAMBDA but also data types and dynamic arrays. We believe these new functional programming features will transform how people make decisions with Excel,” Gordon and Peyton Jones wrote.