Find and correct errors in formulas

Formulas can result in error values as well as cause unintended results. Below are some tools to enable you to find and investigate the causes and determine solutions.What do you want to do?

Correct common errors when entering formulas

Correct common problems in formulas

Change which common errors Excel checks for

Correct common formula errors one at a time, like a spelling checker

Mark common formula errors on the worksheet and correct them there

Correct an error value

Watch a formula and its result by using the Watch Window

Add cells to the Watch Window

Remove cells from the Watch Window

Evaluate a nested formula one step at a time

Display the relationships between formulas and cells

Correct common errors when entering formulas

The following table summarizes some of the the most common errors that you can make when entering a formula and how to correct those errors:

Correct common problems in formulas

Like a grammar checker, Microsoft Office Excel uses certain rules to check for errors in formulas. These rules do not guarantee that your spreadsheet is error-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.

Errors can be reviewed in two ways: one at a time like a spelling checker, or immediately on the worksheet as you work. A triangle appears in the top-left corner of the cell when an error is found. Both methods present the same options.

An error can be resolved by using the options that appear, or it can be ignored. If an error is ignored, it does not appear in further error checks. However, all previously ignored errors can be reset so that they appear again.

Change which common errors Excel checks for

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.

2. Select or clear the check boxes that you want under the Excel displays an error when section.

   Cells containing formulas that result in an error

   The formula does not use the expected syntax, arguments, or data types. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Each error value has different causes and is resolved in different ways.

   Note If you enter an error value directly in a cell, it is not marked as an error.

   Text formatted cells contain years represented as 2 digits

   The cell contains a text date that can be misinterpreted as the wrong century when used in formulas. For example, the date in the formula =YEAR("1/1/31") could be 1931 or 2031. Use this rule to check for ambiguous text dates.

   Numbers are formatted as text or preceded by an apostrophe

   The cell contains numbers stored as text. These usually come from data imported from other sources. Numbers stored as text can cause unexpected sorting behaviors, and it is best to convert them to numbers.

   A formula is inconsistent with other formulas in the region

   The formula does not match the pattern of other formulas near it. In many cases formulas that are adjacent to other formulas only differ in the references used. For example, the formula =SUM(A10:F10) would be noted because the adjacent formulas change by one row, and it changes by 8 rows.

   Formulas
   =SUM(A1:F1)
   =SUM(A2:F2)
   =SUM(A10:F10)
   =SUM(A4:F4)

   If the references used in a formula are not consistent with those in the adjacent formulas, then the error is noted.

   A formula omits cells in a region

   The formula may not include a correct reference. If a formula refers to a range of cells, and you add cells to the bottom or right of that range, the references may no longer be correct. The formula does not always automatically update its reference to include the new cells. This rule compares the reference in a formula against adjacent cells. If the adjacent cells contain more numbers (are not blank cells), then the error is noted.

   For example, the formula =SUM(A2:A4) would be noted with this rule, because A5, A6, and A7 are adjacent, and contain data.

   Invoice
   15,000
   9,000
   8,000
   20,000
   5,000
   22,500
   =SUM(A2:A4)

   Unlocked cells contain formulas

   The formula is not locked for protection. By default, all cells are locked for protection, so the cell has been set to be unprotected. When a formula is protected it cannot be modified without being unprotected. Check to make sure that you do not want the cell protected. Protecting cells that contain formulas prevents them from being changed, and can help avoid future errors.

   A formula refers to empty cell(s)

   The formula contains a reference to an empty cell. This can cause unintended results, as in the following example.

   Suppose you want to take the average of the numbers below. If the third cell down is blank, then the result is 22.75. If the third cell down contains 0, then the result is 18.2.

   Data
   24
   12
   45
   10

   Formula
   =AVERAGE(A2:A6)

   Data entered in a table is invalid

   There is a validation error in a table. Check the validation setting for the cell by clicking Data Validation on the Data tab in the Data Tools group.

   Inconsistent calculated column formula in tables

   A calculated column can include formulas that are different from the column formula, which creates an exception. Calculated column exceptions are created when you do any of the following:

   • Type data other than a formula in a calculated column cell.
   • Type a formula in a calculated column cell, and then click Undo on the Quick Access Toolbar.
   • Type a new formula in a calculated column that already contains one or more exceptions.
   • Copy data into the calculated column that does not match the calculated column formula.

     Note If the copied data contains a formula, this formula will overwrite the data in the calculated column.

   • Move or delete a cell on another worksheet area that is referenced by one of the rows in a calculated column.

