The humble spreadsheet has become omnipresent in the work environment. From simple budgeting for households to complex financial models for multinational companies, spreadsheets have become the workhorse of decision-making.

However, despite their importance, spreadsheets are notoriously error-prone. No one really likes to check spreadsheets and verify formulas and things like that — and unfortunately, it shows. An innovative analysis showed that almost all business spreadsheets contain errors, oftentimes critical errors.

“The high rate of errors in these spreadsheets is concerning,” say the authors. “These mistakes can cause major issues.”

An effort spearheaded by Pak-Lok Poon, from Central Queensland University, Australia, took on the task of analyzing the world’s spreadsheets.

The team carried out a literature review — a study of studies. They looked at 35.5 years of articles detailing spreadsheet quality and errors across different fields. Ultimately, they found that 94% of spreadsheets contain errors.

Why this is happening and why it matters

Researchers say this has become prevalent as individuals without adequate software training create and maintain these tools.

Back in the day, spreadsheet creation and management were tasks reserved for IT professionals. Today, however, millions of non-technical users are responsible for developing and maintaining these tools. This democratization of spreadsheet use has immense benefits, such as increased flexibility and faster problem-solving. However, it also brings significant risks, as most end-users lack formal training in software development, testing, and debugging.

The consequences can be dire, particularly in critical areas like finance, healthcare, and energy. For example, errors in spreadsheets used for financial reporting can lead to making incorrect decisions, resulting in substantial financial losses. In the healthcare sector, a spreadsheet error could lead to incorrect patient treatments, with potentially life-threatening consequences.

The types of errors in spreadsheets varied wildly, from input errors (mistyping numbers, text, or symbols) and incorrect formulas to inconsistent data formats or logic flow errors (misordering calculations).

Prevention is better than cure

Fixing all the errors floating around in company spreadsheets is a mammoth task. However, one place to start would be trying to make better spreadsheets rather than trying to fix bugs.

Generally, we pay the most attention to the later stages of the spreadsheet life cycle, particularly testing and debugging, the researchers found. However, research suggests that a more holistic approach would be better. Errors often originate in the early stages of development, such as during the problem identification and design phases. By addressing quality issues at these stages, it is possible to prevent many errors from occurring in the first place.

“Spreadsheet quality is best addressed throughout the whole spreadsheet life cycle, rather than just focusing on a few specific stages of the life cycle,” Poon and colleagues conclude in the research. “Relatively more studies focus on spreadsheet testing and debugging (related to fault detection and removal) when compared with spreadsheet specification, modeling, and design (related to development). As prevention is better than cure, more research should be performed on the early stages of the spreadsheet life cycle.”

Another important direction for future research is the development of better testing and debugging techniques. While many effective methods exist, they are often underutilized in practice. Finding ways to make these techniques more accessible and easier to use could have a significant impact on spreadsheet quality.

Finally, as the complexity of spreadsheets continues to grow, the risk of errors appearing and going unnoticed also increases. Therefore, we need more research into how to manage and mitigate this complexity.

As businesses and individuals continue to rely on spreadsheets for critical tasks, the importance of quality assurance will only grow. By focusing on prevention, improving testing and debugging techniques, and developing new tools and resources for end-users, it is possible to reduce the prevalence of errors and ensure that spreadsheets remain a reliable tool for decision-making.

Journal Reference: Pak-Lok Poon et al, Spreadsheet quality assurance: a literature review, Frontiers of Computer Science (2024). DOI: 10.1007/s11704-023-2384-6