Catching Your Excel Errors

spreadsheetcheck2It’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.

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 my favourite articles on this debacle:

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

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 Studio

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.

To watch the two minute demo video, and download the free add-in, click here: Spreadsheet Studio

Your Suggestions

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

Thanks!

__________________

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

5 comments to Catching Your Excel Errors

  • sam

    What can you do to prevent errors in your Excel files?.... absolutely nothing... to err is human

  • Formatting a range as table is a good way to prevent havoc when data expands or contracts.

  • alexJ

    @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.

  • 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.

  • Mark Bishop

    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

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>