Correct common formula errors one at a time, like a spelling checker

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

1. Select the worksheet that you want to check for errors.

2. If the worksheet is manually calculated, press F9 to recalculate now.

3. On the Formulas tab, in the Formula Auditing group, click Error Checking.

   

   The Error Checking dialog box is displayed.

4. If you have previously ignored errors, you can recheck them.

   How to reset ignored errors

   1. Click Options.
   2. In the Error Checking section, click Reset Ignored Errors.
   3. Click OK.
   4. Click Resume.

5. Position the Error Checking dialog box just below the 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 box. The options are different for each type of error.

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

7. Click Next.

8. Continue until the error check is complete.

Mark common formula errors on the worksheet and correct them there

1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.

2. In the Error Checking section, select the Enable background error checking check box.

3. To change the color of the triangle that marks where an error occurs, in the Indicate errors using the color box, select a new color.

4. Select a cell with a triangle in the top-left corner of a cell.

5. Next to the cell, click the Error Checking button that appears, and then click the option that you want. The commands are different for each type of error, and the first entry describes the error.

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

6. Repeat the two previous steps.

Correct an error value

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

Correct a ##### error

This error occurs when a column is not wide enough, or a negative date or time is used.

• Review the following possible causes and solutions.

  The column is not wide enough to display the content

  Do one or more of the following:

  Increase the width of the column

  1. Select the column by clicking the column header.
  2. On the Home tab, in the Cells group, click Format, and then click AutoFit Column Width.

     

     Tip Alternatively, you can double-click the boundary to the right of the column heading.

  Shrink the contents to fit the column

  1. Select the column.
  2. On the Home tab, in the Cells group, click Format, click Format Cells, and then click the Alignment tab.

     

  3. 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, you can decrease the number of decimal places after the decimal point.

  Dates and times are negative numbers

  • If you are using the 1900 date system, dates and times in Microsoft Office Excel must be positive values.

  • When you subtract dates and times, make sure that you build the formula correctly.

  • If the formula is correct but the result is still a negative value, you can display the value by formatting the cell with a format that is not a date or time format.

    1. On the Home tab, in the Cells group, click Format, click Format Cells, and then click the Number tab.

       

    2. Select a format that is not a date or time format.

Correct a #DIV/0! error

This error occurs when a number is divided by zero (0).

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions.

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

   Change the divisor to a number other than zero.

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

   Note If the operand is a cell that is blank, Microsoft Office 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 changes 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 by 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.

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

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

Correct a #N/A error

This error occurs when a value is not available to a function or formula.

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions.

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

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

   Make sure that the lookup_value argument is the correct type of value - for example, a value or a cell reference, but not a range reference.

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

   Using 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 has been entered into multiple cells, make sure that 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).

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

   Enter all arguments in the function.

   Using a custom worksheet function that is not available

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

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

   Make sure that the arguments in the function are correct and in the correct position.

Correct a #NAME? error

