Find and correct errors in formulas

Formula errors can result in error values as well as cause unintended results. Below are some tools to help find and investigate errors.

ShowCorrect an error value, such as #NAME?

If a formula cannot properly evaluate a result, Microsoft Excel will display an error value. Each error type has different causes, and different solutions.

Show#####

Occurs when a column is not wide enough, or a negative date or time is used.

Review the possible causes and solutions.

Possible causes and solutions

ShowColumn is not wide enough to display the content

Increase the width of the column Select the column, point to Column on the Format menu, click Width, and then enter a number.

Shrink the contents to fit the column Select the column, then on the Format menu, click Cells, click the Alignment tab, and then select the Shrink to fit check box.

Apply a different number format In some cases, you can change the cell's number format to make the number fit within the existing cell width. For example, decrease the number of decimal places after the decimal point.

ShowDates and times are negative numbers

  • If you are using the 1900 date system, dates and times in Microsoft Excel must be positive values.
  • When you subtract dates and times, make sure you build the formula correctly.
  • If the formula is correct, although the result is a negative value, you can display the value by formatting the cell with a format that is not a date or time format. Click Cells on the Format menu, click the Number tab, and then select a format that is not a date or time format.

Show#VALUE!

Occurs when the wrong type of argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) or operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) is used.

  1. Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  2. Review the possible causes and solutions.

Possible causes and solutions

ShowEntering text when the formula requires a number or a logical value, such as TRUE or FALSE

Microsoft Excel cannot translate the text into the correct data type. Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!.

ShowEntering or editing an array formula, and then pressing ENTER

ShowEntering a cell reference, a formula, or a function as an array constant

ShowSupplying a range to an operator or a function that requires a single value, not a range

  • Change the range to a single value.
  • Change the range to include either the same row or the same column that contains the formula.

ShowUsing a matrix that is not valid in one of the matrix worksheet functions

ShowRunning a macro that enters a function that returns #VALUE!

Make sure the function is not using an incorrect argument.

Show#DIV/0!

Occurs when a number is divided by zero (0).

  1. Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  2. Review the possible causes and solutions.

Possible causes and solutions

ShowEntering a formula that contains explicit division by zero (0) — for example, =5/0

Change the divisor to a number other than zero.

ShowUsing the cell reference to a blank cell or to a cell that contains zero as a divisor

 Note   If the operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) is a cell that is blank, Microsoft Excel interprets the blank as zero.

  • 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, which will change the result of the formula to #N/A from #DIV/0! to denote that the divisor value is not available.
  • Prevent the error value from displaying, using the IF worksheet function. 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.

ShowRunning a macro that uses a function or a formula that returns #DIV/0!

Make sure the divisor in the function or formula is not zero or blank.

Show#NAME?

Occurs when Microsoft Excel doesn't recognize text in a formula.

  1. Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  2. Review the possible causes and solutions.

Possible causes and solutions

ShowUsing a function that is part of the Analysis Toolpak add-in, without the add-in being loaded

Install and load the Analysis Toolpak add-in (address: The path to an object, document, file, page, or other destination. An address can be a URL (Web address) or a UNC path (network address), and can include a specific location within a file, such as a Word bookmark or an Excel cell range.).

ShowHow?

  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.

ShowUsing a name that does not exist

Make sure the name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) exists. On the Insert menu, point to Name, and then click Define. If the name is not listed, add the name by using the Define command.

ShowMisspelling the name

ShowUsing a label in a formula, without labels being allowed

Allow labels to be used. On the Tools menu, click Options, and then click the Calculation tab. Under Workbook options, select the Accept labels in formulas check box.

ShowMisspelling the name of a function

Correct the spelling. Insert the correct function name into the formula by clicking Function on the Insert menu.

ShowEntering text in a formula without enclosing the text in double quotation marks

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

Enclose text in the formula in double quotation marks. For example, the following formula joins the piece of text "The total amount is " with the value in cell B50:

="The total amount is "&B50

ShowOmitting a colon (:) in a range reference

Make sure all range references in the formula use a colon (:); for example, SUM(A1:C10).

ShowReferencing another sheet 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 ( ' ).

Show#N/A

Occurs when a value is not available to a function or formula.

  1. Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  2. Review the possible causes and solutions.

Possible causes and solutions

ShowMissing data, and #N/A or NA() has been entered in its place

Replace #N/A with new data.

 Note   You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.

ShowGiving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function

ShowUsing the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table

By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_lookup argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) that instructs the function to find an exact match even if the table is not sorted. To find an exact match, set the range_lookup argument to FALSE.

