Microsoft Research Blog

Microsoft Research Blog

The Microsoft Research blog provides in-depth views and perspectives from our researchers, scientists and engineers, plus information about noteworthy events and conferences, scholarships, and fellowships designed for academic and scientific communities.

Influencing mainstream software—Applying programming language research ideas to transform spreadsheets

January 14, 2019 | By Andy Gordon, Principal Research Manager; Simon Peyton Jones, Principal Researcher

Simon Peyton Jones writing with a fellow researcher at a desk

Spreadsheets are the world’s most widely used programming language, by several orders of magnitude. We asked ourselves whether it would be possible to apply programming language research ideas to make spreadsheets a better programming language? If we could, that would empower a huge user community to do more.

One of the joys of working at Microsoft Research is the ability to directly influence mainstream software technologies – in this case, Microsoft Excel. And the Excel product team has been excited to partner with us. The core ideas of our collaboration are these:

  • Make Excel’s data values reflect the datatypes of our end users’ domains, by adding arrays, vectors, records, and even domain-specific data types implemented by third parties. A first step is to make arrays—already a very natural data type for Excel—into first-class values that can be computed by any formula; see Champagne Prototyping: A Research Technique for Early Evaluation of Complex End-User Programming Systems (VLHCC’04).
  • Make Excel functions reflect the abstractions of our end users, by allowing end-users to define new functions using an ordinary worksheet, rather than by switching to a totally different language (and computation paradigm) such as Visual Basic. See A User-Centred Approach to Functions in Excel (ICFP’03).

Through deep collaboration between Microsoft Research Cambridge and the Microsoft Excel product team, Excel is now moving decisively to reflect these core principles. The first pieces have already appeared: an Excel cell can now contain a first-class record, linked to external data sources. And ordinary Excel formulas can now compute array values, that “spill” into adjacent cells (dynamic arrays).

The Calc Intelligence team and Excel colleagues sharing a moment at Microsoft Research Cambridge, UK.

There is more to come: we have a working version of full, higher-order, lexically-scoped lambdas (and let-expressions) in Excel’s formula language and we are prototyping sheet-defined functions and full nesting of array values.

New directions

Our Calc Intelligence project works at the boundary between programming language research and human experience and design. This interdisciplinary work has thrown up interesting new ideas that will extend the reach and ambition of what end users can achieve with Excel, including:

  • Calc View. Traditional programming sees programs principally as textual code, with the data being delayed until runtime. In contrast, spreadsheet programming focuses on the data and its visualisation, with the code (the formulas) being almost entirely hidden. What if we were to provide both views at once? The grid would sit side-by-side with a textual “calculation view”, that could give far more insight into the computations that are going on, illuminated by comments, layout, and all the other mechanisms that programmers use to make their code robust, modular, and long-lived. See Calculation View: multiple-representation editing in spreadsheets (VLHCC 2018).
  • Elastic Sheet Defined Functions. End-users will initially define new user-defined functions on fixed-size arrays, but we would like to generalize them to work on arbitrary-sized arrays. You could come up with ad-hoc ways to do this for special cases, but could we imagine a principal generalisation of a single example to arbitrary sized inputs? We did. See Elastic Sheet-Defined Functions: Generalising Spreadsheet Functions to Variable-Size Input Arrays (in submission).

In each case, the needs of end users have driven us in intriguing (and often technically-demanding) new research directions. Our internal prototypes and strong cross-company relationships provide wonderful intellectual opportunities for research here in Cambridge.

Alongside our long-term partnership with the Excel team, we have close connections with researchers at other Microsoft Research labs who work on spreadsheets. For example, research by our colleagues at Microsoft Research Asia in Beijing on extracting insights from multi-dimensional datasets has influenced the Ideas feature in Excel, while recent research from Microsoft Research Redmond, Washington shows how deep learning can find errors in spreadsheets.

The Office team is thinking creatively and broadly about how Office apps can evolve to embrace the opportunities that surround us. There is no better time to be working in Microsoft Research alongside the product teams to change the future!

Up Next

Human-computer interaction, Programming languages and software engineering

Neural Program Synthesis and the Quest to Democratize Programming with Dr. Rishabh Singh

Episode 10, January 31, 2018 - We can program computers to do almost anything. But what about programming computers to… program computers? That’s a task that Dr. Rishabh Singh, and the team in the Cognition group at Microsoft Research, are tackling with Neural Program Synthesis, also known as artificial programming.

Microsoft blog editor

Programming languages and software engineering

Functional Programming Languages and the Pursuit of Laziness with Dr. Simon Peyton Jones

Episode 7, January 10, 2018 - When we look at a skyscraper or a suspension bridge, a simple search engine box on a screen looks tiny by comparison. But Dr. Simon Peyton Jones would like to remind us that computer programs, with hundreds of millions of lines of code, are actually among the largest structures human beings have ever built. A principle researcher at the Microsoft Research Lab in Cambridge, England, co-developer of the programming language Haskell, and a Fellow of Britain’s Royal Society, Simon Peyton Jones has dedicated his life to this very particular kind of construction work. Today, Dr. Peyton Jones shares his passion for functional programming research, reveals how a desire to help other researchers write and present better turned him into an unlikely YouTube star, and explains why, at least in the world of programming languages, purity is embarrassing, laziness is cool, and success should be avoided at all costs.

Microsoft blog editor

Programming languages and software engineering

How Programming Languages Quietly Run the World with Dr. Ben Zorn

Episode 6, January 3, 2018 - In an era of AI breakthroughs and other exciting advances in computer science, Dr. Ben Zorn would like to remind us that behind every great technical revolution is… a programming language. As a Principal Researcher and the Co-director of RiSE – or Research in Software Engineering – group at Microsoft Research, Dr. Zorn has dedicated his life to making sure the software that now touches nearly everything in our lives is easy, accurate, reliable and secure. Today, Dr. Zorn tells us some great stories about bugs and whales, warns us against the dumb side of “smart” objects, shares about his group’s attempt to scale the Everest of software security, and makes a great case that the most important programming language in the world today is… the spreadsheet.

Microsoft blog editor