Mistakes in spreadsheets aren’t just a personal annoyance. This can lead to serious consequences. And in the past few years alone, there have been countless horror stories about spreadsheets.
Northern Ireland Police, August 2023 I apologized There was a data breach of “extraordinary proportions” when a spreadsheet containing statistics on the number of officers and their ranks was shared online in response to a Freedom of Information request.
There was another overlooked tab in the spreadsheet that contained the personal information of 10,000 active duty police officers.
a series of spreadsheet errors Recruitment of trainee anesthetists in Wales was suspended in late 2021. The National Anesthetist Recruitment Office (ANRO), the body responsible for selecting and recruiting anesthetists, has notified all candidates for positions in Wales that some candidates are ‘not available for appointment’. . Highest score in the interview.
The responsibility was on the process of integrating the interview data. Spreadsheets in various fields lacked standardization in formatting, naming conventions, and overall structure. To make matters worse, data had to be manually copied and pasted between various spreadsheets, a time-consuming and error-prone process.
ANRO first discovered the blunder when a rejected applicant questioned his dismissal letter. The fact that there were no candidates considered acceptable for Wales’ position should have been a red flag. Clearly no testing or validation was applied to the critical spreadsheet, and simple steps could have prevented this serious error.
In 2021, Crypto.com, an online provider of cryptocurrencies, forwarded by mistake An Australian customer’s account was credited with $10.5m (£8.3m) instead of $100 because an incorrect number was entered into the spreadsheet.
The clerk who processed the Australian customer’s refund incorrectly entered her bank account number in the refund field of the spreadsheet. It took seven months before the mistake was discovered. The recipient attempted to flee to Malaysia, but was stopped at an Australian airport with a large amount of cash in his possession.
In 2022, Iceland’s state-owned bank Icelandbanki sold some of its shares, which had been significantly undervalued due to the financial crisis. spreadsheet error. When merging assets from different spreadsheets, spreadsheet data was not “cleaned” and formatted properly. The bank’s shares were subsequently undervalued by up to £16m.
Dark problems of corporate IT
The above are just some of the spreadsheet errors that are regularly encountered by various organizations.
Spreadsheets represent unknown risks such as errors, privacy violations, trade secrets, and compliance violations. But they are also important to how many organizations make decisions. For this reason, they explained It has been pointed out by experts as the “dark matter” of corporate IT.
industry the study We find that 90% of spreadsheets with more than 150 rows have at least one serious mistake.
This makes sense, since spreadsheet errors are common but difficult to spot.my own research Although we show that inspecting spreadsheet code is the most effective way to debug spreadsheet code, this approach still only catches 60-80% of all errors.
It is estimated that 9 out of 10 spreadsheets contain errors.
The appeal of spreadsheets doesn’t just exist in the financial world.they are essential engineering, data scienceand even send robot To Mars. The key to their success is flexibility.
Spreadsheet software is constantly evolving and more features are available to make it even more appealing. For example, you can now automate many tasks in Excel (the most popular spreadsheet software) using Python scripts.
But considering all the aforementioned issues, maybe it’s time to put aside Excel and other spreadsheet software and choose a more reliable software?
human error
The root cause of these spreadsheet problems is human error, not software.
The problem is that most users don’t see the need to plan or test their work.most users explain The first step in creating a new spreadsheet is to simply type in numbers and codes directly.
Many of us don’t think we need to seriously consider spreadsheets.This means that we will be Satisfied And suppose we don’t need to test, verify, or verify our work.
the study An analysis of the mental effort required by a task, or “cognitive load,” reveals that creating a complex spreadsheet requires as much concentration as a general practitioner’s diagnosis. This intense mental tension makes it easy to make mistakes. However, while general practitioners study their specialty for years before becoming qualified, most spreadsheet users are unable to do so. Self-taught.
There are several things organizations can do to break the cycle of recurring spreadsheet errors. First, introducing standardization helps minimize confusion and mistakes. For example, this means consistent formatting, naming conventions, and data structure throughout your spreadsheet.
Secondly, it is important to improve your training. Providing users with the knowledge and skills to create robust and accurate spreadsheets will help them identify and avoid pitfalls.
Finally, it’s important to foster a culture of critical thinking around spreadsheets. This means encouraging users to continually question calculations, validate data sources, and double-check their work.
Simon Thorne He is a senior lecturer in computing and information systems. Cardiff Metropolitan University
This article is republished from conversation Under Creative Commons License.read Original work.
