Microsoft Office Tutorials and References
In Depth Information
Solving Formula Errors
Note: The SUBSTITUTE() function always performs a case-sensitive search. That means if you try using
SUBSTITUTE() to replace the word it, in the previous example, Excel won’t match It.
Solving Formula Errors
Errors… they happen in the most unexpected places, transforming rows of
calculations into unhelpful error codes like #NAME?, #VALUE! , and #MORON! (OK, that
last one doesn’t actually appear in Excel, but it might as well, given the sense of defeat
and frustration these error codes can give you.) In some cases, you can see how to
fix an error just by looking at the formula. However, sometimes the problem isn’t so
easy to solve, especially if your formulas perform calculations using the results of
other formulas. In such cases, you can have a tough time tracking down where the
original error happened.
Excel provides some interesting formula auditing tools —a handful of features that
you can use to inspect broken formulas or figure out what’s going on in really
complex ones. These tools make it much easier to fix errors.
With any error, your first step is to identify the error code by using the information
listed in Table 17-2 on page 476. If the problem isn’t immediately obvious, then you
can use the Formula Auditing tools to perform the following tasks:
• Evaluate an expression step-by-step, until you hit the error. That way, you know
exactly what part of the formula’s causing the error.
• Trace the precedents of a formula that’s causing an error. Precedents are the cells
that a particular formula references. In the formula = , both A1 and B1 B1
are precedents. If either one of these cells contains an error, then the error gets
fed into—and trips up—the formula.
• Trace the dependents of a cell. Dependents are other cells that use the current
cell. If one cell has the formula = , and another cell contains = A1*10 , both B1
these cells are dependents of cell A1. If A1 has an error, it infects both formulas.
• Perform an error check on the entire worksheet. Excel’s error check is like a spell
check. One by one, it takes you to each cell that has an unresolved problem.
To perform any of these tasks, you’ll need the Formulas tab’s Formula Auditing
section. The following sections explain how you use it to find errors, evaluate formulas
piece-by-piece, and trace relationships.
Complex formulas usually include multiple sub-expressions . Each sub-expression is a
piece of any formula that’s evaluated separately. It may be an arithmetic operation in
parentheses, a nested function, or even just a cell reference. In order to understand
what’s causing an error in your formula, you need to know which sub-expression
caused the problem.