9 ways to Avoid Spreadsheet Errors
BEWARE
The spreadsheets underpinning you reports are probably
chock-full of errors!
Spreadsheets are great, they are my favourite go-to way of dealing with data – BUT there are drawbacks; well actually ONE major drawback – spreadsheets are created by humans – and humans are prone to errors!
There are plenty of stories on the web outlining problems with spreadsheet errors;
When people undertake simple mechanical tasks, such as typing, they make undetected errors in about 0.5% of all actions.
When we do more complex logical activities, such as writing spreadsheets, the error rate rises to about 5%.
I believe the primary purpose of preparing any report is to drive good decision-making; if a report is incorrect then it is practically impossible to end up with the best decision, so it is vital to avoid spreadsheet errors!
There are two basic types of errors, both are the enemy of Accurate Spreadsheets:
* Quantitative Errors – create an immediate incorrect result
* Qualitative Errors – create confusion for the user
Avoid Spreadsheet Quantitative Errors
There are three ways to create an error in the outcome of a spreadsheet.
- Mechanical errors – simple mistakes, such as mistyping a number or pointing to the wrong cell
- Logic errors – entering the wrong formula because of a mistake in reasoning
- Omission error – something is left out
Avoid Spreadsheet Qualitative Errors
A spreadsheet with Quality Errors is difficult or even impossible to read, edit, update and maintain. Quality errors within spreadsheets include incorrect instructions, and usually these drive errors when undertaking basic tasks of change such maintenance and updating data.
Having read all this, and been reminded about this unbelievably high statistic – I have been collating some really useful ways to improve the accuracy and usability of all spreadsheets ….Using a single ‘error-checking-sheet’ consistently, for every document, is an awesome idea! For brevity I haven’t included any screen shots or too many examples – check the credits at the bottom of the post if you want more details.
Many spreadsheets are large and complex, and development often involves interactions among multiple people – so it is also poignant to remember to design your sheet for everyone to use, not just yourself. Often I arrive at clients and the accountant has created a set of tools that no-body else can use or understand how to run. Whilst being indispensable does create a sense of security, if it also brings with it a risk there will be no reporting or ways to understand the company operations when someone goes on holidays – then it is best avoided!
9 Ways to Avoid Spreadsheet Errors
(I am using “worksheets” to refer to additional/separate spreadsheets/tabs, each with a subsets of work, within a spreadsheet file – these are generally labelled “Sheet 1″, “Sheet 2″, etc in Excel)
1. Organize your workbook by Function
Often I see very large single spreadsheets with several reports and side calculations all cluttered together on a single sheet which creates confusion, and leads to errors.
2. Watch your Headings
Avoid using just numbers in header cell, also include alpha characters in header labels.
_________________________________________________________
DOWNLOAD YOUR FREE CHECKLIST
9 Ways to Improve Spreadsheet Accuracy
Spreadsheets are great, BUT there is ONE major drawback
Spreadsheets are very very prone to human errors!
_________________________________________________________
3. Use Range Names
If you assign understandable names to key ranges in your spreadsheet, and then use those names in your formulas, your formulas will be much easier to understand. And the errors in your formulas will be much more obvious.
4. Keep Formulas Simple
Keep your formulas simple instead of stringing lots of things together – you can make things as complex as you like, but breaking the calculation into easily recognisable steps helps you to identify errors. Work with several meaningful components, across a few more cells with labels so that the logic of your workings is obvious.
5. Perform “Reasonability” checks
Add an entire worksheet/tab for checking Errors; this is a great way to keep things in order and can be used in conjunction with, or independently from, “checksum” totals.
6. Reconcile your results with an ultimate truth
Generally the data in your spreadsheet comes from one or two sources – always monitor the spreadsheet to see if it diverges from these original “truths”.
7. Use Lots of Comments
Comments are a great way to attach information/instructions to a cell, given they remain unpublished limit comments to those that will assist users of the spreadsheet, not users of the final report.
8. Use line graphs to uncover unusual results
You can quickly and easily use a line graph to display outliers in a series of numbers. Chart formatting doesn’t matter. Chart placement doesn’t matter. What does matter is that outliers – unusually large or small numbers – stand out from the crowd. If there are no apparent issues simply delete the chart.
9. Save Versions
Save new versions as you progress, recording major changes on a History Worksheet/Tab with the date and details of the iterations.
Finally, once you think your sheet is finished; loop back and find an error – then look for some others…. Here’s to hoping that you found them all, but probably you didn’t.
Share your horror story about accuracy of your spreadsheets… (probably best to change the names to protect the guilty)
Credit where credit is due – this article is a mash-up of information available all over the internet, but here are a few good resources:
* http://a4accounting.com.au/ (Neale Blackwood, the man behind the a4accounting site, also has an excellent book – Advanced Excel Reporting by Wiley Press – that I use frequently)
* http://panko.shidler.hawaii.edu/SSR/Mypapers/whatknow.htm
* http://w3schools.com/
Not sure you agree?
Although accuracy is vital for good reporting, sometimes accuracy isn’t the be-all-and-end-all – find out when being less than accurate can be a benefit! - see Why introducing bias into information graphics is good
About Eve Blackall
I really appreciate that you are reading my post; I regularly write to help SME owners discover hidden profits, cash and value in your business.
If you would like to read my regular posts you are welcome to follow my blog on Facebook and Twitter, or subscribe to my six weekly blog roundup newsletter.
I am a ‘business fixer’. Using myProfitology, Cashflowology and Exitology frameworks to solve the two biggest financial challenges of any business: making more money, and keeping more money.
Having been a Group Financial Controller for several top 100 ASX listed firms, in addition to operating my own highly lucrative accounting practice, I am also now the author of ‘Profit-ology’ the ultimate guidebook, anyone can use, to increase your profits.
The only thing I would is protect as much as possible from editing as possible. This not only stops accidental over types but it also stops people tampering with things. In one office I found 7 different versions of what was supposed be identical copies of the same spreadsheets. People were tampering (with the best of intentions) the formulas because they thought they were wrong or they did simply did not understand the results