Many formula errors are simple mistakes. For example, you might misplace or omit an opening or closing parenthesis, define a cell range without using a colon, or try to divide by zero. Excel quickly spots these types of errors and displays a message in a cell that contains an errant formula and offers to fix it. The following are error values that might appear in cells:
– #DIV/0! A divide-by-zero error, which occurs when you create a formula with a divisor that refers to a blank cell.
– #NAME? An invalid or nonexistent name. Excel also displays this error #value when you fail to wrap a text string in quotation marks when you use it in a formula.
– VALUE A reference to a text value in a mathematical formula.
– #REF! A formula reference to a deleted cell or range.
– #N/A A function or formula can’t find a value it needs (number not available).
– #NUM! Invalid numeric value in a formula or a function.
– #NULL! A formula includes a space between two ranges to indicate an intersection, but the ranges have no cells in common.
Specific types of formula errors are checked by default. When Excel detects a formula that is inconsistent with adjacent formulas, a small triangle appears in the cell’s upper-left corner. You might also see this indicator when Excel detects that a formula is incomplete, contains a faulty reference, uses unacceptable syntax, or is otherwise incorrect. When you point to the arrow, Excel displays a menu that offers options, including ignoring the error.
Tip If you don’t want Excel to check for certain types of errors as you work, you can change the options on the Formulas page of the Excel Options dialog box to turn off error checking altogether or to turn off specific error-checking rules.
To check for stealthier errors, use the Formula Auditing group on the Formulas tab. The Error Checking command lets you take one of several steps in response. If the selected formula has more than one calculation step, which is to say, includes more than one operator, the Show Calculation Steps button appears. Clicking it opens the Evaluate Formula dialog box, which is equivalent to clicking Evaluate Formula on the Formulas tab. Use this dialog box to follow each step in the calculations executed by the formula in the selected cell. Each time you click the Evaluate button, Excel executes the next calculation, until the final result is displayed in the dialog box.
➤ To check for errors manually
➜ On the Formulas tab, click Error Checking.
➤ To trace an error
1. Select the cell that displays an error value.
2. On the Formulas tab, in the Formula Auditing group, click the Error Checking arrow, and then click Trace Error.
➤ To turn on or off error checking options
1. In the Backstage view, click Options.
2. In the Excel Options dialog box, display the Formulas page.
3. Under Error Checking Rules, clear or select the check boxes for the rules you want to use, and then click OK.