applied tips : march 2007
microsoft excel : dealing with errors
Despite all of its considerable computing power, even Excel encounters formulas that it cannot calculate. These erroneous formulas can come from incorrectly written formulas, referencing data that doesn't exist, or simply breaking mathematical rules.
Error values 101
Whenever there is an error, Excel displays a green triangle in the corner of the cell and one of several cryptic-looking error values. Several common ones include #####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, and #NUM!. Be sure to check out the table and example later in this issue for detailed descriptions and examples of each of the error types.
Help is automatic
Of course, once you're faced with an error value, the important question is: What am I supposed to do now?
Often, before you can even answer that question, Excel comes to the rescue. If you modify a formula incorrectly, as soon as you press to leave the cell, Excel displays a dialog box telling you that it has found an error.
It also proposes a correction to the error. You can click Yes to accept the correction and move on, or you can click No and Excel will display another dialog box. Here you can select Help to review a Help topic about the error, or you can select OK and you'll be transported to the formula bar where you can edit the formula yourself.
Auditing formulas
Unfortunately, there are other types of error situations where Excel dialog boxes do not magically appear to help. At that point, the best solution is to use the Formula Auditing toolbar.
This toolbar helps you find cells that have a relationship to a formula, displays formulas affected by changes made to the cell, and—most importantly—tracks down the source of errors.
Excel 2002 and 2003: Select the error cell. The Trace Error list arrow appears, giving you several options. Click the list arrow and select Show Formula Auditing Toolbar from the list.
Excel 97 and 2000: Select the error cell. Select Tools > Auditing > Show Auditing Toolbar from the menu.
Once the Auditing toolbar is open, click the Trace Error button on the toolbar and you'll notice an arrow pointing from the cells that caused the error to the active cell containing the error value. You can use the arrow as a guide to retrace your formula calculations until you spot where your formula went wrong.
Error-checking formulas
If you are still having difficulty figuring out what you need to fix, select the cell with the error value and click the Error Checking button on the Formula Auditing toolbar.
Click the "Help on this error" button. The Excel Help task pane opens and displays a description of the error value and lists some possible causes and solutions.
Excel 97 and 2000: There is no Error Checking button on the Auditing toolbar. Instead, select Help > Microsoft Excel Help from the menu, type in the error name (i.e. "#NAME? error") and click Search. Select from the help topics to view possible causes and solutions to the error.
Removing arrows
When you're done, click the Remove All Arrows button on the Formula Auditing toolbar to remove the error tracing arrows.
Show Formulas Shortcut
A great shortcut to use when you are reviewing or editing formulas is the "Show Formulas" option which can be toggled on and off by pressing Control-Grave (`).
Common Errors
This table discusses most of the errors you will encounter:
| Error | Description |
|---|---|
| ##### | The numeric value of the cell is too wide to display in the space allocated. Resize the column by dragging between the column headings. |
| #VALUE! | You entered a mathematical formula that references a text entry instead of a numerical entry. You probably have text in a cell that should contain a number. |
| #DIV/0! | You tried to divide a number by zero, or a value that is currently zero or blank. If you cannot control the divisor, try using an IF statement to display nothing if the divisor is equal to zero, i.e.: =IF(A2=0,"",A1/A2) |
| #NAME? | You entered text in a formula that Excel doesn't recognize. You may have misspelled the name or function, or entered text in a formula without enclosing the text in quotation marks. This usually means you've mistyped a formula name, like SOM instead of SUM. |
| #N/A | A value is not available to a formula. You can also manually type #N/A into a cell when you do not yet know the answer. Any formulas based on this cell will also display #N/A rather than an error or unexpected result. |
| #REF! | A cell reference is not valid. You probably moved or deleted a cell range referenced by a formula. |
| #NUM! | You used an invalid argument in a function. |
schedule a class on excel
Request an on-site Excel class from Applied Office. Sessions are just $95/hr and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time. Learn more here
upcoming workshops
March 7 - Understanding Formulas: Learn exactly how formulas work from the basics (simple arithmetic) to the complex (IF, financial formulas, etc). Time and date calculations, text manipulation, and handy formula tricks will also be covered. Watch how a time card is created that automatically calculates overtime, wage, and vacation accrual. More Information
April 11 - Making it Look Good: Discover how easy it is to create attractive spreadsheets that communicate your ideas with ease. Through the use of spacing, borders, font, and color, you'll observe how a simple spreadsheet can be made to be more easily understood. Learn how to create charts and adjust their appearance. More Information
quick reference card
Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your own printer. You might even want to laminate it.