Why did Public Health England’s Covid contact system lose 16,000 coronavirus cases? Because the Excel spreadsheet ran out of rows.

That sounds like an unfunny joke with a dumb punchline, but tragically that’s exactly what happened in the last week of September 2020.

It’s not the first time that using spreadsheets for something they weren’t designed to do has reaped terrible consequences, and sadly it won’t be the last.

Let’s dive a little deeper into spreadsheets.

Spreadsheets are Fantastic

I’m a spreadsheet geek and have apparently found it necessary to create over 600 of them in my past 8 years at UpShift. If I’m nutting out an issue around data and logic my first port of call is often a spreadsheet.

As a child I was introduced to spreadsheets in the 1980s by my father, Brian Easton - a prominent economist and statistician. Back then the recently released spreadsheet software were revolutionary tools for analysing and manipulating data.

My father and I started with SuperCalc, moved to Lotus 1-2-3 and finally settled on Excel as the software package that is so ubiquitous the terms “Excel” and “spreadsheet” are interchangeable. For the past 8 years I’ve used Google Sheets, which brings a whole new level of power and interoperability to the humble spreadsheet.

But it’s not just IT geeks and professional statisticians who benefit from spreadsheets. Spreadsheets represent a democratisation of computing power and function where anyone using a computer can perform complex calculations, produce charts and even dabble in basic programming. Empowering people with technology is a very good thing.

At the most basic level spreadsheets are an easy way to store and organise data. At their most sophisticated they represent a programming framework that can be used to build and run everything from business processes to complex scientific models.

Many of the apps we’ve created at UpShift have started their lives as spreadsheets. Spreadsheets are great flexible prototyping tools for building logic and data models. We’ve taken some very complex scientific, environmental and engineering models from spreadsheets and turned them into human friendly digital tools.

Talk to anyone with a view of a large organisation's IT infrastructure and they’ll tell you some staggering numbers around how many spreadsheets are in their organisation and in some cases what business critical processes rely on spreadsheets.

But is that necessarily a bad thing?

Spreadsheets are Terrible

The flexibility that draws people to use spreadsheets is also their greatest weakness. Spreadsheets are like digital multi-tools that can be used for pretty much anything.

Like most mountain bikers I carry a multi-tool on rides, it’s a Swiss Army Knife type contraption that can adjust or fix many things on my bike. While it’s portable it can be clumsy to use and doesn’t work very well at all in more challenging situations like fixing a broken chain.

All bike mechanics and many bike enthusiasts will have a selection of proper tools in our workshops so we can work on our bikes without making a bunch of potentially damaging compromises. Even a chain fixed on the trail with a multi-tool should be checked and re-fixed using proper tools as soon as possible.

While in many cases spreadsheets are good for quick fixes, small jobs and situations where the proper tools are unavailable like the bicycle multi-tool they bring a bunch of compromises, from the benign to the dangerous.

First up spreadsheets are very easy to break. Each filled cell contains a value or a calculation, often connecting to a myriad of other cells in a vast web of logic and data. The wrong change to a cell can cause the entire spreadsheet to collapse like a house of cards.

Secondly it’s very hard to troubleshoot spreadsheets when they do break. More often than not the problem will result in hard to detect incorrect calculations rather than easily noticed error messages. For example, an analysis of 15,000 spreadsheets from the Enron Email Archive found 24% of them contained at least one formula error.

To further complicate troubleshooting it’s easy for other people to change spreadsheets. In most cases there’s very little control over what cells can be changed by who. As an example if you are sending someone a spreadsheet that calculates their carbon footprint, or tax requirements, or mortgage calculation how do you ensure they don’t accidentally disrupt or fiddle around with the calculations? There are ways of doing this using “protected cells”, but it’s not an easy method to manage or police.

Following on from the previous point spreadsheets have very little data integrity. Take a “Profit and Loss” report in Xero - every item is referenced back to existing data and can be verified back to the point the data entered the system. Copy that report into a spreadsheet and every item is just a value in a cell and can be changed at whim.

The ease of use, lack of access control and minimal data integrity add up to a security risk around the data itself. A common use of spreadsheets is storing contact information, which can often extend to more personal information. The ease that spreadsheets can be copied and shared opens up all sorts of issues around data privacy and who has access to sensitive data.

And finally spreadsheet scripting functionality can represent a huge security risk. In Excel these scripts are known as macros and are a common access point for hackers. Recently malware called “Panda Stealer” has used Excel macros in fake business quote requests to steal cryptocurrency from users that opened the file. Even more damaging ransomware (which locks businesses out of their data) can be delivered via malicious macros.

Faced with the potential security risk of macros, organisations have to decide between disabling macros across their organisation and potentially crippling spreadsheet based business processes versus leaving macros enabled but risking a damaging security breach.

Wherefore Art Thou Spreadsheet?

So, where have we got to?

Spreadsheets can be fantastically flexible digital tools which empower ordinary people to do extraordinary things, they can also be horrible unstable insecure nightmares which represent a substantial risk to people and organisations.

That’s not much help, is it?

Coming Up Next

At over 1,000 words in this post is already stretching past the short form format I prefer.

So, I’m going to break up my exploration of spreadsheets into (for now) three posts tentatively titled:

1. Spreadsheets are Simultaneously Fantastic and Terrible.
2. When You Should Stop Using a Spreadsheet. (Coming Soon)
3. Spreadsheet Alternatives and How They Can Work For You. (Coming Later)

In the meantime if you’d like to learn more about situations where spreadsheets are certainly the wrong tool have a listen to Cautionary Tales – Wrong Tools Cost Lives  by Tim Harford.

Photo by Power Digital Marketing