Director SumProduct
Share this content

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.

13th Feb 2020
Director SumProduct
Share this content
Number highlighted

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.

Excel options

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:
Highlighted errors

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).

Formula auditing

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:

Spreadsheet Inquire

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:

Find and replace

Review inconsistencies in formulas

 Sometimes, data is in the form of formulas. Imagine the following represents a block of formulae:

Excel table

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):

Excel table
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:

Cell selection

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.

Graph

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.

Highlight errors

If you would prefer, there are two ways you can highlight errors in data in Excel too:

  1. 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:

New formatting rule

Set the format as required and click ‘OK’. This will highlight any error values in the selected dataset.

  1. 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:

Go To Special

Select ‘Formulas’ and uncheck all options except ‘Errors’:

Go To Special

This selects all the cells that contain errors. Now you can manually highlight these, delete them or edit them in some other way.

Spell check

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:

Spelling Excel

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.

Replies (5)

Please login or register to join the discussion.

avatar
By tedbuck
13th Feb 2020 12:39

Might be interesting but the detail in the panels is absent - often the case with pictures too. Also find it difficult to load accweb as usually it takes forever and I get a 'cannot find the website' message.
Also find replies often disappear into cyberspace so have to keep a copy to try again. I'm probably not the only one as I see double postings from time to time.
Any cyber specialists who can tell me what I or Accweb are doing wrong?

Thanks (0)
Replying to tedbuck:
avatar
By Alf
14th Feb 2020 09:11

I had missing images for ages as well, although it seems to have fixed itself as of today!!
My previous workaround was to use Microsoft Edge, for AW Excel articles, instead of my regular browser (Firefox)

Thanks (0)
avatar
By Alf
14th Feb 2020 09:18

Not sure if it is me, AW or Firefox but I save some articles as pdfs but I cannot get this article to print/save

Thanks (0)
avatar
By roryg
20th Feb 2020 10:43

Advert next to article - "Top 5 reasons why not to use excel" !

Thanks (1)
avatar
By Farkhem Hall
28th May 2020 10:15

Or just run this code on your spreadsheet in question and any sheet with any error will be highlighted:

https://www.accountingweb.co.uk/tech/excel/checksum-tool-a-simple-routin...

Thanks (0)