Excel tip: use Evaluate Formula to track down errors such as #NAME?, #REF! and #VALUE!

If you're having trouble working out why a formula displays an error such as #NAME?, #REF!, #VALUE!, #N/A or #DIV/0! you can use Evaluate Formula to help identify step-by-step what happened.

Select Evaluate Formula from the Formula Auditing group on the Formulas tab.  Click read more for detailed more help on how to use Evaluate Formula.



Here we will step through an example of a formula that has returned an error.  This is a simple formula so it should be easy to see where the problem is, but in cases where there is a more complex formula the same principle applies.

Making sure we have the cell we want to evaluate selected, we click on Evaluate Formula.  A dialog box appears showing the formula for evaluation:

You can see the formula in G4 has a divide by zero error.  At this point, the formula evaluator has underlined the part of the formula that will be evaluated next.  You can perform the following actions, depending on context:

• Step In: this performs a step-by-step sub-evaluation of the underlined expression, which is useful for drilling down into the error once you have identified the part of the formula causing the problem.
• Evaluate: this performs an evaluation of the underlined expression without showing the working needed to reach the final answer.  This is useful for an initial quick analysis to show which part of the formula went wrong.  Once you've found which part went wrong you can re-do the analysis and Step In to see a step-by-step breakdown.
• Step Out: if you have used Step In you can step back out again to see how the values get passed back up the chain to the owner of the value.

Let's start by pressing Evaluate a few times.  We notice that once F1 is evaluated at the end of the formula we are left evaluating 6/0.  Once that is evaluated it changes to #DIV/0!, so that's a bit suspicious.  Our problem lies here, but we need to know why we ended converting F1 to 0.

We keep pressing Evaluate until we are given the option to Restart.  Then we press Evaluate until we see the F1 at the end of the formula highlighted.  Now we can drill down using Step In.

Pressing Step In will show how the value of the underlined expression gets calculated (for example, if the cell F1 contains a formula as well).  In this case when pressing Step In we'll see that F1 contains a reference to F6, so we'll press Step In again to see what is in F6.  It turns out F6 is blank.  This is all displayed in a nested view to make it easy to see what is going on:

We can now press Step Out twice to see how the value gets passed up the chain to the original formula.  So clearly we have a blank cell (F6) from which F1 gets its value of 0.  We now know exactly where the problem came from and can adjust the formula to take this into account.