All hail to the almighty spreadsheet!

The most useful application of all time

(Like this article? Read more Wednesday Wisdom!)

The first software product I ever shelled out real money for was a spreadsheet. I cannot remember the name of the product anymore, but it was for my BBC microcomputer (not ViewSheet though). I wanted it so badly that I was ready to spend my hard earned money on it. Not that I had a good use case, but I was just intrigued by the concept. In my small community of nerds there were lots of games being copied, but nobody had any interest in business software, and hence I had to pay for it.

More than thirty years after this event, on the occasion of me creating a particularly crafty spreadsheet, one of my colleagues created a badge in our internal who-is-who system called "Spreadsheets are my life" and assigned it to me .

I (still) make spreadsheets every day in both my professional and personal life. What my professional spreadsheets do is of course top secret, but my personal spreadsheets do things like calculating mortgage interest payments, helping me plan vacations, and equity modeling. This very Wednesday morning I created a new spreadsheet to examine the progression of costs over time in a backup service I am using. Spreadsheets are maybe not my entire life, but they are the single most handy tool in existence. All hail to whomever invented the spreadsheet.

Spreadsheets are in fact so useful that at the start, in the middle, and at the end of every project there are a bunch of spreadsheets. When we investigate a data model ⇒ spreadsheet. When we make a list of critical bugs to fix before launch ⇒ spreadsheet. When we are planning a reorg ⇒ spreadsheet.

Spreadsheets are at the core of corporate existence.

I am currently part of a team that is designing a tool for <something useful>. When I originally asked what the need for the tool was, my boss said: "We do a lot of this and it's always a mess of spreadsheets." My answer: "You do realize that no matter how good our tool is, there will always be a mess of spreadsheets, right?"

I predict that one of the core features of our tool will be to import and export spreadsheets :-)

Q: Why are spreadsheets so incredibly pervasive?
A: Simple: A spreadsheet is in essence a notebook with a built-in calculator. And there is almost nothing you cannot do with a notebook and a calculator.

Every problem you have that boils down to organizing information and doing some math on it can be done with a notebook and a calculator. Hence it can be done with a spreadsheet. That makes the spreadsheet applicable to almost every problem known to womankind.

Q: Is the spreadsheet the perfect tool for every use case?
A: No. It can be a good tool, great even, but never perfect. That's why we still have specific tools for specific use cases. 

Because of the generic nature of spreadsheets it is also quite easy to make a mistake, which might be hard to find. According to Bard, in 1985, a crash of a Delta Air Lines Boeing 727 was attributed to a spreadsheet error that led to incorrect fuel calculations. Then in 2009 (again according to Bard), a crash of an Air France Airbus A330 was linked to a spreadsheet error that led to incorrect speed calculation.

Now might be a good time to mention that I use a homebrew spreadsheet to do cross-country flight planning. If I ever get lost, I suggest the search and rescue crews start by checking that spreadsheet :-)

Not that bespoke tools don't contain errors. In the 1990s I filed a bunch of incorrect VAT returns because of a bug in the tool I used to calculate the tax amounts. Guess who the author of that tool was? :-)

The ubiquitous nature of the spreadsheet should give us some thought when it comes to building bespoke tools. Are we really doing something that can't be done with a good spreadsheet? 

The fact that every project, despite being supported by myriads of tools, still ends up with a bunch of spreadsheets when shit gets real should give us pause. Whenever a project manager starts sending spreadsheets around with the links to bugs that really must be solved before launch, I always ask them why the spreadsheet? Surely this is something our bug tracking or ticket management tool can do? Whenever I ask this they always look at me wearily and ask me to please get on with it :-)

Some time ago I was talking to a colleague about a bulk uploading tool we were supporting. When I asked how it worked they explained that the user could input the structure of the data, upon which the tool would create a spreadsheet for subsequent download. The user would then enter the data in the spreadsheet and upload it again. My colleague strongly disliked this approach, but I thought it was genius: Use a bespoke tool to do what the spreadsheet is bad at (help defining the structure, validating data against our complex backends, and then kicking off transactions in these complex backends to modify data), but use a spreadsheet for that bit of the use case that is really hard and probably different every time: Assembling the data that needs to be uploaded. Who knows how that bit goes? Every time we do this the data comes from different sources, it needs to be massaged in different ways before it is ready for upload, and multiple people might need to collaborate. A notebook with a builtin calculator is exactly what you want there.

Spreadsheets are also the perfect prototyping tool for badly defined problems that you haven't thought through yet.

For one service I used to be responsible for we had perennial capacity problems at all layers in the stack. At one point I was so sick of it that I spent days building a spreadsheet to capture numbers about traffic volumes, traffic distribution, latencies, error rates, load test results, number of servers and whatnot. Crunching all of these numbers, the spreadsheet then predicted how much of everything we needed at each layer in the stack. 

After I had adjusted the shapes of sizes in our infrastructure I updated the spreadsheet with the latest numbers from our monitoring infrastructure. Things had improved, but as it turned out I had not captured all relevant factors and some things that I thought were relevant were really not. So I adapted the spreadsheet to the new insights and ran the numbers again. Much better already. Over the subsequent years I would run the numbers every quarter, look at them, make some changes to the model or to key ratios, run the numbers again, and then adjust the capacity of our infrastructure according to the spreadsheet's outputs. That process made all capacity related problems go away. And as a bonus it also allowed me to predict the impact of proposed changes to the service like new features, turning up a new datacenter, or moving to a new server platform. And I got the “Spreadsheets are my life badge” :-).

If at that time a bunch of product managers would have descended upon me to tease requirements out of me for a capacity planning tool I would have given them my first spreadsheet model. However, that would have been an imperfect tool because of my imperfect understanding of the problem. They would have gone off and built the first version of tool that I would then have to use, only to figure out that it was not quite right, after which we would have to talk about a version two. All this would have been more expensive and would have taken a long time. The spreadsheet on the other hand allowed me to do exactly what I needed and I could adapt it to whatever my needs and latest and greatest insights were. The only thing that could have worked as well for me as a spreadsheet would have been a tool that was so flexible that for all practical purposes it would have been a spreadsheet.

All hail to the almighty spreadsheet!