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:

1. Resolve all error values.
2. Calculate nested formulas one step at a time.
3. Trace the relationships between formulas and cells.
4. Use the Error Checking tool to check your worksheet for common formula problems.
5. 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.).

1. Click the cell that contains the error.
2. 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:

If the formula uses a function that is part of the Analysis ToolPak add-in but the add-in has not been loaded, install and load the Analysis ToolPak add-in.

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

Make sure the name exists.

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

If the name is not listed, define the name:

1. In the Names in workbook box, enter the name for the formula.
2. 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.

Verify the name spelling.

Verify the spelling. Select the name in the formula bar , press F3, click the name you want to use, and then click 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.

To allow labels, on the 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.

To insert the correct function name into the formula, click Function on the Insert menu.

Enclose any text in the formula in double quotation marks.

Excel tries to interpret your entry as a name even though you intended it to be used as text.

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

Enter a colon (:) between the range references; for example, SUM(A1:C10).

Check for sheet references not enclosed in single quotation marks.

If the formula refers to values or cells on other worksheets or workbooks and the name of the other workbook or worksheet contains a nonalphabetical character or a space, you must enclose its name within 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.

1. Select the cell you want to evaluate. Only one cell can be evaluated at a time.
2. On the Tools menu, point to the Formula Auditing menu, and then click Evaluate Formula.
3. 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.
4. Continue until each part of the formula has been evaluated.
5. 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)

1. Select the cell that contains the formula for which you want to find precedent cells.
2. 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:

     1. Click Options on the Tools menu, and then click the View tab.
     2. Make sure that Show all or Show placeholders is selected under Objects.
3. To identify the next level of cells that provide data to the active cell, click Trace Precedents   again.
4. 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)

1. Select the cell for which you want to identify the dependent cells.
2. 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:
     1. On the Tools menu, click Options, and then click the View tab.
     2. Make sure that Show all or Show placeholders is selected under Objects.
3. To identify the next level of cells that depend on the active cell, click Trace Dependents again.
4. 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.
5. 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.

1. Select the worksheet you want to check for errors.
2. If the worksheet is manually calculated, press F9 to recalculate now.
3. On the Tools menu, click Error Checking. If the Error Checking tool finds potential errors, the Error Checking dialog box opens.
4. If you have previously ignored errors, and want to recheck them, click Options, click Reset Ignored Errors, click OK, and then click Resume.

5. 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.

6. 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.
7. Click Next.
8. Continue until the error check is complete.

How can I change which common problems Excel checks for?

1. On the Tools menu, click Options, and then click the Error Checking tab.
2. 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:

1. On the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode .
2. On the File menu, click Print.
3. 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.

1. Select the cells you want to watch.
2. On the Tools menu, point to Formula Auditing, and then click Show Watch Window.
3. Click Add Watch .
4. Click Add.
5. 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.