Spreadsheets are staples of business life. But — as with sausages and laws — it is harder to respect them if you know how they are made. Users are overwhelmingly self-taught. The migraine-inducing slog of plugging numbers and formulas to a tight deadline invites shortcuts. Unsurprisingly, errors are rife. Research suggests nine out of 10 spreadsheets contain a mistake.
Iceland mispriced its sale of a slug of Islandsbanki shares earlier this year, after an Excel spreadsheet omitted investors’ offers that had included “foreign commas or amounts defined as text”. The impact is disputed but might have cost as much as $16mn.
Such failures of data error cleaning and testing are all too common, according to the European Spreadsheet Risks Interest Group (Eusprig). This group of researchers has been sounding the alarm for 20 years. High on its list of spreadsheet horror stories was JPMorgan Chase’s $6.2bn “London Whale” trading losses in 2012. That featured an error-ridden model that required data to be manually copied and pasted from one spreadsheet.
A spectacular blunder in an influential Harvard University paper on public debt and growth cast doubt on a cornerstone of the austerity policies introduced after the financial crisis. The mistake was a simple one: the accidental omission of five rows of data when calculating an average.
Another nightmarish slip-up arose when Public Health England misplaced nearly 16,000 Covid-19 test results. That was because someone had used the wrong Excel file format, XLS, which only has about 64,000 rows, so some cases vanished off the bottom.
Sometimes, tools meant to help with accuracy add to the problem. Autocorrect errors are the bane of geneticists. Nearly a third of publications featuring gene names contain errors where the abbreviated form of a gene’s name is incorrectly recognised as a date. For example, SEPT4 (septin 4) is changed to 4-Sep.
People do not take spreadsheets seriously enough, says Eusprig’s programme chair, Simon Thorne. Unlike software engineers who typically spend 40 per cent of their time testing their coding, many spreadsheet users hardly bother.
Moreover, people are bad at picking up their own errors. Self-review catches, at most, about two-thirds of errors, according to research by Ray Panko of the University of Hawaii. Overconfidence is at the heart of the problem. People tend to be proudly aware of errors they fix. But they have no idea of how many remain.