Fixing Formula Errors

Working with formulas is the building block of creating powerful and dynamic Excel spreadsheets. However when there is a problem, it is not always that easy to diagnose what the problem with your formulas is.

Fortunately there a few tools provided with Excel to assist you in fixing formula errors on your spreadsheet.

 

Formula Error Messages

 

When an error occurs you will be shown an error message.

 

Formula error messages begin with a number sign (#) and are then followed by some text that gives an indication as to the type of problem you encountered.

 

Understanding these error messages can help you solve your formula problems.

 

Some common formula error messages include;

• #NAME? – Excel does not recognise the text you entered as a reference to a range, table or function.
• #REF! – Excel cannot find the cell that you referenced within the formula.
• #VALUE! – Normally a formatting issue. For example, a cell your formula is referencing may contain text instead of a number.
• #DIV/0! – Occurs when you try and divide by 0 or an empty cell.
• #NUM! – Normally occurs when you try and pass invalid data to an argument like text rather than a number.
• ######## - Either the number is too large for the cell that is displaying it, or it contains a date before 01/01/1900.

Tracing Precedents and Dependents

When trying to trace the cause of a formula error, the Trace Precedents and Trace Dependents buttons will illustrate the cells that the formula is dependent on (precedents), and those affected by the formula result (dependents).
 

These buttons can be found on the Formulas tab of the Ribbon.
 

The image below shows the Trace Precedents button identifying the cells that the formula in B17 is dependent upon. It highlights that the issue is caused by the contents of cell B5.

  

Displaying Formulas

 

By default Excel displays the value (or formula result) in a cell and the formula in the Formula Bar. This means that you need to select a cell to view the underlying formula.

 

If you are trying to diagnose a large formula that references other cells (which may also contain formulas), this can get irritating.

Fortunately you can toggle the display of formulas on and off. Making them visible in the cell can make them easier to troubleshoot.
 

Click the Formulas tab on the Ribbon, and then Show Formulas in the Formula Auditing group.

 

Show Calculation Steps and Error Checking

When a formula is quite large you may have trouble locating where the error is occurring.

 

Good news is that Excel will show you the calculation steps of the formula. This means that it will run the formula and stop at the point just prior to the error occurring. It even underlines the problem part.

Click the smart tag icon next to the cell containing the error, and select Show Calculation Steps from the menu.

 

Excel steps through the formula, displaying the values used and stops when it cannot go any further without producing the error.

This example highlights that the issue was that a cell contained text. A basic example but it demonstrated using these features to find the fault.
 

Let’s look at a stronger example. There is an error in the formula below.

 

Click the Error Checking button on the Formulas tab. This provides an explanation of the suspected problem. It mentions that a cell may have moved, or that a function is returning a reference error.

 

Click the Show Calculation Steps button to step into the formula. The first Vlookup function is underlined highlighting the problem function.

 

You now have the source of the problem from a formula that used 4 different functions. Click the Evaluate button to see the error occur.

 

The actual problem with this formula is that we have asked to return data from column 11, but the table array for the Vlookup is only 10 columns wide.
 

Boost your Excel formula skills to superhero status, sign up for our Excel Superhero course.

More Tips for Troubleshooting Formula Errors