The Excel Formula Language Is Now Turing-Complete

Brian Jones, head of product for Excel, recently announced LAMBDA, a new capability added to the Excel formula language. LAMBDA lets users define custom functions using Excel’s formula language, rather than JavaScript or VBA.

With the addition of custom functions that can call each other and recursively call themselves, Excel’s formula language becomes Turing-complete, effectively meaning that Excel users can compute anything without resorting to another programming language. The Excel community has already started to put the feature to use.

Simon Peyton Jones, a major contributor to the Haskell functional programming language and researcher at Microsoft Research, noted that the Excel formula language may be the most popular functional programming language in use in the world.

In a presentation from the Calc Intelligence team, Jones explained the rationale behind the new evolution of Excel’s formula language as follows:

When I first joined Microsoft 22 years ago, my first question was how can a functional programming researcher make an impact at Microsoft? So I soon zeroed in on Excel because Excel’s formula language is precisely a purely functional programming language. Moreover, it’s more widely used than any other programming language on the planet. [However,] Excel’s formula language, considered as a programming language, is terribly limited.
[...]
You can’t define new functions. You can only write formulas that call the existing built-in 600 functions that are part of Excel. I don’t really count writing new functions in JavaScript here because end users just can’t do that.

Jones mentioned that the ideas that eventually led to the implementation of LAMBDA date from the early 2000s. LAMBDA adds to Excel spreadsheets the most fundamental mechanism that programmers use to control complexity: the ability to define reusable abstractions.

A user-defined lambda function can be an argument to another lambda or its result; lambdas can return lambdas; lambdas can be named and recursively call themselves.

With the addition of LAMBDA, the Excel formula language thus becomes Turing-complete, which means that Excel users can perform any computation with Excel lambda functions.

Microsoft Research provides the following example of lambda functions that cooperate to reverse a string:

(Source: Microsoft Research blog)

The previous example shows three lambdas (HEAD, TAIL, and REVERSE) with REVERSE calling itself recursively, and using HEAD and TAIL for its computation. For comparison purposes, a function that reverses a string could be written in the purely functional language Haskell as follows:

reverse_str s = case s of "" -> s
                        c:cs -> reverse_str cs ++ [c]

c:cs effectively pattern-matches c to the head (the first character) of the string, with cs being the tail (the rest of characters).

Excel Lambda functions can be defined with the LAMBDA function as in the following formula, implementing the function x -> x + y:

(Source: Microsoft support article)

Lambdas can be named with Excel’s built-in name manager, making them easier to reuse in other parts of the spreadsheet. The previous illustration assumes that the lambda was named myLambda prior to its usage.

Microsoft provides plenty of examples of lambda in LAMBDA’s announcement post and documentation. The following lambda function for instance replaces forbidden characters in a string:

=LAMBDA(textString, illegalChars,
   IF(illegalChars=””, textstring,
     REPLACECHARS(
       SUBSTITUTE(textString, LEFT(illegalChars, 1), “”),
       RIGHT(illegalChars, LEN(illegalChars)-1)
      )
    )
)

Referring to the previous example, one Excel expert said:

Previously, you would have to use a function to remove the illegal characters one at a time. This could get very complicated because you would not always have the same number of illegal characters to be removed in each string, and it would need careful use of functions to prevent errors from occurring.

Following LAMBDA’s release in December 2020, the Excel community has also been at work producing examples of lambda functions.

LAMBDA thus makes it easier for Excel users - the vast majority of which are not programmers, to abstract commonly used functions behind a named formula. The alternatives include copy-pasting entire formulas in cells. The practice commonly generates very large, hard-to-read, hard-to-maintain formulas and has been known to generate errors that may go undetected for a long time.

As a programming language, the Excel formula language benefits from the Excel application (used as an IDE), its immediate feedback loop (achieved with interactive playgrounds, REPLs, or hot module replacement in other languages), and its dataflow programming model.

However, Excel, as a low-code application, may have underinvested in supporting large user applications. While modularity and abstraction, key enablers of development at scale, may have been improved with LAMBDA, automated testing, contracts, and typing, key features that positively impact robustness, are mostly missing, leaving plenty of room for human errors. Ray Panko, professor at the University of Hawaii, assessed that, on average, 88% of the Excel spreadsheets have 1% or more errors in their formulas. Cassotis Consulting commented on the study results as follows:

The frequency of errors generated while developing spreadsheets is similar to that of the development of programming codes. However, the latter go through many more tests and validation processes before they are officially used, while most spreadsheets are used soon after the first draft is developed.

