Spreadsheet errors can have disastrous consequences, yet we keep making the same mistakes

Spreadsheet blunders aren’t just frustrating personal inconveniences. They can have serious consequences. And in the last few years alone, there have been a myriad of spreadsheet horror stories.

In August 2023, the Police Service of Northern Ireland apologized for a data leak of “monumental proportions” when a spreadsheet that contained statistics on the number of officers it had and their rank was shared online in response to a freedom of information request.

There was a second overlooked tab on the spreadsheet that contained the personal details of 10,000 serving police officers.

A series of spreadsheet errors disrupted the recruitment of trainee anesthetists in Wales in late 2021. The Anesthetic National Recruitment Office (ANRO), the body responsible for their selection and recruitment, told all the candidates for positions in Wales they were “unappointable,” despite some of them achieving the highest interview scores.

The blame fell on the process of consolidating interview data. Spreadsheets from different areas lacked standardization in formatting, naming conventions and overall structure. To make matters worse, data was manually copied and pasted between various spreadsheets, a time-consuming and error-prone process.

ANRO only discovered the blunder when rejected applicants questioned their dismissal letters. The fact that not a single candidate seemed acceptable for Welsh positions should have been a red flag. No testing or validation was apparently applied to the crucial spreadsheet, a simple step that could have prevented this critical error.

In 2021, Crypto.com, an online provider of cryptocurrency, accidentally transferred US$10.5 million (£8.3 million) instead of US$100 into the account of an Australian customer due to an incorrect number being entered on a spreadsheet.

The clerk who processed the refund for the Australian customer had wrongly entered her bank account number in the refund field in a spreadsheet. It was seven months before the mistake was spotted. The recipient attempted to flee to Malaysia but was stopped at an Australian airport carrying a large amount of cash.

In 2022, Íslandsbanki, a state-owned Icelandic bank, sold a portion of shares that were badly undervalued due to a spreadsheet error. When consolidating assets from different spreadsheets, the spreadsheet data was not “cleaned” and formatted properly. The bank’s shares were subsequently undervalued by as much as £16 million.

The dark matter of corporate IT

The above is just a fraction of the spreadsheet errors that are regularly made by various organizations.

Spreadsheets represent unknown risks in the form of errors, privacy violations, trade secrets and compliance violations. Yet they are also critical for the way many organizations make their decisions. For this reason, they have been described by experts as the “dark matter” of corporate IT.

Industry studies show that 90% of spreadsheets containing more than 150 rows have at least one major mistake.

This is understandable because spreadsheet errors are easy to make but difficult to spot. My own research has shown that inspecting the spreadsheet’s code is the most effective way of debugging them, but this approach still only catches between 60% and 80% of all errors.

Spreadsheets’ appeal doesn’t just exist in the financial world. They are indispensable in engineering, data science and even in sending robots to Mars. The key to their success is their flexibility.

Spreadsheet software is constantly evolving, with more features becoming available that increase their appeal. For instance, you can now automate many tasks in Excel (the most popular spreadsheet software) using Python scripting.

But given all of the aforementioned problems, isn’t it time for Excel and other spreadsheet software to be sidelined in favor of something more reliable?

Human error

The underlying cause of these spreadsheet problems is not the software but human error.

The issue is that most users don’t see the need to plan or test their work. Most users describe their first step in creating a new spreadsheet as merely jumping straight in and entering numbers or code directly.

Many of us don’t consider spreadsheets to warrant serious consideration. This means we become complacent and assume there is no need to test, validate or verify our work.

Research on “cognitive load,” the amount of mental effort required for a task, shows that building complex spreadsheets demands as much concentration as a GP making a diagnosis. This intense mental strain makes mistakes more likely. But GPs study their profession for many years before becoming qualified while most spreadsheet users are self-taught.

To break the cycle of repeated spreadsheet errors, there are several things organizations can do. First, introducing standardization would help to minimize confusion and mistakes. For example, this would mean consistent formatting, naming conventions and data structures across spreadsheets.

Second, improving training is crucial. Equipping users with the knowledge and skills to build robust and accurate spreadsheets could help them identify and avoid pitfalls.

Finally, fostering a culture of critical thinking towards spreadsheets is vital. This would mean encouraging users to continually question calculations, validate their data sources and double-check their work.

This article is republished from The Conversation under a Creative Commons license. Read the original article.