O'Reilly logo

Excel 2010: 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 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 8-2 on page 242. 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 =A1+B1, both A1 and B1 are precedents. If either one of these cells contains an error, then the error gets fed into—and trips up—the formula.

  • Trace ...

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