As Doug Hudgeon, CEO of Managed Functions, explained to InfoQ in an article addressing low-code platforms:

Community developers create two types of risks. First, integration risk, which involves exposing data that shouldn’t be exposed. And second, transformation risk, which involves bugs or miscalculations in the app that lead to bad business decisions.

Typed cells could have lowered transformation risk and avoided a €750k loss that is attributed to a fund erroneously marked on the spreadsheet as a euro fund instead of as a dollar fund. The National Treasury Management Agency (NTMA), which purchased the fund, explained:

Subsequently, when the error was discovered, the dollar exchange rate had moved against the NTMA and the investment return was down €750,000.

Regarding typing, the Excel product team has already expanded the types that Excel understands. The team also experiments with logic-based type and dimensional inference, supported by machine learning.

Support for creating, versioning, publishing, importing, and debugging formulas may need to improve. Jones signaled future improvements for formula creation:

One thing that I can tell you that gets me every time is the experience of editing in the name manager ... definitely lots of room for improvement there.

The Microsoft team also works on sheet-defined functions, which let users define the parameters and outputs of functions as worksheet cells. Advait Sarkar, a senior researcher in the Calc Intelligence team, demoed how sheet-defined functions simplified the experience of creating functions. Worksheets used to contain sheet-defined functions may also be used to store tests that document the function or breakdown stages of a complex computation in miscellaneous cells. With the abstraction offered by LAMBDA, one may even dream of a library of value generators that supports checking formulas with property-based testing, like the Arbitrary class used by Haskell’s QuickCheck property-based testing tool.

The Excel formula language growing in capabilities also means that it is growing in complexity. Jones however noted:

Even if it takes greater skill and knowledge to author a lambda, it takes no extra skill to call it. LAMBDA allows skilled authors to extend Excel with application-domain-specific functions that appear seamlessly part of Excel to their colleagues, who simply call them.

The research team reported positive preliminary user feedback. One user reported:

Excel already has the opportunity to package up oft-repeated calculations as Visual Basic functions. However, what you are proposing is much to be preferred ... One [advantage] is performance; VB functions can be rather slow. [...] VB functions break the audit trail; not all of their behavior is determined by their parameters, as they can retrieve data from cells other than through the parameter list. Debugging VB functions requires programming skills; yours requires more standard spreadsheet skills.

Other users may fail to see the point. Mike James, the author of The Programmer’s Guide To Theory, objected:

So if you’re not bright enough to understand lambda calculus, you can just use it like a dumb ape ... We all know where that ends up. Spreadsheets are dangerous enough without making them super dangerous by way of academic obfuscation.
[...]
What we have here is an academic curiosity - a desk ornament or toy. It might amuse some people clever enough to recognize a lambda when they see it, but down-earth-spreadsheeting it isn’t.

In any case, Excel lambdas are just out and how they will be used by the Excel community will ultimately decide its practical usefulness. Programming experts may, for instance, implement DSLs with parsers and interpreters expressed as lambdas. Business users may then use those DSLs to achieve their business purpose with a lowered possibility of unintended mistakes, better error reporting - and the friendliness, immediate feedback of an Excel environment. Intermediate users may extend a DSL without getting into an actual programming language like VBA or JavaScript, whose integration with Excel adds security issues.

While lambda functions are likely to be abused in ways that remain to be understood, and a new class of errors is poised to see the light if no care is taken, the feature may also simplify formulas already existing and new worksheets, thereby reducing the potential for basic mistakes. Like in other programming languages, errors born from blindly copy-pasting code (formulas) will continue to occur. They may, however, be less surface for errors if the formulas are shrinking, through the reuse of properly tested user-defined lambda abstractions.

Ultimately, spreadsheets have just become more powerful for a large majority of users. As Jonathan Edwards puts it:

The simplicity and utility of spreadsheets put programming to shame. We believe the power of spreadsheets is that they offer a computational substrate: an autonomous artifact combining code and persistent data that is presented through a simple spatial metaphor.

Users interested in trying LAMBDA may join the Office Insider Program and choose the Beta Channel to get early access. Feedback and suggestions are welcome and can be posted within the program or on the Excel Tech Community forum.

About the Author

Bruno Couriol holds a Msc in Telecommunications, a BsC in Mathematics and a MBA by INSEAD. Starting with Accenture, most of his career has been spent as a consultant, helping large companies addressing their critical strategical, organizational and technical issues. In the last few years, he developed a focus on the intersection of business, technology and entrepreneurship.