Excel Hell: It's not just blame for pandemic pandemonium being spread between the sheets
- Reference: 1601989514
- News link: https://www.theregister.co.uk/2020/10/06/excel/
- Source link:
16,000 cases lost – purportedly in a CSV conversion blunder involving an out-of-date version of Excel? In a multibillion-pound, "world-beating" contact-tracing system? Unnoticed for a week of rising infection? In a system known to be broken for months but still not fixed?
Ridicule and despair, those shagged-out nags of our [2]Johnsonian apocalypse, once again trudged exhaustedly across the plaguelands of England.
But the true horror is rooted much deeper and the underlying sins stretch much wider than Number 10. Of that sad catalogue of fail, one item which was widely blamed for the chaos deserves our very finest scorn. One alleged piece of the jagged little jigsaw is a global pandemic all its own, poisoning our data and sickening our businesses for 35 years. In five years' time, with some luck and much work, vaccines and social changes will see COVID-19 demoted to flu status, yet Excel will be with us still.
The government has said that its "mishap" won't "materially alter the course of the pandemic": you and I know that can't be true, not if test and trace is having any effect whatsoever. People will sicken and die because of this mistake, whether it's connected to the cursed spreadsheet or not.
Use spreadsheets for their intended purpose
For your edification, I present this list – [3]44 pages long – of spreadsheet horror stories, of data entry, calculating, modelling, and analytic chaos that has cost hundreds of millions of pounds and put endeavour of all sorts at risk.
To complete the evidential bundle, I can only refer to my own experiences of a lifetime of dealing with spreadsheet abuse, some of it not even my own, in the sure knowledge that you too will have been a victim. Some [4]research says that more than 90 per cent of spreadsheets have errors in them, and that's before those that think they're databases, or document management systems, or org charts, or security front-ends or… the list is infinite of spreadsheets doing what spreadsheets should not, and doing it extremely badly.
I say spreadsheets, because all are bad, but I mean Excel, because that's the only one that matters. That's the compulsory one. The compatibility enforcer, the one that sets the rules. The one that misrules.
Excel remains recognisably the [5]mutant offspring of VisiCalc , the original 8-bit small business product that started SME computing and made Apple, well, Apple. We are now 40 years on with computers a million times more powerful – 1.5 million times by Moore's Law – yet Excel continues to squat at the heart of business computing like a bowl of leeches in the ICU.
It is an execrable design, from any angle. It has forced generations of untrained business people to operate without help on their data through a letterbox using Lego bricks as scalpels. It knows not any modern data techniques – of structure, robustness, verification, documentation, modularity, versioning, variable typing, variable naming, bound labels. How the Hell is anyone supposed to build and use and communicate and maintain any sort of model where you have to build it cell by cell, with tricksy little links and kiddy algebraic naming conventions?
As for the user interface and accessibility – no condemnation is strong enough. The UI has all the out-of-time impracticalities of Gothic Revival with none of the redeeming aesthetics, the calculating engine can't calculate properly – just look at [6]Wikipedia's list of Excel "Quirks" – and it is wildly version, implementation and platform-dependent. It is a sixth-form programming project grown to the size of Godzilla.
If you mention VBA, I shall scream.
If you mention security, I shall scream louder.
For this, I blame Microsoft. People have to use Excel because it is the only data manipulation tool in Office, and Office is the only game in town. And because of this, Microsoft hasn't done an innovation worth a damn in personal data management for the common business user in decades.
It doesn't have to, and when Microsoft thinks it doesn't have to do something – [7]Internet Explorer 6 , m'lud, we have not forgotten nor shall we ever forget – the rest of us can go hang.
The result is an unending history of misery, at a corporate and a personal level, because Microsoft doesn't care. It's got your money, you've got to use its software, off you go. Microsoft can sink billions into vanity projects like quinquennial mobile strategies that nobody asks for, and it loves talking about AI and quantum and all the things.
But it hasn't done a decent job – any job – of the real hard problem, the one that needs dedication and innovation and insight and hard, interactive, experimental work: that of creating a business data tool that doesn't actually hurt us.
If there were health and safety rules for software, Excel would be up there with [8]radium cigarettes and arsenic gobstoppers.
In fact, where there are rules – such as in some medical regulatory environments – you can't just use Excel. You have to certify your particular application. But in the absence of regulation, in a distorted monopoly market run by a company still furious it has lost its dictatorial powers elsewhere, nothing will change.
It could be better. Microsoft could atone. It could sponsor design competitions. It could ask the world's finest digital design brains to set up the R&D framework for a revolution. It could look at the damage done and say: "We can do better."
It's a very hard problem. People and data do not mix well, but in the sacred names of Turing, Shannon, and Lovelace, we deserve a better answer than Excel.
We all deserve better than Excel. ®
Get our [9]Tech Resources
[1] https://www.theregister.com/2020/10/05/excel_england_coronavirus_contact_error/
[2] https://www.theregister.com/2020/03/27/prime_minister_tests_positive_coronavirus/
[3] http://www.eusprig.org/horror-stories.htm
[4] http://mba.tuck.dartmouth.edu/spreadsheet/product_pubs.html
[5] https://www.theregister.com/2013/01/31/when_lotus_met_excel/
[6] https://en.wikipedia.org/wiki/Microsoft_Excel#Quirks
[7] https://www.theregister.com/2012/01/03/microsoft_ie6_death/
[8] https://www.orau.org/ptp/collection/brandnames/radiumcigarettes.htm
[9] https://whitepapers.theregister.com/
Relax...
Ok, so you don't like Excel. You've made that perfectly clear. Like most IT tools, languages, there are quirks, nuances, whatever you like to call them. Excel serves a purpose. I hate to use the phrase 'in skilled hands', but that's the important thing. If you know what you're doing, Excel is a very helpful tool. It's not a corporate RDBMS replacement, but, unfortunately, that seems to get overlooked by those who want quick and dirty, and cheap, solutions to their problems. And that's why it's out there, and will stay out there.
Re: Relax...
While I agree with most of what you say, it's worth noting that almost all the actual uses of Excel in practice are as a simple spreadsheet. Which is undeniably useful, and something Excel is pretty good at. That's why it's ubiquitous.
Re: Relax...
> almost all the actual uses of Excel in practice are as a simple spreadsheet
Almost all actual uses of Excel are as an ETL (extract-transform-load) tool.
ie to download information from one app, reformat/reorganise it and send it to some other app - and it's extraordinarily bad at this - in ways that most users don't know.
Hence all the genes misclassified as dates, dropped initial zeros in phone numbers, bad handling of embedded commas etc
Re: Relax...
Google made a very fine tool for this, then uncharacteristicly open-sourced it rather than killing it: https://openrefine.org/
I used it to mark data up semanticly as RDF, but it can get data from damn near anything and turn it into damn near anything else.
Re: Relax...
This is a pandemic. It is serious stuff. The non programmer should ask a programmer when they need data crunching done, not do the car-racing equivalent of saying, "yeah, I can do that too" and driving their Ford Fiesta out onto the Formula 1 racetrack and start racing with them.
The programmer may say "you can use Excel for a while but I need the budget to do a replacement now because one day this will fail and you probably won't even know", that would be better than the non-programmers not knowing, congratulating themselves on a job well done getting the Ford Fiesta out onto the racetrack, and driving the car off the curve and into the audience.
I used a gratuitous car analogy, sorry.
Re: Relax...
>The non programmer should ask a programmer when they need data crunching done
Yes, cos the intern at the NHS in charge of getting data emailed from 17 incompatible NHS trusts into one report is "empowered" (sorry) to call up Crapita and demand a custom applications
Hurrah!
You have to grudgingly admire the perpetrators one of the greatest scams. You have to despair of the army of managers that have allowed this overgrown toy to take over the world.
This is just a teenage rant from 2000. I'm surprised the author doesn't refer to micro$oft.
Obviously most of it is hyperbolic verbiage, but the substantive parts are simply wrong. Excel is like mole grips: there are very few situations it's the really correct tool to use, but in an awful lot of others it's good enough to get the job done when you don't have the perfect tool.
Blaming the tool for someone's idiocy is silly; there are endless examples of idiots managing to idiot even with the right tools.
Downvoted because in my experience Excel adds a variety of bizarre idiosyncracies on top of the numerous normal hazards of spreadsheet use. I'm talking about things that didn't/don't happen in better behaved spreadsheets. My favorite -- the teacher who tried to copy and paste a column of zip codes. Excel inserted the first then copied down as expected. But it quietly incremented each code after the first 90210, 90211, 90212 ...
Excel only does that incrementing while doing autofill, not copy/paste. Granted, one can inadvertently invoke autofill with a clumsy mouse drag, but the Undo command is there for a reason, isn't it?
Having faced many abortions of "systems", "developed" in Excel, I can only agree with Rupert.
Using Excel as a proper spreadsheet to analyse some figures, fine.
But I've experienced:
* Timesheet system for a whole company automated with Excel 4 macros (not VBA, Excel Macros).
* Forecasting system so complex in 1-2-3 that Lotus threw up their hands in disgust and told my project manager to use a real language!
* Production completely bypassing an ERP system, doing planning and production in Excel and booking the end result back into ERP.
* Complex "databases" automated in Excel
And many more sins.
Ye Olde English Proverb
A bad workman blames his tools.
[No, not the singular!]
upvoted
upvoted because for decades my goto DIY tools were a medium sized swiss army knife and a pair of mole grips.
I have other tools now, but they dynamic duo get regular outings.
As for excel. Microsoft did try to upgrade it, calling the results Access. We all know how that went.
I get it
It’s Microsoft’s fault. Hmm. I remember when a small, little-known, company named, ah, ‘Lotus’ brought out a far superior product named Jazz. I remember it crashing and burning, for several reasons: it was Mac-only in 1985, it cost $600 (in 1985! Jesus bloody Christ, what were they smoking at Lotus?), it was aggressively and yet uselessly copy protected (at the time the standard Mac floppy was 400 kB, but the Jazz disk 1 was 405 kB, making it impossible to copy... unless you had a copy of CopyIIMac or similar, which copied it in minutes) and, most crucially, it sold under 20,000 copies (see ‘Mac-only’, ‘$600’, and ‘outrageously copy-protected’) while the then brand-new Excel v1 sold over 200,000 copies because while it was Mac-only (until Windows 1 showed up, that is) it cost less than half what Jazz did and was not nearly as user-hostile. Excel also did much less than Jazz, but went on to become _the_ killer app on Macs, Win 1-3, Win 95, Win NT... Lotus canceled a follow-up, ‘Modern Jazz’, which would have been much better and possibly run on Win 1-3. Lotus also didn’t bring out a Mac version of 1-2-3 until 1992, and brought out a Windows version of 1-2-3 far too late. Borland Quatro crashed, burned, and went to Canada, becoming part of Word Perfect Office from Corel... and notably stayed away from Macs after Corel killed the Mac version of Word Perfect. Excel was left as last man standing, with no real competition, and not due to anything Microsoft did. Lotus did try again with Symphony, but by that time it was too little, too late.
People used Excel because there was no real competition and because it was what they had. If Lotus, or Borland, or _someone_ had put something worthwhile out as competition to Excel, then perhaps things would have been different.
Brilliant! I nominate this for the "Single Most Useful Reg Article Ever" award.
I'm going to share it with every last one of my users who talk about "building an Excel database".
So good to have Rupert back, dual wielding uzis of contempt in a righteous manner. And completely correct.
Alternative?
Whats the alternative to Excel then? Is there a simple bit of software that allows you to create tables for data classification and statistical analysis without having to know SQL or Python etc?
My wife is using Excel for some casework analysis (how many cases were apealed, overturned etc along with various reasons). She is not very familiar with it and when I have been helping her it really strikes me just how easy it is to screw up and Excel spreadsheet/pivot table.
Re: Alternative?
"Whats the alternative to Excel then?"
OpenOffice/LibreOffice I suppose, but they seem to me more or less Excel look alikes. Koffice, but I don't think it's maintained any more. Emacs org-mode tables, but they are not for the faint of heart. Personally, I was kind of fond of the spreadsheet in Microsoft Works, but I think getting it to run in a modern software environment might be, at the very least, challenging. And converting it's files to any other current format might be even more difficult.
Re: Alternative?
AWK obviously - the answer to a text processing problem is always AWK
Re: Alternative?
If your problem is "I am using Excel for something that should not be done on a spreadsheet", Libre Calc, Gnumeric and so on are not the answer. They may be better in some aspects, but they still have the fundamental problem that they are spreadsheets.
Re: Alternative?
For something approximately single user I use CSV edited with a text editor for the data and python for report generation (reportlab) and emails. There are a mixture of costs and benefits.
The source data will probably arrive in multiple spreadsheets with fields in inconsistent columns. This can be converted to CSV an run through some python regexes to spot bad data, data in the wrong column, floating point phone numbers, duplicate records and all the usual mistakes caused by data entry into a spread sheet.
Update requires discipline to deal with commas inside fields and adding the correct number of commas when there are a few blank fields in a row but you can recycle your initial data correction tool to report these problems. (Update turned out to be completely beyond the ability of one trying-to-be-helpful Mac user who did not have/could not find a text editor or get a word processor to output in any format usable without whatever strange software she was using. [ended up with pdftotext and a script to get to CSV]) The good news is that problems are easily visible, machine detectable and human correctable without arcane knowledge of the guts of excel.
Next comes your first big payoff: automatically generated documents can have a consistently spelled names, addresses, emails and phone numbers for each job title. The entire document set can be automatically regenerated when any contact detail changes or when a different person takes responsibility for a job.
Basic queries can be done with awk | wc -l.
Mass snail mail can be handled with a PDF for the documents, a PDF with one envelope sized page per recipient and one of a number of companies that can combine the two and sort out postage.
Python has the libraries required to send emails with attachments. It is easy to add non-trivial logic to handle who prefers snail mail, who has expressed interest, who has already paid, output a list of who would receive what so you can check you got it right and send only to yourself so you can proof the output.
Python has excellent documentation but requires troublesome thinking skills. Reportlab has a barrier to entry but the PDF output will not display differently because someone has a different version of Word with the wrong printer driver selected. Taking the time and effort gives you valuable skills not tied to technological lock-in, spyware and adverts.
Finally on project handover some computer illiterate can import the CSV data into Excel and think they can do what you were doing because Excel is expensive software used by business professionals.
What should I use instead
Ok we should not use Excel. What should a non programmer use instead? If you are going to destroy the city you need to build something in its place. And it needs to be as easy to use, as powerful and not require a small army or programmers or consultants to maintain.
Re: What should I use instead
In the late 80's one of my first newbie tasks was to move a database off a Harris mini onto a PC and I used a simple DOS-based flat file database application that was easy to set up, could search by example, generate standard or custom reports with a few keystrokes, export data etc. Worked absolutely fine and ran off a floppy! That system worked really well for years until some IT department big-wig decided it needed migrating onto Access at which point complexity and lack of usability killed it totally for the users. After a while it got migrated onto Excel where, to be honest, it was horrible and difficult but it worked *for the people that needed it*.
If I want to do the same initial migration now the words to be spoken will probably include "Access", "SQL" and "Oracle", "cloud", "hardware upgrade", "internet stability" ... And, when the punter is scared enough of the prices, horrendous learning curve, cost of books and cost of courses and realises 90% of their investment is actually for useless software components because all they want is a simple flat file database that's easy for non-IT geeks to drive, they'll end up using Excel (probably badly) as there's bugger all else and they 'know' it ...
Perhaps, for the average punter, we need to take a step back to the database past and simplify so the punter has an alternative to Excel? But just think of the money we'd lose if people could actually do what they wanted to *well* with minimal input from consultants, support desks, training courses, books ...
Re: What should I use instead
How about just Excel, but with a flag that turns OFF all automatic data conversion? Just edit the strings that are there; don't strip leading zeros from stock numbers or interpret gene names as dates.
It would be AWESOME simply to have an application to edit data in CSV files that DOESN'T "re-imagine" data values whenever it bloody well feels like it.
Re: What should I use instead
Percentages? DON'T TALK TO ME ABOUT PERCENTAGES!
Re: What should I use instead
... simply to have an application to edit data in CSV files that DOESN'T "re-imagine" data values whenever it bloody well feels like it.
Yes !
Have another upvote.
O.
While there are times when an Excel spreadsheet is perfectly adequate for the job, it sounds like using it for contact tracing, with what has to be tens of thousands of records, if 16000 can get lost without anyone immediately noticing is clearly not one of those times when it was the right tool.
Its worse than that...
When I was "young", I worked on a sales forecasting system which downloaded data from a VAX Oracle database and performed calculations in Lotus 1-2-3. The project manager wrote a quick-and-dirty prototype in 1-2-3 and presented it to the customer as a set of "working" screen mock-ups. The finished project should have had a database and use C++... Only the customer said, "no, 1-2-3 is great! All our sales people have that already, just get the prototype working!"
I was called into the project at that stage and no amount of wailing helped. The customer was adamant. So, we expanded the 1-2-3 model. It ran in DOS, it had around 40 sheets it loaded in one after another and ran calculations... Then it started doing funny things.
Self-modifying /-code macros didn't help (dynamic cell references, as 1-2-3 didn't have variable). It worked fine in debug mode, stepping through the thousands of lines of /-codes. But actually run it? It fell over randomly and gave the wrong results. After tearing our hair out, we actually contacted Lotus support. They asked for a copy of the spreadsheet, they got a 2MB bundle of tables (we are talking DOS here, 640KB main memory, 1MB with Himem.sys and a 40MB drive!).
They looked at it. They wept. Their official answer was, "forget it, 1-2-3 was never designed for anything this big or complicated!"
When you need something fast without the hassle of creating and hosting a database, access, permissions ,defined structures then excel is your go to albeit it should be your temporary go to. You can't email a database or chuck it on a network drive for someone to access however every one can get into a spreadsheet. In the case of track and trace they needed something quick that could work with all the various companies and individuals doing the track and trace and enable work flows from said data. Why they didn't move it from Excel is beyond me especially the amount of money thrown at it. The way I see excel is that it doesn't need to get better because it does what it's supposed to with spreadsheets/data. Now if someone comes up with an easy to use/setup database (I'm talking excel user easy and universal software wise) they might be onto a winner.
My thought was that someone mocked up an example of how it could work (PoC) using Excel and for some reason that is where things stopped...
By why, just why, was XLS used rather than XLSX, that bit seems almost like someone breaking things on purpose!
Maybe due an ancient library for reading files somewhere that only understands XLS?
VBA Security Security Security Security... I can't hear you!!!
This sounds more like a FOTW than an El Reg article. Amusing, makes some interesting points, but really belongs in Bootnotes.
All this really boils down to "A fool with a tool is still a fool".
Excel is great at what it does - really. It has simplifed or speeded up my job no end on occasions. It has faults and limitations (some very basic and silly), but I've not used a tool that hasn't.
And yes, an excel spreadsheet is likely more secure than a database set up by someone who has no idea what they are doing. After all, it is rather difficult to locate a file with a simple port scan...
Re: VBA Security Security Security Security... I can't hear you!!!
"Excel is great at what it does". Yes, absolutely.
I have rerun some calculations I did in my student days 50 years ago. Exercises that took a half day or whole day are now done in a few minutes.
Blame the user not the tool
This reminds me of Bjarne Stroustrup's observation that there are two kinds of programming languages - the ones people complain about and the ones nobody uses. Excel is a good general-purpose tool for what it's meant for. There are lots of horror stories because it's so widely used. Any other tool that was as popular would have people misusing it. Most of the horror stories listed are human error.
Having said that, Excel is not meant to be used as a database (why MS makes Access and SQL Server). It's not MS or Excel's fault this happened, it's people stupidly importing csv files into a spreadsheet program when databases are the right tool for the job and freely available.
Anyone Remember Lotus Imrov
No not Lotus 123, of course you remember that if you are old enough.
Lotus tried to fix the problems with 123, which are the same problems Excel has today.
Lotus Approach was also not bad if you wanted something a bit better than a spreadsheet but not as complex as Access. People would have had that on their Smartsuite CD back before they switched to Office, but they never used it.
Microsoft has PowerBI which seems to be trying to do the same thing. I haven't looked at it much, and probably nobody else has either.
You can try to make better things, but people will continue to misuse spreadsheets because that's what they know.
Re: Anyone Remember Lotus Imrov
PowerBI is fast overtaking Tableau as the tool of choice in Business reporting. The underlying data engine is also built into excel as powerquery and can handle 100's of millions of rows very easily in excel.
Re: Anyone Remember Lotus Imrov
The road to success: a PowerBI application developped by Accenture...
The same company produces an operating system where the default is to hide file extension, thus allowing any scammer to send a file called 'nothingtoworryabouthere.pdf' and hiding the last part '.exe'
Microsoft would read: 'It could look at the damage done and say: "We can do better."'
as
"We can do better, we have not yet damaged the world enough. Let us redesign and add even more bugs to the product and gaze upon the carnage."