28
votes
The badly thought-out use of Microsoft's Excel software was the reason nearly 16,000 coronavirus cases went unreported in England
Link information
This data is scraped automatically and may be incorrect.
- Title
- Excel: Why using Microsoft's tool caused Covid-19 results to be lost
- Published
- Oct 5 2020
- Word count
- 634 words
My inner database developer weeps any time someone uses Excel for anything over 100k rows. I'm of the personal opinion that any beurocratic process involving Excel is fragile not because of the tool per se, but because it was probably created by one person who quit the company years ago, but knew just enough to Google some macros. And now everyone uses that spreadsheet but nobody actually understands it, and can't/won't adapt it as their business needs change.
So many businesses are held together by these "magic" spreadsheets, it's terrifying.
My firm uses so many spreadsheets for all manner of stuff, and it's such a creaky and sluggish system. The answer to everything always seems to be "let's make a spreadsheet!"
Sometimes it's valuable, but there's other times (such as the software matrix that we're in the process of writing up, or the list of new joiners to the firm) where other tools would suit the use so much better. I've tried showing my bosses tools we could use in lieu of these monolithic spreadsheets, but they don't want to switch away from the tried and tested visual nightmares that we have.
Spreadsheets are the ultimate generalist tool to solve problems. And they are genuinely useful in a lot of cases!
Unfortunately, rote memorisation of Excel functionality along with its ubiquity and accessibility to not just developers but also white collar workers, an inability to apportion upfront cost to designing or migrating to new, more specialised systems that enforce the appropriate amount of correctness, and general organisational inadequacy and slow-as-molasses governance leads to Excel being used to support any number of truly disastrous scenarios that any person outside said organisation would immediately find suspect and likely conclude they're all lunatics.
Oh I agree, Excel is extremely useful, I've used it for many things myself. And you're correct, it is a flavor of database.
However it's only one tool in a toolbox. But if all you're familiar with is a hammer....
My point is less about Excel itself and more about it's mis-use being a symptom of some deeper dysfunction. It often belies a fragile set of processes or labrythian bureaucracy which all work just barely "good enough" and have difficulty handling mistakes. And certainly do not have the ability to handle a disruptive change.
I find it quite easy to pass judgement in this particular situation because absolutely nobody who thinks Excel is a valid software solution for a national-scale database should be anywhere near a national-scale project costing millions of pounds.
There are lots of restrictions on government IT work although not as much as you might imagine (I wish there was the degree of interoperability you're suggesting!) but none of that changes the fact that I'd be amazed if a work experience kid thought Excel was a good choice of data storage for this job. I once had to explain to a work experience kid that flat text file XML wasn't the ideal option for a much smaller dataset although he wasn't entirely wrong for suggesting it. That spotty teenager wouldn't have made this blunder.
It doesn't work, though, and that's the problem - it dropped thousands of records from a critical system in real use. In retrospect, yes there were possible fixes, checks, and workarounds that would have made it viable to keep using a spreadsheet, but someone would have needed to think of them in advance.
Using the right tool isn't just about whether it's theoretically possible, it's about whether it's safe and sensible to do so. This wouldn't have happened if they'd used a proper database, and more generally if they'd had the staff and process in place to point that fact out loudly, clearly, and repeatedly.
It isn't "good enough". It's an incredibly expensive potentially life-critical system, built over a course of several months, that failed in production. I have very little appetite to give them the benefit of the doubt on this one.
It was a comparatively minor mistake made because they were using the wrong tool for the job. It's possible to chop an onion with a hammer and chisel, but you'll get a better result with a far lower chance of injuring yourself if you just use a knife.
As for the complex circumstances, I do broadly agree. I think there probably are a few idiots in the mix, plus some smart, dedicated people whose warnings were ignored, plus a whole lot of vested interests, plus a staggering array of legacy systems and bureaucracy. And you know what? I don't care. They spent £12m of public money to navigate those problems. They have no excuse for failure, yet they fail time and again.
NO, Excel is not a database, basically or otherwise!
If we accept that Excel has moved past Lotus 1-2-3's origins as a glorified tabular calculator (which it has, partly), it is still in no way suitable for storing or processing nontrivial amounts of data. There is nothing resembling a schema, essentially zero tools for ensuring data validity, and both the format and application utterly fall on their face when faced with more than a few hundred thousand rows.
There is no credible way this issue could have arisen in a system backed by an actual database (even one from 1987).
Now, Excel is certainly useful; it lets business people create horrifying ad-hoc abominations without having to bother any software developers (which, as a developer, I'm always grateful for). It has no business in any designed or automated software system.
Admittedly I only used it for a college course years ago, but it seems like Access would be a better fit for this stuff than Excel. It has a lot (all?) of the benefits of a proper database with the usability and UI of Excel. There's a learning curve, but nowhere near as steep as learning SQL.
This is disappointing but not especially surprising. NHS software is an ongoing shitshow and while this is a particularly egregious bug, it's par for the course. They managed to spend just under £12m on this system in less than three months, which is one of those numbers that just gets handwaved away in the context of government spending; it's worth stopping to think just what could be built for that money, especially in the software world.
I feel exactly the same way. A friend of mine pointed out you could have a team of 50 people, each on a salary of £500,000/year (higher than 99.8% of British incomes), and if you pro-rate that to 3 months you've still only spent half of your £12m budget. When you break it down, it really is beyond comprehension.
Once you get into their larger failures, the thoughts of what could actually have been done with £10,000,000,000 at the height of austerity are just too big to envisage, at least for me. I've speculated previously on how it happens - the best guess I've got is the kind of genteel, ingrained corruption that's so endemic it doesn't even really get thought of as such.
You buy your software from deloitte, accenture and/or any other big deal consultancies.
At an old job, the admins kept a spreadsheet that had a list of every IronMountain box (20k or so) that had the UPC in one column followed by the team and a description of the contents. One of their admins at some point sorted only the UPC column, but nothing else and promptly saved over the backup.
We had tape backups going back about two months, but even the oldest one was messed up.
It's crazy that basic Excel isn't taught in every office -- at least to those who use it as part of their job. More importantly, it should be taught from the get-go that, like @whbboyd said, spreadsheets are not databases.
Almost every job I've gone into, I've tried to move my team over to databases, but there's so much resistance (even within Change Management teams.)
More than any other piece of software, I'm willing to wager that bad Excel has cost the economy the most... but I am also willing to accept that my view on this is skewed by experience.