It's just a spreadsheet, what can go wrong?
- Typing a "2" instead of a "1", led to 10,000 too many tickets being issued for a synchronised swimming event.
- Missing a minus sign forced Fidelity to cancel a $4.32/share year-end dividend.
- Hiding cells instead of deleting them, without realising they would reappear when the spreadsheet was converted to PDF, resulted in Barclays buying 179 more contracts than they had intended in the purchase of Lehman Brothers.
- A formatting error led MI5 to bug more than a thousand of the wrong phones.
- Ill-advised cut and paste led to JP Morgan losing more than $6bn in their London whale disaster.
- Cut and paste again, and this time a £24m loss for TransAlta.
And perhaps most notoriously...
- Carmen Reinhart and Kenneth Rogoff's report Growth in a Time of Debt, used as the justification for government austerity programmes, originally claimed that when a country's debt hit 90% of its GDP, the economy would shrink by 0.1%. Cue cuts to government budgets around the world. But five countries were accidentally missed out of the analysis when a sum range stopped two thirds of the way down a column. Correct the mistake and 0.1% shrinkage turns into 2.2% growth.
So, what can we do to avoid all these scary pitfalls? Presumably these august organisations were not entirely staffed by idiots so we could easily fall into the same traps. Clearly you must familiarise yourselves with the error checking tools in excel. The best way to do this is by practising. Just type "error tracking" into the help function in the toolbar and it will walk you through any errors it spots in your work. But here are four more general tips to reduce your chances of making mistakes:
Understand the business context
If you understand properly what your spreadsheet is attempting to model you are much less likely to make a mistake and much more likely to spot it if you do. Apply the common-sense test. Do the outputs of this spreadsheet make sense? Do they correspond to my view of how the business works in this area? If they don't, you either have a new insight or a mistake. Announcing the former, only to discover it is in fact the latter, is embarrassing and undermining, so best avoided!
Understand the source data
If you understand the source data, you are more likely to spot mistakes. Whatever your spreadsheet says, it is unlikely the current inflation rate in Germany is 147%. If you understand what is behind the source data, you will know instinctively if something is wrong.
Built in functions
Do not use built in functions unless you really understand how they work. They sometimes contain assumptions that might not be appropriate. On the other hand, if you are going to build your own functions, make sure they are properly tested.
Minimise user error
It sounds too obvious to say, but it is critical. Most common among problems is unintentional formatting – numbers formatted as text, for example. Excel can't use its common sense and convert text to numbers, even if that is what you meant. The problem can be caused by cutting and pasting, or by ranges for formats going beyond the intended area. Of course, you may not be the only user. Password protect your document before passing it on to users so that they can only read it or change the fields you want them to change.
So there you are. Four non-technical tips to help prevent spreadsheet errors and avoid you ending up on someone else's list of comical cock-ups!
You need to sign in or register before you can add a contribution.