Data / Development

Microsoft Excel Becomes a Proper Programming Language

7 Feb 2021 6:00am, by
Screenshot - Speaking at POPL 2021, Advait Sarkar (from Microsoft Research Cambridge)

It may be the oldest piece of software still in widespread use. It was 34 years ago, just three years after Apple introduced its very first Macs, that Microsoft released the first version of its familiar Excel spreadsheet app, initially a rough copy of Dan Bricklin’s VisiCalc. Fast forward to the future, and the Irish Times noted in 2017 that Microsoft CEO Satya Nadella was calling Excel Microsoft’s most important consumer product, pointing out that it had over 750 million users.

So it feels almost historic when one of the world’s largest corporations augments a crucial component of its Office software suite — yet sure enough, Excel has been upgraded with a major new feature.

Microsoft’s researchers believe they’ve now finally transformed Excel into a full-fledged programming language, thanks to the introduction of a new feature called LAMBDA. “With LAMBDA, Excel has become Turing-complete. You can now, in principle, write any computation in the Excel formula language,” a Microsoft blog proclaimed.

“Being Turing complete is the litmus test of a full-fledged programming language,” explained a new article in Visual Studio magazine. And it adds that “early community response has been encouraging,” noting that Microsoft researchers are enthusiastically envisioning skilled Excel users creating functions “that appear seamlessly part of Excel to their colleagues, who simply call them.”

Here’s a look at these changes, and what they portend for the future of Excel, as a programming language.

Programming with Excel

Excel users do much of their work using formulas — where the input into a cell starts with an equals sign followed by some kind of calculation (“=A2 + B2”). Microsoft’s blog calls formulas the world’s most widely used programming language — yet it had always been limited to a pre-defined universe of options. To roll their own custom functions, users had to use Microsoft’s other macro-based programming language, Visual Basic for Applications. (Or, starting in 2018, JavaScript — and of course, Microsoft’s JavaScript-superset TypeScript.)

In a video appearance at POPL 2021, long-time Microsoft researcher Simon Peyton Jones noted that Excel’s end users were really implementing functions using JavaScript, a point reiterated in a Microsoft Research blog post by a senior principal researcher and a senior principal research manager. “Excel formulas are written by an order of magnitude more users than all the C, C++, C#, Java, and Python programmers in the world combined.”

But now all those users can write actual programs without leaving the world of Excel formulas. More specifically, formulas written in Excel can now be “wrapped” inside a named LAMBDA function — and it can then be called from anywhere else in the spreadsheet. And yes, it supports recursive programming, so you can even call your named function from within your named function.

The names are supplied in the “Name Manager” choice tucked away in Excel’s Formulas toolbar. And just like git, users can enter a comment when creating their function.

Creating a named LAMBDA function in Excel (screenshot from Excel blog)

As Microsoft Senior Researcher Jack Williams described it at POPL 2021, “In Excel, it is now possible to build real, full-fledged programming experiences… We can now start to build things that look like real programs.”

The blog post provides an example: a cell whose value includes the name of a function.

Screenshot from Microsoft Excel blog - LAMBDA function using Excel cell address as an argument

The value ultimately displayed in that cell is the output of the specified function — and that function’s argument is…the address of another cell.

Alternately, a function can also accept an array (holding multiple values) as its argument — since Excel began adding the ability to define arrays in Excel in September of 2018.

Screenshot from Microsoft Excel blog - Excel array as an argument in a LAMBDA function

A function can also send back an array as its return value, with its values “spilling out” into multiple cells. In his video presentation at POPL, Williams writes a function that instantly generates a calendar.)

And Excel’s blog post promises more array manipulation functions are coming in the future. Microsoft’s Research blog even promises the company is working on “efficient implementations of array-processing combinators, such as MAP and REDUCE” to be used on the output of named functions.

And Excel’s program manager Chris Gross hinted about even higher hopes for the future. “I would love to see us add much-needed tools for debugging and authoring formulas,” he wrote in a comment. “Akin to what you get with great IDEs.”

A Dream From 2004

For Simon Peyton Jones, this represents the fulfillment of a long-standing dream. Speaking at POPL 2021, the long-time Microsoft researcher and functional programming advocate told the story of visiting Microsoft’s Excel team way back in 2004. “I learned that Excel is like a supertanker. It has a very high value. It has a very large mass. And it’s operated by a surprisingly small and heavily-overworked crew. So it’s not easy to change course!”

But 10 years later he discovered that one of the mid-level program managers, “those friendly, receptive folk that I mentioned, had since been promoted to be the great boss of Excel… Suddenly we begin to get senior, executive-level buy-in for some of these ideas.” And, even more importantly, the engineering support to make it happen.

Screenshot of Simon Peyton Jones speaking at POPL 2021 about Excel LAMBDA functions.png

Microsoft’s Research blog calls this new LAMBDA feature “a qualitative shift, not just an incremental change.” Named Lambda functions offer programmers the high-quality language-like attributes of “composability” and “re-use,” respecting one of the long-standing principles of good coding, namely not repeating work.

A named function can even become part of another named function elsewhere in the spreadsheet. But that’s just the beginning of what could be even more elaborate constructions, according to the Research blog post. It ultimately envisions “sheet-defined functions” where several different functions, each defined in different cells, are collectively used to define a larger function.

And interestingly, the concept of sheet-defined functions was first described in a 2003 research paper co-authored by Jones. “Our case study is unusual in that it highlights the way that programming language insights can be applied to a product not normally considered as a programming language,” Jones had written.

Also speaking at POPL 2021, Advait Sarkar, a senior researcher from Microsoft Research Cambridge, envisioned additional cells being used for commenting the code and adding formatting flourishes. “We view programming language design as a research discipline whose goal is to create more usable human-computer interfaces,” Sarkar says at one point.

Last year, Sarkar co-authored a paper imagining a new Excel concept called Gridlets — in which a selection of cells could be copy-and-pasted, allowing the reuse of a group of formulas, ultimately offering a kind of object-oriented counterpart to sheet-defined functions. Gridlets could be instantiated again and again, each with its own unique parameters or “properties,” while changes in the original parent gridlet will propagate to its children.

creenshot of Brian Jones speaking on Excel LAMBDA at POPL 2021It’s the work of the “Calc Intelligence” team at Microsoft Research Cambridge, which has as its stated goal enhancing Excel as a programming language. Right now it’s only available to members of the “Office Insider” program’s beta channel. Although even within that program, eager users complained that they weren’t rolling it out fast enough, remembers Excel’s head of product Brian Jones in his POPL 2021 appearance. Williams said the feature had generated a lot of interest. “Even within the first few weeks of LAMBDAs being released, there were videos with hundreds of thousands of views on YouTube. There’s really big space to explore how spreadsheets and higher-order functional programming can co-exist and produce a unique experience.”

The post on Microsoft’s Excel blog ends by promising named LAMBDA functions are just the beginning, adding “join us for the journey.”


WebReduce

A newsletter digest of the week’s most important stories & analyses.