The MATCH worksheet function contains a match_type argument that specifies the order the list must be sorted in to find a match. If the function cannot find a match, try changing the match_type argument. To find an exact match, set the match_type argument to 0.

ShowUsing an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula

If the array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) has been entered into multiple cells, make sure the ranges referenced by the formula have the same number of rows and columns, or enter the array formula into fewer cells. For example, if the array formula has been entered into a range 15 rows high (C1:C15) and the formula refers to a range 10 rows high (A1:A10), the range C11:C15 will display #N/A. To correct this error, enter the formula into a smaller range (for example, C1:C10), or change the range to which the formula refers to the same number of rows (for example, A1:A15).

ShowOmitting one or more required arguments from a built-in or custom worksheet function

ShowUsing a custom worksheet function that is not available

Make sure the workbook that contains the worksheet function is open and the function is working properly.

ShowRunning a macro that enters a function that returns #N/A

Show#REF!

Occurs when a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) is not valid.

  1. Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  2. Review the possible causes and solutions.

Possible causes and solutions

ShowDeleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas

Change the formulas, or restore the cells on the worksheet by clicking Undo Button image immediately after you delete or paste the cells.

ShowUsing a link to a program that is not running

Start the program.

ShowLinking to a Dynamic Data Exchange (DDE) topic such as "system" that is not available

Make sure that you're using the correct DDE topic.

ShowRunning a macro that enters a function that returns #REF!

Check the function to see if an argument (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) refers to a cell or range of cells that is not valid. For example, if the macro enters a function that refers to a cell above the function, and the cell that contains the function is in row 1, the function will return #REF! because there are no cells above row 1.

Show#NUM!

Occurs with invalid numeric values in a formula or function.

  1. Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  2. Review the possible causes and solutions.

Possible causes and solutions

ShowUsing an unacceptable argument in a function that requires a numeric argument

ShowUsing a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result

Use a different starting value for the worksheet function.

Change the number of times Microsoft Excel iterates formulas.

ShowHow?

  1. On the Tools menu, click Options, and then click the Calculation tab.
  2. Select the Iteration check box.
  3. To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum iterations box. The higher the number of iterations, the more time Excel needs to calculate a worksheet.
  4. To set the maximum amount of change you will accept between calculation results, type the amount in the Maximum change box. The smaller the number, the more accurate the result and the more time Excel needs to calculate a worksheet.

ShowEntering a formula that produces a number that is too large or too small to be represented in Microsoft Excel

Change the formula so that its result is between minus one times ten to the 307th and one times ten to the 307th.

Show#NULL!

Occurs when you specify an intersection of two areas that do not intersect. The intersection operator is a space between references.

  1. Click the cell that displays the error, click the button that appears Button image, and then click Trace Error if it appears.
  2. Review the possible causes and solutions.

Possible causes and solutions

ShowUsing an incorrect range operator

  • To refer to a contiguous range of cells, use a colon (:) to separate the reference to the first cell in the range from the reference to the last cell in the range. For example, SUM(A1:A10) refers to the range from cell A1 to cell A10 inclusive.
  • To refer to two areas that don't intersect, use the union operator, the comma (,). For example, if the formula sums two ranges, make sure a comma separates the two ranges (SUM(A1:A10,C1:C10)).

ShowRanges do not intersect

Change the reference so that it intersects.

ShowHow?

When you enter or edit a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), cell references and the borders around the corresponding cells are color-coded.

Color-coded cell references

Color-coded cell references

If there are no squares at each corner of the color-coded border, then the reference is to a named (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) range.

ShowChange references that are not to a named range

  1. Double-click the cell that contains the formula you want to change. Microsoft Excel highlights each cell or range of cells with a different color.
  2. Do one of the following:
    • To move a cell or range reference to a different cell or range, drag the color-coded border of the cell or range to the new cell or range.
    • To include more or fewer cells in a reference, drag a corner of the border.
    • In the formula, select the reference, and type a new one.
  3. Press ENTER.

ShowChange references that are to a named range

  1. Do one of the following:
    • Select the range of cells that contains formulas in which you want to replace references with names.
    • Select a single cell to change the references to names in all formulas on the worksheet.
  2. On the Insert menu, point to Name, and then click Apply.
  3. In the Apply names box, click one or more names.

