Catching Your Excel Errors

It’s been a bad week for Excel, with big news stories about spreadsheet errors. Two Harvard professors were in the news after a student at the University of Massachusetts found errors in their economic research paper.

Spreadsheet Errors

For example, in one of the formulas, five rows of data were omitted, so the average was incorrect. It’s easy for that kind of mistake to happen, especially if you add new data, or rearrange things, and forget to adjust your formulas.

You’ve probably read all about it, but if not, here are links to a couple of articles on this debacle:

Next New Deal Blog

You can see a bit of the spreadsheet, and the formula with the missing countries, in this article on the Next New Deal blog:

[no longer available] Researchers Finally Replicated Reinhart-Rogoff, and There Are Serious Problems

No wonder the results were wrong – they didn’t include Canada!

What Can You Do to Prevent Errors?

Most of us don’t publish our Excel reports, or have them examined by PhD students. So, your mistakes might not be quite as embarrassing, but they can be costly, in both time and money.

What can you do to prevent errors in your Excel files?

  • You’ll find good ideas on the European Spreadsheet Risks Interest Group (EuSpRIG) website: Best Practice
  • Patrick O’Beirne, current chairman of EuSpRIG, has published a guide to finding and preventing errors: Spreadsheet Check and Control
Spreadsheet Check and Control book
Spreadsheet Check and Control book

Spreadsheet Studio

[no longer available]

Chartered accountant, Joseph McDaid, has created an impressive free Excel add-in, Spreadsheet Studio.

This add-in’s tools let you review and audit your Excel files, and create comments with priority ranking and task allocation.

spreadsheetstudio

You can colour formulas to see if they are consistent, highlight the input and output cells, and use the Formula Explorer to go to any range reference in a formula.

Your Suggestions

Are there other resources that you use, and ideas for preventing and finding Excel errors? Please share them in the comments.

Thanks!

__________________

0 thoughts on “Catching Your Excel Errors”

  1. @Jan Karel:
    Exactly! Building consistent data structures is critical.
    Better organization helps too (seperate the data layer from the calcultion layer from the presentation layer)
    Adding some checksum calculations doesn’t hurt, either.
    Testing and peer review from critical items also helps.

  2. The economic paper in question was fraught with errors. The starting hypothesis was disputed by more economists than supported it. The statistical techniques were not what would have been widely practiced. There was confusion between correlation and causation. The Excel problem was only the icing on the cake.
    The most important thing the authors of the study could have done was to have somebody check their work. It could have been anybody: the UMass student who uncovered the methodology and calculation errors was an undergraduate student, who studied their paper as part of a class project. But the combination of bad method and bad Excel practice gave the authors too good a story, so they had no incentive to check their results.

  3. Thankyou so much for the very useful hints.
    One of my tasks for later this year is to set controls on my organisation’s 3 workbook, multi-sheet budgeting system.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.