3 things you don’t understand about spreadsheets (Part 1)

The world has a love/hate relationship with spreadsheets.

People loath them, and use them as a metaphor for the cold, the calculated and the impersonal. Yet hundreds of millions of people hardly go through a day without using them.

“Sophisticated” programmers and IT people like to frown upon their usage. Yet they’re fighting a losing battle as spreadsheets are everywhere. And used for everything. Including — granted — things they are not right for.

But there are 3 things that most people don’t understand about spreadsheets. I’ll cover those one by one in a series of three posts.

#1 Spreadsheets are programs

Look at me. I’m a program!

This is the single most important thing to understand about spreadsheets. Once you get your head around this, everything else about them and their use falls into place: Spreadsheets are programs.

Spreadsheets are programs and Excel is the most popular software development environment in the world!

Or as one of the smartest researchers in this field — Felienne Hermans — likes to say: “Spreadsheets are code” (and she makes a pretty damn convincing argument).

In fact they are both programs AND code. In the world of spreadsheets there is no distinction between the runtime and the software code.

Granted, spreadsheets are not a linear sequence of operations that are executed in succession. Instead:

  • Relationships between data elements are encoded in a declarative way (functional)
  • These data elements are automatically reevaluated when anything they depend on is altered (reactive)

And if you’ve been keeping up with the literature, you’ll know that functional and reactive programming are all the rage. In fact, I’d argue that currently spreadsheet programs are the only mainstream environments that come close to Bret Victor’s ideas on instant feedback and “Inventing on Principle” (if you consider yourself a nerd and haven’t seen this presentation yet, drop everything and watch it NOW).

The third characteristic of spreadsheets as programming environments is that they mix data, logic and presentation.

This is a key strength and weakness at the same time:

  • It’s a strength in the eyes of regular users that don’t think about such abstractions and can just start typing in data (without thinking about data types or data models), logic in the form of formulas (without thinking about testing, code reuse or documentation) and presentation (without thinking about client development, device form factors, etc.). In fact they use spreadsheets to build everything from pure databases to print-out forms with no data or logic element. Without a second thought.
  • But it’s equally a weakness in the eyes of professional software developers where such lack of abstraction is blasphemy as they need to think about testing and quality assurance, maintenance, co-development and operations.

In the early days of the personal computers everybody understood the nature of spreadsheets instinctively: They were a way for regular users to write their own business applications instead of having to buy, or worse — wait for — someone to pick up on their specific need and write software for it. They were a way to turn the generic purpose machine that was the personal computer into a machine that addressed your specific need.

Or as Ben Rosen wrote in the Morgan Stanley Electronics Newsletter in a 1979 review of VisiCalc, the first spreadsheet for a personal computer:

Though hard to describe in words, VisiCalc comes alive visually. In minutes, people who have never used a computer are writing and using programs. […] You simply write on this so-called electronic blackboard what you would like it to do — and it does it.

But more about the early days of spreadsheets in part 2