This error occurs when Microsoft Office Excel doesn't recognize text in a formula.

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions.

   Using the EUROCONVERT function without the Euro Currency Tools add-in being loaded

   The EUROCONVERT function requires the Euro Currency Tools add-in .

   Install and load the Euro Currency Tools Add-in add-in

   1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
   2. Select Excel Add-ins in the Manage list box, and then click Go.
   3. In the Add-Ins available list, select the Euro Currency Tools check box, and then click OK.

   ---

   Using a name that does not exist

   Make sure that the name exists. On the Formulas tab, in the Defined Names group, click Name Manager, and then see if the name is listed. If the name is not listed, add the name by clicking New.

   Misspelling the name

   Verify the spelling. Select the name in the formula bar , press F3, click the name that you want to use, and then click OK.

   Misspelling the name of a function

   Correct the spelling. Insert the correct function name into the formula by clicking Function Wizard in the Function Library group on the Formulas tab.

   Entering 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

   Omitting a colon (:) in a range reference

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

   Referencing 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 ( ' ).

Correct a #NULL! error

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

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions.

   Using 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 that a comma separates the two ranges (SUM(A1:A10,C1:C10)).

   Ranges do not intersect

   Change the reference so that it intersects.

   When you enter or edit a formula , cell references and the borders around the corresponding cells are color-coded.

   Color-coded cell references

   The first cell reference is B3, the color is blue, and the cell range has a blue border with square corners.

   The second cell reference is C3, the color is green, and the cell range has a green border with square corners.

   • If there are no squares at each corner of the color-coded border, then the reference is to a named range.
   • If there are squares at each corner of the color-coded border, then the reference is not to a named range.

   Do one of the following:

   Change references that are not to a named range

   1. Double-click the cell that contains the formula you want to change. Microsoft Office 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.

   Change 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 Formulas tab, in the Defined Names group, click the arrow next to Define Name, and then click Apply Names.
   3. In the Apply Names box, click one or more names.

Correct a #NUM! error

This error occurs with invalid numeric values in a formula or function.

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions.

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

   Make sure that the arguments used in the function are numbers. For example, even if the value you want to enter is $1,000, enter 1000 in the formula.

   Using 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 Office Excel iterates formulas.
     1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
     2. In the Calculation options section, select the Enable iterative calculations check box.
     3. To set the maximum number of times that Excel will recalculate, type the number of iterations in the Maximum Iterations box. The higher the number of iterations, the more time that Excel needs to calculate a worksheet.
     4. To set the maximum amount of change that 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 that Excel needs to calculate a worksheet.

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

   Change the formula so that its result is between -1*10³⁰⁷ and 1*10³⁰⁷.

Correct a #REF! error

This error occurs when a cell reference is not valid.

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions.

   Deleting 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 immediately after you delete or paste the cells.

   Using an Object Linking and Embedding (OLE) link to a program that is not running

   Start the program.

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

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

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

   Check the function to see if an argument 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.

Correct a #VALUE! error

This error occurs when the wrong type of argument or operand is used.

1. Optionally, click the cell that displays the error, click the button that appears , and then click Show Calculation Steps if it appears.
2. Review the following possible causes and solutions.

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

   Microsoft Office Excel cannot translate the text into the correct data type. Make sure that 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!.

   Entering or editing an array formula, and then pressing ENTER

   Select the cell or range of cells that contains the array formula, press F2 to edit the formula, and then press CTRL+SHIFT+ENTER.

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

   Make sure that the array constant is not a cell reference, formula, or function.

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

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

   Make sure that the dimensions of the matrix are correct for the matrix arguments.

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

   Make sure that the function is not using an incorrect argument.

Watch a formula and its result by using the Watch Window

When cells are not visible on a worksheet, you can watch those cells and their formulas in the Watch Window toolbar . The Watch Window makes it convenient to inspect, audit, or confirm formula calculations and results in large worksheets. By using the Watch Window, you don't need to repeatedly scroll or go to different parts of your worksheet.

This toolbar can be moved or docked like any other toolbar. For example, you can dock it on the bottom of the window. The toolbar keeps track of the following properties of a cell: workbook, sheet, name, cell, value, and formula.

Note You can only have one watch per cell.

Add cells to the Watch Window

1. Select the cells that you want to watch.

   To select all cells on a worksheet with formulas, on the Home tab, in the Editing group, click Find & Select, click Go To Special, and then click Formulas.

   

2. On the Formulas tab, in the Formula Auditing group, click 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.
6. To change the width of a column, drag the boundary on the right side of the column heading.
7. To display the cell that an entry in Watch Window toolbar refers to, double-click the entry.

Note Cells that have external references to other workbooks are displayed in the Watch Window toolbar only when the other workbook is open.

Remove cells from the Watch Window

1. If the Watch Window toolbar is not displayed, on the Formulas tab, in the Formula Auditing group, click Watch Window.
2. Select the cells that you want to remove.

   To select multiple cells, press CTRL and then click the cells.

3. Click Delete Watch .

Evaluate a nested formula one step at a time

Sometimes, understanding how a nested formula calculates the final result is difficult because there are several intermediate calculations and logical tests. However, by using the Evaluate Formula dialog box, you can see the different parts of a nested formula evaluated in the order the formula is calculated. For example, the formula =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) is easier to understand when you can see the following intermediate results:

