Spreadsheet Accuracy Matters

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 very very prone to human errors!

It is hard to be totally accurate when using spreadsheets!

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:

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:

  1. Simple typos – making mechanical errors such as mistyping a number or pointing to the wrong cell
  2. Mistakes in reasoning – creating an inappropriate formula because of a logic error (usually due to a misunderstanding of the requirements)
  3. 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.

Often workbooks 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 Spreadsheet Accuracy

1. Organise your workbook by function

Separate each major function or display to a new worksheet.

Create multiple worksheets each of which contains one logical section, then you can easily review the entire report or analysis.

Split your work into worksheets for Data Input, calculations and workings, reports and notes etc., assign each worksheet a relevant name.
i.e. rename it from “Sheet 1″

If there are groups of similar worksheets such as 2-4 for Data Input colour code the tabs to group them well. 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.

AVOID : One large single spreadsheet with several reports and side calculations all cluttered together which creates confusion, and leads to errors.

AVOID : hiding worksheets that really can cause trouble if someone else has to use the sheet.

QUICKTIP : to move rapidly from one tab to the next sequential tab Press Ctrl + Page Down

Eve Diamond Expert Support and guidance for NFPs

Eve Diamond

Not-For-Profit Business Remediation Guru

Access trusted support to
Imagine, Build, and Take Care Of Operations