88% of spreadsheets have errors

Microsoft Excel makes it easy for anyone to do the kind of number crunching once reserved for accountants and statisticians. But the world’s best-selling spreadsheet software has also contributed to the proliferation of bad math.

Close to 90% of spreadsheet documents contain errors, a 2008 analysis of multiple studies suggests. “Spreadsheets, even after careful development, contain errors in 1% or more of all formula cells,” writes Ray Panko, a professor of IT management at the University of Hawaii and an authority on bad spreadsheet practices. “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors.”

 

Given that Microsoft MSFT -0.47%  says there are close to 1 billion Office users worldwide, “errors in spreadsheets are pandemic,” Panko says.

Such mistakes not only can lead to miscalculations in family budgets and distorted balance sheets at small businesses, but also might result in questionable rationales for global fiscal policy, as indicated by the case of a math error in a Harvard economics study. By failing to include certain spreadsheet cells in its calculations, the study by Harvard economists Carmen Reinhart and Kenneth Rogoff may have overstated the impact that debt burdens have on a nation’s economic growth.

The problem is so widespread that there are now whole groups devoted to stamping out spreadsheet snafus, such as the European Spreadsheet Risks Interest Group. There’s no question that spreadsheets are a powerful tool, essential to the functioning of the modern world, the group’s chairman, Patrick O’Beirne, tells MarketWatch. “Chainsaws are also a very good tool, but who would use one without a chain guard,” he says. “People don’t take safeguards to ensure their work is correct — in fact, in many cases, all it would take to catch these errors is a second set of eyes.”

While few would publish a book or article without having it edited first, spreadsheets often get put into use in first-draft form, O’Beirne says. “Peer review is the gold standard in academic research, and the best self-protection for business users of spreadsheets,” he says.

There has been growing awareness of the problem in recent years, and there are now several certification programs designed to ensure best practices are followed by spreadsheet creators.

But even those who don’t have someone to check their spreadsheets can take extra steps to minimize mistakes, O’Beirne says. “Technically, the Harvard error could have been spotted by simple tests, such as pressing Ctrl + [ on a formula, to show what cells feed into the total” he says. “There are also many software tools, such as XLTest, to point out structural flaws.”

Simple arithmetic errors may be rampant, but spreadsheet use is likely to go on as before, Panko warns in his paper. “Despite this long-standing evidence, most corporations have paid little attention to the prospect of serious spreadsheet errors,” he says.