# Find hidden discrepancies in Excel

If you've worked with complex financial worksheets, especially in collaboration with others, you know that the most impressive-looking worksheets sometimes hide the most embarrassing errors. Fortunately, Excel has a lot of tools and tricks to help ensure the accuracy of your data and calculations.

In this example, errors in several formulas give unintended results.

The formula for this cell has the wrong cell range. The **Trace Precedents** tool would clearly point this out.

This value looks good, but if you view all the formulas on this page, you'll notice an entered value that overrode the formula.

This cell has the wrong formula. Excel can find errors like this one with the **Error Checker** tool.

Excel displays this reference when a formula has an invalid cell reference.

## What can go wrong in a worksheet?

What can go wrong, and how can you find it before you waste time or, worse yet, send the worksheet out with errors?

Excel can help you find common worksheet problems such as these.

Problem |
Example |
How to find it |
---|---|---|

Unrecognized text in formula | Misspelled function name | Error value #NAME? |

Formula nesting error | Misplaced parenthesis in formula | Evaluate Formula tool |

Calculation doesn't include all data | Formula does not include rows added later | Trace relationships between formulas and cells |

Formula has one of several common problems | Inconsistent formula in region | Formula error checker |

## Locating the problem

To find discrepancies in a worksheet, you can step through a five-part process:

- Resolve all error values.
- Calculate nested formulas one step at a time.
- Trace the relationships between formulas and cells.
- Use the Error Checking tool to check your worksheet for common formula problems.
- View or print all formulas.

## Resolve all error values

Start with the obvious and resolve any error values first. When you enter a formula for which Excel can't evaluate a result, Excel displays an error value in the cell.

##### — Column is not wide enough, or a negative date or time is used

If the column is not wide enough to display the content, you can either widen the column or make the data fit.

**Widen the column**Drag the boundary on the right side of the column heading until the column is the width you want.

**Widen the column to fit the contents**Double-click the boundary on the right side of the column heading.**Shrink the contents to fit the column**Select the column, and on the**Format**menu, click**Cells**. On the**Alignment**tab, under**Text control**, select the**Shrink to fit**check box, and then click**OK**.**Change the formatting**The contents may fit with a different formatting. For example, use a formatting without the currency symbol or with fewer decimal places. Select the column, and then on the**Format**menu, click a**Category**. Select a formatting style, change the number of decimal places if you want, and then click**OK**.

If dates or times are negative, check your data and formulas.

- Dates and times must be positive values.
- If you use dates and times in formulas, make sure your formulas are correct.
- If the negative values are correct, change the cell formatting to a format that is not a date or time format. Select the cells, click
**Cells**on the**Format**menu, click the**Number**tab, and then select a format that is not a date or time format.

#VALUE! — The wrong argument or operand is used

Use the Trace Error tool to check your arguments, such as cell references, numbers, and operands (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.).

- Click the cell that contains the error.
- On the
**Formula Auditing**toolbar, click**Trace Error**.

** Note ** If you don't see the **Formula Auditing** toolbar, point to **Toolbars** on the **View** menu, and then click **Formula Auditing**.

#DIV/0! — A number is divided by zero (0)

- Check for explicit division by zero (0) in your formula — for example, =5/0.
- If your formula has a cell reference to a blank cell or to a cell that contains zero as a divisor, do one of the following:
- Change the cell reference to another cell.
- Enter a value other than zero in the cell used as a divisor.
- Enter the value
**#N/A**into the cell referenced as the divisor. The result of the formula will be #N/A instead of #DIV/0! to denote that the divisor value is not available. - To prevent the error value from appearing, you can use the IF worksheet function to calculate the value only if the divisor is zero (0). For example, if the formula that creates the error is =A5/B5, use =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text string.
- If you are running a macro, make sure the divisor in the macro function or formula is not zero or blank.

#NAME? — Formula contains text that Microsoft Excel doesn't recognize

Check common possible causes:

- On the
**Tools**menu, click**Add-Ins**. - In the
**Add-Ins available**list, select the**Analysis ToolPak**box, and then click**OK**. - If necessary, follow the instructions in the setup program.

On the **Insert** menu, point to **Name**, and then click **Define**.

If the name is not listed, define the name:

- In the
**Names in workbook**box, enter the name for the formula. - In the
**Refers to**box, type**=**(equal sign), followed by the formula or the constant (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.) value.

**OK**. If the formula bar is not visible, click

**Formula Bar**on the

**View**menu.

If the formula uses a label in a formula, make sure labels are allowed.

**Tools**menu, click

**Options**, and then click the

**Calculation**tab. Under

**Workbook options**, select the

**Accept labels in formulas**check box.

Make sure the formula is spelled correctly.

**Function**on the

**Insert**menu.

Enclose any text in the formula in double quotation marks.

Make sure all range references in the formula use a colon (:).

Check for sheet references not enclosed in single quotation marks.

#REF! — The formula contains an invalid cell reference

- If you deleted or pasted over cells referenced by the formula, change the formulas, or restore the cells on the worksheet by clicking
**Undo**immediately after you delete or paste the cells. - Make sure all programs that the formula links to are running.
- If you are linking to a Dynamic Data Exchange (DDE) topic, make sure that you're using the correct DDE topic.
- Check any macro functions to see if an argument refers to an invalid cell or range of cells.

## Calculate nested formulas one step at a time

Double-check complex nested formulas by evaluating the formula parts in the order in which they are calculated.

