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

Formula Errors

If you make a syntax mistake when entering a formula (like leaving out a function argument or including a mismatched number of parentheses), Excel lets you know right away. Moreover, like a stubborn schoolteacher, Excel won't accept the formula until you've corrected it. It's also possible, though, to write a perfectly legitimate formula that doesn't return a valid answer. Here's an example:

=A1/A2

If both A1 and A2 have numbers, this formula works without a hitch. However, if you leave A2 blank, or if you enter text instead of numbers, then Excel can't evaluate the formula, and it reminds you with an error message.

Excel lets you know about formula errors by using an error code that begins with the number sign (#) and ends with an exclamation point (!), as shown in Figure 8-7. In order to remove this error, you need to track down the problem and resolve it, which may mean correcting the formula or changing the cells it references.

When you click the exclamation mark icon next to an error, you see a menu of choices (as shown in Figure 8-7):

  • Help On This Error pops open Excel's online help, with a (sometimes cryptic) description of the problem and what could have caused it.

  • Show Calculation Steps pops open the Evaluate Formula dialog box, where you can work your way through a complex formula one step at a time. Step-by-Step Evaluation describes how this advanced feature works.

  • Ignore Error tells Excel to stop bothering you about this problem, in any worksheet you create. ...

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