How to detect errors in your spreadsheets
Liam Bastick looks at some of the techniques you can follow to clean your data, an essential first step before turning your data into easy to understand information.
Given most of us use Excel to work with numbers, many analysts do not realise the importance of checking their work. For spreadsheets to be accurate, attention must be paid to detail.
End-users become uneasy when you review reports and see errors such as #DIV/0!, #N/A and so on. It is easy to find them, if possibly a little laborious when first starting out. Here are some tips: Use Excel’s Background Error Checking.
Strictly speaking, this should be instigated during the entire model development phase as it can assist the analyst throughout construction.
To enable this functionality, go to Excel’s options (File -> Options, or Alt + F + T) and in the ‘Formulas’ section, ensure that the ‘Enable background error checking’ tick box is checked. Once activated, the user can select which error checking rules should be catered for by inspecting the ‘Error checking rules’ section directly beneath this checkbox.
This functionality does not prevent errors from occurring, but potentially erroneous cells are highlighted by Excel in a fashion similar to cells that include comments:
The problem with this approach is it is easy to miss this annotation, but it is better than doing nothing.
Use Excel’s formula auditing tools
In the ‘Formulas’ tab of the ribbon, use the tools in the ‘Formula Auditing’ section of the toolbar. In particular, ‘Error Checking’ is useful (although it may only be applied to one worksheet at a time) as it highlights a lot of issues Excel is programmed to consider as “dubious” (for example, inconsistent formulas, #DIV/0! errors, and so on).
For those lucky enough to have the Professional Plus version of Excel 2019 or the Pro Plus version of Office 365, Spreadsheet Inquire adds to Excel’s in-built functionalities to allow users to analyse the links between workbooks, worksheets and / or individual cells:
This can help clean excess cell formatting and assist in understanding the relationships between cells and/or worksheets.
Find prima facie errors
There are glitches in Excel and occasionally, a prima facie error may slip through. These obvious errors are particularly embarrassing to miss, as these are usually identified by end-users in just a matter of seconds after a model has been handed over.
There is a simple sure-fire check: Ctrl + F (select ‘Find…’ from the drop-down list in ‘Find & Select’ in the ‘Editing’ grouping of the ‘Home’ tab of the ribbon).
Simply type ‘#’ in ‘Find what’ (the obvious errors all begin with ‘#’), but then click on the ‘Options’ button to display the options and change the ‘Within’ setting to ‘Workbook’ and then look at ‘Formulas’, ‘Values’ and ‘Comments’ in turn using the ‘Find All’ button to correct any issues identified:
Review inconsistencies in formulas
Sometimes, data is in the form of formulas. Imagine the following represents a block of formulae:
Let’s assume this data is supposed to refer to a similar block of data elsewhere. How can we tell if the formula has been copied across and down correctly? Inspection by eye achieves nothing here.
One option is to use the keyboard shortcut Ctrl + ` (the character is the key to the left of the 1 on a standard US QWERTY keyboard):
This shortcut toggles cell values with their content (that is, formulas). This will show formulas which have not been copied across properly, but this is still fraught with user error (can you spot the relevant cells?) and would be cumbersome with vast arrays of data.
Instead, there is a simpler, automatic approach. Select all of the data (click anywhere in the range and press Ctrl + A). Then use the keyboard shortcut Ctrl + \ which may reveal the following:
This automatically selects all of the cells whose contents are different from the comparison cell in each row (for each row, the comparison cell is in the same column as the active cell).
Ctrl + Shift + \ selects all cells whose contents are different from the comparison cell in each column (for each column, the comparison cell is in the same row as the active cell). In this example, where a formula is supposed to be copied across and down, there will be no difference.
These cells can now be highlighted and reviewed at leisure.
Create “quick” charts
For key outputs, you can graph the data momentarily. Simply highlight the data and press the F11 function key to create a chart on its own sheet or Alt + F1 to create a chart on the current worksheet.
Once the chart has been created, consider whether the chart(s) make sense: are there unseemly ‘blips’ or inconsistent trends? Can dramatic changes be readily explained? These rough and ready charts can highlight erroneous data in an instant.
Close and re-open
Do you get unexpected error messages upon opening? This is a frequent oversight made by modellers. Are calculations set to ‘Automatic’? Are there any unexpected links, circular arguments or other error messages (such as “Not enough memory to display”)? It is better that you discover these issues before your users do.
If you would prefer, there are two ways you can highlight errors in data in Excel too:
- Using conditional formatting
Select the entire data set, and go to Home –> Conditional Formatting –> New Rule. In the ‘New Formatting Rule’ dialog box, select ‘Format Only Cells that Contain’, then, in the ‘Rule Description’, select ‘Errors’ from the drop down:
Set the format as required and click ‘OK’. This will highlight any error values in the selected dataset.
- Using Go To Special
Select the entire data set and press F5 (or Ctrl + G, this opens the ‘Go To’ dialog box). Click on the ‘Special…’ button in the bottom left-hand corner:
Select ‘Formulas’ and uncheck all options except ‘Errors’:
This selects all the cells that contain errors. Now you can manually highlight these, delete them or edit them in some other way.
Nothing lowers the credibility of your work more than a spelling mis-steak. These are so simple to avoid in Excel: simply highlight all worksheet tabs and then select ‘Spelling’ from the ‘Proofing’ section of the ‘Review’ tab of the ribbon:
You can also use the keyboard shortcut F7 to perform the same task.
In the next article in this series, we will look at how to solve common spreadsheet data issues.
You might also be interested in
Recognised by Microsoft as one of 104 Most Valuable Professionals (MVPs) in Excel worldwide by Microsoft, Liam has over 30 years’ experience in financial model development/auditing, valuations, M&A, strategy, training and consultancy. He has headed Ernst & Young’s modelling team in Melbourne and was an Assistant Director in their...