Excel is a brilliant calculation package but when your calculation goes wrong where do you start to troubleshoot Excel calculations?  This article gives some key features that are available that will help.

Double clicking on the cell

By double clicking on the cell containing the calculation or clicking in the formula bar the cells used in the calculation are highlighted in different colours as are the cell references in the calculation itself.  So if one of the cells has been incorrectly selected it should be obvious.  This is however most useful if the cells are visible on the same screen as the calculation so this is not o effective in larger spreadsheets.

Trace precedents

This is similar to the previous option but is a better choice if the cells are outside the data visible on the screen.  Although there is no colour coding an arrow shows where the data has come from which can be followed on the screen if the data is further away.  If the information is coming from a different sheet then an arrow with a table symbol appears.  If you double-click on the arrow then the goto dialog box enables you to jump to the other sheet tab and relevant cell.

 Image of Trace precedents

Error checking

This contains 2 options both of which require there to be an error in one or more cells in the spreadsheet to work.  The errors it is looking for are those detailed in the error messages section below.

Error Checking

Jumps to a cell that contains an error.  There are then tools available to help with the error or even ignore it if that’s OK (maybe you are still waiting for data).Trace error

This is the equivalent of trace precedents mentioned earlier.

Evaluate formulas

With complicated formulas it helps if you can see how the answer is derived step by step.  That is exactly what this tool allows you to do.  By doing this it will often become apparent where the root cause of the problem exists.  Without this sometimes the wrong assumptions can be made regarding the order the calculation is performed.

Image of error checking

Error messages

There are a range of different error messages that might be seen if no answer can be given.  There are 7 different ones you may see and each has a specific meaning.

#VALUE! – the data needs to be a number and it is something else like text, spaces or other characters

#NAME? – the data contains a name that Excel doesn’t recognise.  This could be a function, range name, sheet tab name or cell reference that does not exist

#DIV/0! – the calculation is trying to divide by zero or an empty cell.  This is mathematically impossible!!

#REF! – the calculation is trying to reference a cell that doesn’t exist, for example when a row or column has been fully deleted (not just the contents of the cell deleted)

#NULL! – when you specify the intersection of 2 areas that don’t intersect

#N/A – the data you are referring to cannot be found.  Often seen with lookup functions

#NUM! – when the calculation is looking for a number but finds text.  This often occurs when numbers are added using the £ and , symbols e.g. £1,000.  Although this might look the same as a number that has been formatted it isn’t!

Image of #DIV/0!

When calculations don’t work there can be many reasons but hopefully some of these solutions will make it easier to troubleshoot Excel calculations  When dealing with complicated formulas another thing to consider is gradually building up the complexity of the calculation.  This makes it easier to pinpoint where the error has occurred.

 

Want to learn more about Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Excel Courses I run.

MEMBERSHIPS

FOLLOW LARA