ShowWatch a formula and result of a cell

Watch cells and their formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) on the Watch Window toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.), even when the cells are out of view.

Watch Window

Watch Window toolbar
  1. Select the cells you want to watch.

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

  1. On the Tools menu, point to Formula Auditing menu, and then click Show Watch Window.
  2. Click Add Watch Button image.
  3. Click Add.
  4. Move the Watch Window toolbar to the top, bottom, left, or right side of the window.
  5. To change the width of a column, drag the boundary on the right side of the column heading.
  6. To display the cell that an entry in Watch Window toolbar refers to, double-click the entry.

 Note   Cells that have links (external reference: A reference to a cell or range on a sheet in another Excel workbook, or a reference to a defined name in another workbook.) to other workbooks are displayed in the Watch Window toolbar only when the other workbook is open.

ShowCalculate a nested formula one step at a time

You can see the different parts of a nested formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) evaluated in the order the formula is calculated. For example, you can see this in the following formula where the function AVERAGE(F2:F5) is shown as its value, 80.

=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) as

=IF(80>50,SUM(G2:G5),0)

  1. Select the cell you want to evaluate. Only one cell can be evaluated at a time.
  2. On the Tools menu, point to 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.

  1. Continue until each part of the formula has been evaluated.
  2. 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.

ShowTrace the relationships between formulas and cells

You can display both precedent cells (precedents: Cells that are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.) and dependent cells (dependents: Cells that contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.) of formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).).

  1. On the Tools menu, click Options, and then click the View tab.
  2. Check that Show all or Show placeholders is selected under Objects.
  3. On the Tools menu, point to Formula Auditing, and then click Show Auditing Toolbar.
  4. Do one of the following.

ShowTrace 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. To display a tracer arrow (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.) to each cell that directly provides data to the active cell, click Trace Precedents Button image on the Formula Auditing toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  3. To identify the next level of cells that provide data to the active cell, click Trace Precedents Button image 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 Button image. To remove another level of tracer arrows, click the button again.

ShowTrace formulas that reference a particular cell (dependents)

  1. Select the cell for which you want to identify the dependent cells.
  2. To display a tracer arrow (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.) to each cell that is dependent on the active cell, click Trace Dependents Button image on the Formula Auditing toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).
  3. To identify the next level of cells that depend on the active cell, click Trace Dependents Button image 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 Button image. To remove another level of tracer arrows, click the button again.
  1. To remove all tracer arrows (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.) on the worksheet, click Remove All Arrows Button image on the Formula Auditing toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.).

 Note   Red arrows show cells that cause errors. If the selected cell is referenced by a cell on another worksheet or workbook, a black arrow points from the selected cell to a worksheet icon worksheet icon.The other workbook must be open before Microsoft Excel can trace these dependencies, however.

ShowTips

Select All button

ShowCorrect common problems in formulas

Like a grammar checker, Microsoft Excel uses certain rules to check for problems in formulas. These rules do not guarantee that your spreadsheet is problem-free, but they can go a long way to finding common mistakes. You can turn these rules on or off individually. Both methods used below present the same options.

ShowChange 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 you want.

ShowCorrect common formula problems one at a time, like a spelling checker

If the worksheet has previously been checked for problems and the problems were ignored, the problems will not appear until the ignored problems have been reset.

  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.
  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. Position the Error Checking dialog just below the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) Formula bar. The formula bar is where it is most convenient to make changes to a formula while using the Error Checker.
  6. Click a button on the right of the dialog. 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.

  1. Click Next.
  2. Continue until the error check is complete.

ShowMark common formula problems on the worksheet and correct them there

If a cell contains a formula that breaks one of the rules, a triangle appears in the top-left corner of the cell.

Cell with a formula problem

  1. Cell with a formula problem
  2. On the Tools menu, click Options, and then click the Error Checking tab.
  3. Select the Enable background error checking check box.
  4. To change the color of the triangle that marks where a problem occurs, in the Error Indicator Color box, select a new color.
  5. Select a cell with a triangle in the top-left corner of a cell.
  6. Next to the cell, click the button that appears Button image, and then click the option you want. The options are different for each type of problem, and the first entry describes the problem.

If you click Ignore Error, the problem is marked to be ignored for each consecutive check.

  1. Repeat the two previous steps.
 
 
Applies to:
Excel 2003