- Select the cell you want to evaluate. Only one cell can be evaluated at a time.
- On the
**Tools**menu, point to the**Formula Auditing**menu, and then click**Evaluate Formula**. - Click
**Evaluate**to examine the value of the underlined reference. The result of the evaluation is shown in italics. If the underlined part of the formula is a reference to another formula, click**Step In**to display the other formula in the**Evaluation**box. Click**Step Out**to go back to the previous cell and formula. - Continue until each part of the formula has been evaluated.
- To see the evaluation again, click
**Restart**. To end the evaluation, click**Close**.

** Note ** The **Step In** button is not available for a reference the second time the reference appears in the formula, or if the formula refers to a cell in a separate workbook.

## Trace the relationships between formulas and cells

You can trace both precedent cells and dependent cells of formulas and see at a glance where the data is coming from and going to. For example, you can see at a glance that Beverage Sales are not included in the formula for Total Net Sales.

Trace cells that provide data to a formula (precedents)

- Select the cell that contains the formula for which you want to find precedent cells.
- Click
**Trace Precedents**on the**Formula Auditing**toolbar.

** Notes **

- If you don't see the
**Auditing Toolbar**, point to**Formula Auditing**on the**Tools**menu, and then click**Show Auditing Toolbar**. - If
**Trace Precedents**is unavailable on the**Formula Auditing**toolbar, do the following: - Click
**Options**on the**Tools**menu, and then click the**View**tab. - Make sure that
**Show all**or**Show placeholders**is selected under**Objects**.

- To identify the next level of cells that provide data to the active cell, click
**Trace Precedents**again. - To remove tracer arrows one level at a time, starting with the precedent cell farthest away from the active cell, click
**Remove Precedent Arrows**. To remove another level of tracer arrows, click the button again.

Trace formulas that reference a particular cell (dependents)

- Select the cell for which you want to identify the dependent cells.
- Click
**Trace Dependents**on the**Formula Auditing**toolbar.

** Notes **

- If you don't see the
**Auditing Toolbar**, point to**Formula Auditing**on the**Tools**menu, and then click**Show Auditing Toolbar**. - If
**Trace Dependents**is unavailable on the**Formula Auditing**toolbar, do the following: - On the
**Tools**menu, click**Options**, and then click the**View**tab. - Make sure that
**Show all**or**Show placeholders**is selected under**Objects**.

- To identify the next level of cells that depend on the active cell, click
**Trace Dependents**again. - To remove tracer arrows one level at a time, starting with the dependent cell farthest away from the active cell, click
**Remove Dependent Arrows**. To remove another level of tracer arrows, click the button again. - To remove all tracer arrows on the worksheet, click
**Remove All Arrows**on the**Formula Auditing**toolbar.

## Use the Error Checking tool to check your worksheet for common formula problems

What spelling and grammar checkers do for word processing, the Error Checker tool in Excel does for worksheets. The Error Checker tool uses certain rules to check for problems in formulas. These rules do not guarantee that your worksheet is problem-free, but they can go a long way to finding common mistakes. In this example, the Error Checker tool finds an inconsistent formula.

- Select the worksheet you want to check for errors.
- If the worksheet is manually calculated, press F9 to recalculate now.
- On the
**Tools**menu, click**Error Checking**. If the Error Checking tool finds potential errors, the**Error Checking**dialog box opens. - If you have previously ignored errors, and want to recheck them, click
**Options**, click**Reset Ignored Errors**, click**OK**, and then click**Resume**. - The cell referred to in the dialog box is highlighted, and the formula that may have a problem is in the formula bar . The text in the dialog box describes the problem found by the checker.
- Click a button on the right side of the dialog box. The options are different for each type of problem. If you click
**Ignore Error**, the problem is marked to be ignored for each consecutive check. - Click
**Next**. - Continue until the error check is complete.

How can I change which common problems Excel checks for?

- On the
**Tools**menu, click**Options**, and then click the**Error Checking**tab. - Select or clear the check box for the options you want.

## View or print all formulas

You can see all the formulas in a worksheet, and print them for later reference. Viewing the formulas can help you find incorrect formulas. In this example, you can immediately see that a formula has been inadvertently overwritten by a value.

To display all formulas:

- On the
**Tools**menu, point to**Formula Auditing**, and then click**Formula Auditing Mode**. - On the
**File**menu, click**Print**. - Under
**Print what**, select an option to print the selection, the active sheets, or the entire workbook.

## The Watch Window toolbar

The **Watch Window** toolbar is another tool that helps you get to the bottom of errors and unintended results.

With the **Watch Window** toolbar, you can keep an eye on a cell and its formula while you work, even when the cells are out of view. Watching a cell can help you notice unintended results or errors sooner.

- Select the cells you want to watch.
- On the
**Tools**menu, point to**Formula Auditing**, and then click**Show Watch Window**. - Click
**Add Watch**. - Click
**Add**. - Move the
**Watch Window**toolbar to the top, bottom, left, or right side of the window.

To change the width of a column, drag the boundary on the right side of the column heading.

To display the cell that an entry in **Watch Window** toolbar refers to, double-click the entry.

### Watch all the cells on the worksheet that have formulas

To select all cells on a worksheet with formulas, click **Go To** on the **Edit** menu, click **Special**, and then click **Formulas**.

### See cells that have links to other workbooks

To see cells that have links to another workbook, open the other workbook.