Wednesday, February 3, 2021

# LAMBDA: The Ultimate Excel Worksheet Function

The Calc Intelligence project at Microsoft Research Cambridge has a long-standing partnership with the Excel team to transform spreadsheet formulas into a full-fledged programming language. The fruits of that partnership are starting to appear in the product itself. At the 2019 ACM SIGPLAN Symposium on Principles of Programming Languages (POPL 2019), we announced two significant developments: data types take Excel beyond text and numbers and allow cells to contain first-class records, including entities linked to external data, and dynamic arrays allow ordinary formulas to compute whole arrays that spill into adjacent cells. These changes are a substantial start on our first challenge: rich, fully-first-class structured data in Excel.

In December 2020, we announced LAMBDA, which allows users to define new functions written in Excel’s own formula language, directly addressing our second challenge. These newly defined functions can call other LAMBDA-defined functions, to arbitrary depth, even recursively. With LAMBDA, Excel has become Turing-complete. You can now, in principle, write any computation in the Excel formula language. LAMBDA is available to members of the Insiders: Beta program. The initial release has some implementation restrictions that we expect to lift in the future. We discussed LAMBDA and some of our research on spreadsheets in a sponsored video presented at POPL 2021.

Doodpants

"Your scientists were so preoccupied with whether or not they could, they didn’t stop to think if they should.” - Ian Malcolm

Anonymous

Do people still get the LAMBDA: The Ultimate X reference? AIM-353 came out in 1976.

I hope so.

What a bizarre choice of name. The name `LAMBDA` means nothing outside of CS and arcane mathematical circles.

Why not `FUNCTION(…)` or, if we’re being precise, `DEFINE_FUNCTION(…)`? Any spreadsheet user worth their salt knows what functions are and already uses the built-in ones, so a means to define one’s own functions is just a natural extension of that. And if anyone should appreciate the importance of name (brand) awareness, it should be the team responsible for Excel, on of the most famous, recognizable names in existence.

Oh, and I certainly don’t remember Logo ever making such a fuss, where that ability to define one’s own functions was the one—and only—core feature it had. Marketing sleight of hand for being 30 years behind, perhaps.

And above it all, the greatest irony: that many, if not most, Excel users do not actually use it as a programmable parallel calculator (aka spreadsheet), but as an ersatz database despite all the deficiencies that it has at that. Which none of these changes do anything to improve at all.

Honestly, I wonder where these mung beans keep their heads at times. How loud must millions of Excel users shout for them to realize where the product needs to go?

And above it all, the greatest irony: that many, if not most, Excel users do not actually use it as a programmable parallel calculator (aka spreadsheet), but as an ersatz database despite all the deficiencies that it has at that. Which none of these changes do anything to improve at all.

Honestly, I wonder where these mung beans keep their heads at times. How loud must millions of Excel users shout for them to realize where the product needs to go?

I’ve had my fair share of clients who essentially used Excel as an ersatz database, just like you describe. Oftentimes, the main limitation they were running into was Excel’s poor handling of 1:n relations, so I would move them over to a system that was good at exactly that. And, as it would turn out, worse at quite a few things Excel was great at.

I would like for Excel to become better at such fundamental factors. (I would also like it to look at Apple Numbers and realize its approach to sheet layout is much nicer.) But I worry that that ship has sailed. Not only have the market forces decided that Excel (or Google Sheets) is good enough; it’s also really hard to transition people over to a new paradigm.

This lamdbda feature doesn’t do any of that. It’s purely additive; odds are 99% of Excel users will never notice it. A better approach to being a database, OTOH, would probably by necessity disrupt their workflow.