Accurate Spreadsheets Matter
There are two basic types of errors, both are the enemy of Accurate Spreadsheets:
* A. Quantitative Errors – create an immediate problem in your reports, some sort of incorrect result or information
* B. Qualitative Errors – create confusion for the user and ongoing problems, such as entering the right data in the wrong place
Both these problems decrease the reliability of the end result and cause untrustworthy reports – and we all know an untrustworthy report is as good as useless!
A. Quantitative Errors
There are three ways to create errors in the values reported on a spreadsheet:
- Simple typos – making mechanical errors such as mistyping a number or pointing to the wrong cell
- Mistakes in reasoning – creating an inappropriate formula because of a logic error (usually due to a misunderstanding of the requirements)
- Leaving something out – including data gaps and formula oversights
These mistakes boil-down to simple human errors, not necessarily something that can easily be recreated – and consequently these are very difficult to completely avoid. However being aware of how easy it is to make these mistakes is a good start, and really well designed spreadsheets go a long way to reducing the incidence of these errors.
B. Qualitative Errors
A spreadsheet with Quality Errors is difficult or even impossible to read, edit, update and maintain – which means it is difficult to get right! Quality errors within spreadsheets include incorrect instructions, and usually these drive errors when undertaking basic tasks of change such maintenance and updating data.
Many spreadsheets are large and complex, and development, and updating often involves lots of 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, when someone gets sick or goes on holidays – which is best avoided!
Again, designing your spreadsheets in a manner that is user friendly for a variety of excel skill levels goes a long way to reducing these errors too.
9 Ways to Create Accurate Spreadsheets
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. 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. Use Range Names
Suppose a value in a report is labeled Total Sales, Western Region. And suppose the formula for this result is: =SUM($M$5:$M$34) How can you tell if that formula is pointing to the correct data? It’s impossible to know, of course, without going to the cell address and examining its data.
Instead, suppose the formula is: =SUM(SalesService). 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.
Creating a single worksheet for inputs will enable you to update your workbook without having to change all the formulas – don’t forget to define your range names as larger than required so that the number of inputs can vary without the need to redefine the name range or adjust any formulas in the worksheet – colouring the range you name can help make this even more clear.
2. 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.
e.g. eg Total Sales: =(Price*Quantity)-(Price*Quantity)*Discount+(Price*Quantity)*Tax. Remember your algebra teacher nagging about “showing all your workings” and include the separate steps so your logic is transparent (and later you can come back and see what you did)
3. 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.
Therefore, as a general rule, separate each major function or display to a new worksheet in the workbook. Split your work into worksheets for Data Input, calculations and workings, reports and notes etc, assign each worksheet a relevant name (ie rename it from “Sheet 1″) and If I have a lot of similar worksheets such as 3-4 for data input I tend to colour code the tabs to group them well. If each worksheet contains one logical section, you can easily review the entire report or analysis. Wherever possible avoid hiding worksheets – that really can cause trouble if someone else has to use the sheet.
Additionally create a notes/instructions tab, and include a description of why each sheet exists and working instructions on how to use the workbook to produce the required reports.
4. Watch your Headings
Avoid using just numbers in header cell, also include alpha characters in header labels. You might think they’re harmless, but they can generate errors that are easy to miss. For instance, in a simple worksheet with the 2012, 2013, 2014, 2015 used as headers, Autosum will happily include your Header as one of the numbers to be added when sum-totaling the information below the headers – That’s a mistake you might not catch. The solution is simple: Always include alpha characters in header labels. In this case, you might use the headers FY 2013, or Financial Year 2014 or even just Year 2015.
5. 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.
If you find nearly every cell has a comment reconsider which of those are better moved to the notes and instructions sheets because they apply generally to the process or workbook and which comments relate to specific cells.
6. 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.
- Click the Chart Wizard button in your toolbar, or choose Insert Chart
- Click the Line chart type
- Click Finish, accepting all defaults
7. Save Versions
Save new versions as you progress, recording major changes on a History Worksheet/Tab with the date and details of the iterations.
8. Check Your Work (this is two steps)
This sounds so obvious, but it is amazing how often I see a sheet that hasn’t been looked at by the person who prepared it.
I don’t mean boringly going over your work line by line… these two approaches will help overall:
- Do a “Reasonability” Check – Add an entire worksheet/tab for checking Error – eg do all tables add up to the same thing across as down? This is a great way to keep things in order and can be used in conjunction with, or independently from, “checksum” totals.
- Check 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”.
e.g. if sales have been decreasing then most likely revenue should also be trending downwards, if it has taken a sharp hike upwards then go to the sales department and uncover any back-story (yes you may have to actually speak to someone outside
of accounts). That way you can make sure your reports are an accurate reflection of what is happening in the field.
Finally, once you think your sheet is finished; loop back and find at least one error – then look for some others…. Here’s to hoping that you found them all, but probably you didn’t.
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, also has an excellent book – Advanced Excel Reporting by Wiley Press – that I use frequently)
What tips do you use to improve the accuracy of your spreadsheets?
(share in the comments at the bottom of the page so everyone benefits)