| When you enter a formula or function correctly, the cell that contains the formula or function displays the correct result of the equation. If the result is incorrect or the cell displays one of the error messages described in Table 6.1, the formula or function has a problem. Table 6.1. Formula and Function Error Messages | Error Message | Indicates | | #### | Cell is too narrow to display the result. | | #VALUE! | The wrong type of function, argument, or operand is in use. | | #DIV/0! | The formula or function calls on Excel to divide a value by zero. In math, you are never allowed to divide a number by zero. | | # NAME ? | Excel does not recognize a cell reference or other text in the formula or function. Usually caused by a typo. | | #N/A | A particular value inside the formula or function is unavailable to it. | | #REF! | The formula or function contains an invalid cell reference. | | #NUM! | A numerical value inside the formula or function is invalid. | | #NULL! | The formula or function references an intersection of two ranges that do not intersect. Usually indicates that the formula has a space between two cell references instead of an operator or comma. | In many cases, you can spot the cause of the problem by taking a quick glance at the formula or function. You may have mistyped an operator or cell reference or selected the wrong cell. To spot errors in more complex formulas and functions, however, you might need help. Fortunately, Excel features three of its very own worksheet auditors that can help you track down the causes of most errors. Following is a list of the worksheet auditors with a brief description of each: -
Trace Precedents highlights the cells that supply values to the formulas, as shown in Figure 6.12. You can then verify the cell references in the formula and check to make sure the cells contain the correct values. Figure 6.12. Trace Precedents highlights the cells that supply values to the formula.  -
Trace Dependents highlights cells containing formulas that reference the currently selected cell. -
Trace Error highlights any likely causes of a particular error message that appears in a cell that contains a formula or function. Trace Error checks for syntax errors, wrong operands (mathematical symbols), and other possible causes. To run any one of these worksheet auditors, follow these steps: -
Click the cell that contains the function or formula that is not working properly (or the cell that is referenced by a problem formula or function). -
Open the Tools menu, point to Formula Auditing , and select the desired auditing tool: Trace Precedents , Trace Dependents , or Trace Error . The auditing tool displays arrows that point to the referenced cells or highlights errors. Figure 6.13 shows a worksheet analyzed by Trace Error. Figure 6.13. Trace Error can help you inspect a formula for common errors.  -
Examine the contents of the cells that supply values to the formula, and examine the formula for any faulty cell references, wrong mathematical operators, and typos. -
To remove the auditor 's highlighting and arrows, open the Tools menu, point to Formula Auditing , and click Remove All Arrows .  |
