Spreadsheets are everywhere. They are simple to create and are an immensely powerful tool. Unsurprisingly then this means that a lot of areas of business rely on spreadsheets to function correctly. But spreadsheets are dangerous too. They suffer from, well-known, fundamental flaws.
The problem is that spreadsheets are a special type of code, and I’m not talking about the Excel ‘macros’ I’m talking about the formulas. As such they probably need to be treated the same way as other types of code, but their very nature makes this difficult. But I’m getting ahead of myself, let’s first look at some of what is good and bad about spreadsheets.
Pros
Spreadsheets are remarkable for their:
- Utility – we can bend them into almost any shape we want because they give one way to represent almost any business process;
- Portability – we can pick up our little gobbets of data and logic and relocate them to almost anywhere inside or outside the company, in file-systems, mail servers and web-sites;
- Simplicity – you don’t have to explain a spreadsheet to anyone. They might have to be a proto-genius to figure out how it works but the working knowledge they would need to get started is pre-loaded in their heads and ready-to-run.
Cons
So they sound pretty useful, and I like to think that I’m a pragmatic guy, so why do I hate them so much? Many have noted about the shortcomings of spreadsheets. The page on spreadsheets at Wikipedia spells it out clearly enough so I’ll paraphrase:
- Productivity – Working with spreadsheets requires a lot of “sheet-shuffling” to reach the required goal. The bigger the sheet, the more time is spent copying, cutting and pasting cells around.
- Reliability – Although what consitutes an error in a spreadsheet is subjective, the paper A Critical Review of the Literature on Spreadsheet Errors” (pdf) reveals a series of studies (some more recent than others) that have shown that approximately 5% of cells contain errors.
- Collaboration – Sharing a spreadsheet is difficult. Having two independent people working on the same sheet and merging their results is as far as I know impossible.
The first two items don’t bother me overly. Yes, it’s a problem but then the alternatives aren’t that great either. Consider what you would do if you didn’t have a spreadsheet to fulfill the task. You’d either do it with a bit of paper and a calculator (i.e. simulate a spreadsheet) or get a programmer to do the task for you. Either way the amount of productivity loss/gain and the amount of errors aren’t going to be that significantly different from using a spreadsheet. Don’t get me wrong, I love my fellow programmer, but we make a LOT of mistakes too. The difference perhaps is that bespoke systems usually end up getting audited (and hence fixed) and spreadsheets often don’t. Although this point is probably moot.
Good + Bad = Too Bad
My real beef is with what happens when you have the ‘pro’ of high portability with the ‘con’ of low collaborative power. You have no way of knowing which version of the spreadsheet you have is the “true” one, and which version is duff. Every copy, whether it be inadvertently through forwarding a sheet by email to someone else or explicitly by taking a ‘backup’ is a 12 foot tall baby-eating, business-crushing monster waiting to rip you and everyone you love apart.
Hug the Monster, Then Run
The thing is we kind of have to embrace the baby-business-beating monster because it’s about all we’ve got. There are some tasks, as a programmer, that I’m really happy that you as the non-programmer don’t bother me with and solve yourself in sheets. Want to set-up an intra-company phone-book as a spreadsheet so you don’t have to bother will all that “Access” voodoo? Be my guest, but I’m watching you. Want to set-up a spreadsheet to run your fantasy football so you don’t have to add two numbers together? Go right ahead, I’ll even drive you to the game so you don’t miss the turn. Want to set up a spreadsheet to calculate payments and and do a mail-merge with the results … STOP. RIGHT. NOW.
The truth is though that you might not know that you’re creating the mother-of-all spreadsheets when you start. I might not know it either but there will probably come a time when a line is crossed and then I will want to know what you’ve been doing and who you’ve been doing it with. I’m just like that.
Unless you are small company (and hence don’t have a lot of choice) you have to be very afraid of trusting anything that might lose you money to a spreadsheet. You need to be very aware of the risks and the potential-costs you are letting yourself in for. Here in Europe there is even a special interest group dedicated to highlighting the risks of spreadsheets. Those guys must throw wild parties …
The Missing Links
In my opinion there is something missing, something that can fill the gap between spreadsheet and system.
I think we need something that can:
- Track spreadsheet changes – Not knowing which spreadsheet is “true” and which lies (by being able to identify revisions of the sheet that have happened after yours was ‘branched’), and not being able to merge sheets is a problem. Perhaps someone solved it already, if they had that would be great.
- Track spreadsheets themselves – Having some more information about what sort of corporate-data was being accessed, who was using it and how frequently they ran it might alert us to potential spreadsheet monsters being born.
- Narrow the gap – Making spreadsheets more like traditional software systems, without significantly castrating the usefulness of the spreadsheet, would be great too. This is a little like asking for the moon on a stick though.
Perhaps I’ll make something like this one day. I have to admit it’s not a terribly exciting project but it has some potential I think. Perhaps I could spice it up by throwing a party and invite the guys from the “European Spreadsheet Risks Interest Group”. Now we’re talking. How will I budget for the 7-up, party hats and streamers? In a spreadsheet of course.
2 replies on “The Spread-able System”
Hi there.
All valid points and well made. FYI I come at this from a user’s point of view rather than a developer’s.
Out of the pro’s that you mention regarding spreadsheets, the utility one is by far the dominant feature. A piece of paper is also simple and portable (and has utility too, I guess). In particular, in the business environments which I have experienced, the ability to quickly knock something together to solve a problem is essential. In developer terms, I guess you would call this rapid prototyping. Consider a business where, for each ten client requests, only one gets executed. Then to value those requests, you are not going to build every single one into a system. You will do a quick and dirty on a spreadsheet; if the request is executed then only then will you consider putting it into a fully-fledged system.
Re. the cons, I also agree that by far the biggest (and probably only important) one is the inability to track changes. This explicity means that there is no audit trail and so any concept of process control disappears.
But I would add another con too – that of lack of scaleability (sp?). We have a simple problem, so we build a simple spreadsheet. Then we add a few tweaks, expand a few tables, add a few scenarios, and before long we have a monster. Spreadsheets are NOT scaleable. They are good for simple problems, but beyond a certain size thet become impractical and unsupportable.
So I think we need two things. The first, and hardest, is a spreadsheet version of SourceSafe or Subversion. This would allow changes to be tracked, an audit trail to be built, etc. I assume this is nigh-on impossible as no-one has done it so far – I leave that to the more technically minded to decide on. But I would have thought any product like this would have sold like hot cakes.
The second is the ability to take a spreadsheet and use that to develop a system. Some businesses let users develop their systems on spreadsheets, and then take them and use the spreadsheets as the requirements specification. There is a package out there (I can’t remember the name but will try and dig it out) which turns spreadsheets into C++ modules. Hence, after a spreadsheet reaches a certain size, it can be turned into a “real” system (whatever that means).
Re. your comment on narrowing the gap. Has anyone looked at a finance system called Front Arena from Sungard? It is basically set up to work similarly to a spreadsheet. But it proves quite hard to sell. Client: “Tell me, what can your system do?” Sungard: “Anything you want it to.” And then the client buys it and it doesn’t do anything until the client has spent ages telling it what to do… it’s very flexibility is its Achilles heel.
[Aside: a good interview question for salespeople is “tell me what a spreadsheet can do.”]
I wonder if putting the sheet online would solve many of the problems? Replace Excel with Google Docs (http://docs.google.com/) or Zoho Sheet (http://sheet.zoho.com/).
Google Docs has a revision history, real-time collaboration on a document (and chat whilst you edit), e-mail notifications, and of course no versioning / distribution problem.
There’s no VBA, but there are Gadgets, written in Javascript.
And of course there’s a real API with libraries in all major languages, so we’re free of the Microsoft silo.
*Now* we’re talking.