1. Select the cell that you want to evaluate. Only one cell can be evaluated at a time.
2. On the Formulas tab, in the Formula Auditing group, 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.

   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.

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.

Notes:

• Some parts of formulas that use the IF and CHOOSE functions are not evaluated, and #N/A is displayed in the Evaluation box.
• If a reference is blank, a zero value (0) is displayed in the Evaluation box.
• The following functions are recalculated each time the worksheet changes, and can cause the Evaluate Formula to give results different from what appears in the cell: RAND, AREAS, INDEX, OFFSET, CELL, INDIRECT, ROWS, COLUMNS, NOW, TODAY, RANDBETWEEN.

Display the relationships between formulas and cells

Sometimes, checking formulas for accuracy or finding the source of an error can be difficult when the formula uses precedent or dependent cells:

• Precedent cells are 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.
• Dependent cells contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.

To assist you in checking your formulas, you can use the Trace Precedents and Trace Dependents commands to graphically display, or trace the relationships between these cells and formulas with tracer arrows .

1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
2. In the Display options for this workbook section, select the workbook you want, and then check that All or Placeholders is selected under For objects, show.
3. If formulas reference cells in another workbook, open that workbook. Microsoft Office Excel cannot go to a cell in a workbook that is not open.
4. Do one of the following.

   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. To display a tracer arrow to each cell that directly provides data to the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Precedents .

      Blue arrows show cells with no errors. 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 . The other workbook must be open before Excel can trace these dependencies.

   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, on the Formulas tab, in the Formula Auditing group, click the arrow next to Remove Arrows, and then 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. To display a tracer arrow to each cell that is dependent on the active cell, on the Formulas tab, in the Formula Auditing group, click Trace Dependents .

      Blue arrows show cells with no errors. 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 . The other workbook must be open before Excel can trace these dependencies.

   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, on the Formulas tab, in the Formula Auditing group, click the arrow next to Remove Arrows, and then click Remove Dependent Arrows . To remove another level of tracer arrows, click the button again.

   See all the relationships on a worksheet

   1. In an empty cell, type = (equal sign).
   2. Click the Select All button.

      

   3. Select the cell, and on the Formulas tab, in the Formula Auditing group, click Trace Precedents twice.

   Issue: Microsoft Excel beeps when I click the Trace Dependents or Trace Precedents command.

   If Excel beeps when you click Trace Dependents or Trace Precedents , either Excel has traced all levels of the formula, or you are attempting to trace an item that cannot be traced. The following items on worksheets that can be referenced by formulas cannot be traced by using the auditing tools:

   • References to text boxes, embedded charts, or pictures on worksheets
   • PivotTable reports
   • References to named constants
   • Formulas located in another workbook that refer to the active cell if the other workbook is closed
5. To remove all tracer arrows on the worksheet, on the Formulas tab, in the Formula Auditing group, click Remove Arrows .

Notes:

• To see the color-coded precedents for the arguments in a formula, select a cell and press F2.
• To select the cell at the other end of an arrow, double-click the arrow. If the cell is in another worksheet or workbook, double-click the black arrow to display the Go To dialog box, and then double-click the reference you want in the Go to list.
• All tracer arrows disappear if you change the formula the arrows point to, insert or delete columns or rows, or delete or move cells. To restore the tracer arrows after making any of these changes, you must use auditing commands on the worksheet again. To keep track of the original tracer arrows, print the worksheet with the tracer arrows displayed before you make the changes.