O'Reilly logo

Excel 2003: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Solving Formula Errors

Errors...they occur 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 leave you with.) 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, it can be tough to track down where the original error occurred.

Excel provides some interesting formula auditing tools—a handful of features that inspect broken formulas—which can make it much easier to fix errors. With any error, your first step is to identify the error code by using the information listed on Table 7-2. If the problem isn't immediately obvious, 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 is 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 =A1+B1, both A1 and B1 are precedents. If either one of these cells contains an error, 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. For example, if ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required