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.
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:
- BBC: The Mysterious Powers of Microsoft Excel
- IEEE Spectrum: Excel Spreadsheet Error Heard Around the World
- Toronto Star: Student finds glaring spreadsheet errors
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
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.
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
Are there other resources that you use, and ideas for preventing and finding Excel errors? Please share them in the comments.