Use error checking to correct common errors in formulas

Like a spelling checker that checks for errors in data that you enter in cells, you can implement certain rules to check for errors in formulas. These rules do not guarantee that your worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.) is error-free, but they can go a long way toward finding common mistakes. You can turn any of these rules on or off individually.

Errors can be marked and corrected in two ways: one error at a time (like a spelling checker), or immediately when they occur on the worksheet as you work. Either way, a triangle appears in the top-left corner of the cell when an error is found.


Cell with a formula problem

Cell with a formula error

Errors that cannot return a result (such as dividing a number by zero) require attention and display an error value in the cell (in this case, #DIV/0!). Other errors can be less serious, such as a formula that appears to be inconsistent with adjacent formulas. The formula returns a correct result, but the error advises you that you should examine the formula.


You can resolve an error by using the options that appear, or you can ignore the error by clicking Ignore Error. If you ignore an error in a particular cell, the error in that cell does not appear in further error checks. However, you can reset all previously ignored errors so that they appear again.

What do you want to do?


Turn error checking rules on or off

  1. Click the File tab, click Options, and then click the Formulas category.
  2. Under Excel checking rules, select or clear the check boxes of any of the following rules:

For more information about how to resolve these errors, see the links in the See Also list.

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

  • 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 UndoButton image 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 overwrites 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.
  • Cells containing years represented as 2 digits    The cell contains a text date that might be misinterpreted as the wrong century when it is 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 unclear text dates.
  • Numbers formatted as text or preceded by an apostrophe    The cell contains numbers stored as text. This typically occurs when data is imported from other sources. Numbers that are stored as text can cause unexpected sorting behaviors and cannot be calculated, so it is best to convert their cells to a Number format.
  • Formulas 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 differ only in the references used. In the following example of four adjacent formulas, Excel displays an error next to the formula =SUM(A10:F10) because the adjacent formulas increment by one row, and the formula =SUM(A10:F10) increments by 8 rows — Excel expects the formula =SUM(A3:F3).
A
Formulas
=SUM(A1:F1)
=SUM(A2:F2)
=SUM(A10:F10)
=SUM(A4:F4)
  • If the references that are used in a formula are not consistent with those in the adjacent formulas, Excel displays an error.
  • Formulas which omit cells in a region    A formula might not automatically include references to data that you insert between the original range of data and the cell that contains the formula. This rule compares the reference in a formula against the actual range of cells that is adjacent to the cell that contains the formula. If the adjacent cells contain additional values and are not blank, Excel displays an error next to the formula.

For example, Excel inserts an error next to the formula =SUM(A2:A4) when this rule is applied, because cells A5, A6, and A7 are adjacent to the cells that are referenced in the formula (A2:A4) and also to the cell that contains the formula (A8), and those three cells (A5, A6, and A7) contain data that should have been referenced in the formula.

A
Invoice
15,000
9,000
8,000
20,000
5,000
22,500
=SUM(A2:A4)
  • Unlocked cells containing formulas    The formula is not locked for protection. By default, all cells in a worksheet are locked for protection, so this means that the cell has been set by a user to be unprotected. When a formula is protected, it cannot be modified without first 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.
  • Formulas referring to empty cells    The formula contains a reference to an empty cell. This can cause unintended results, as shown in the following example.

Suppose you want to calculate the average of the numbers in the following column of cells. If the third cell is blank, because is not considered a value, it is not included in the calculation, so the result is 22.75. If the third cell contains 0 (0 is considered a value), that cell is included in the calculation and the result is 18.2.

A
Data
24
12
 
45
10
=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 in the Data Tools group on the Data tab.

Top of Page Top of Page

Correct common formula errors one at a time

If the worksheet has previously been checked for errors, any errors that were ignored will not appear until 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 Formula Auditing group on the Formulas tab

The Error Checking dialog box appears when errors are found.

  1. If you have previously ignored any errors, you can check for those errors again by doing the following (you can keep the Error Checking dialog box open):
    1. On the File tab, click Options, and then click Formulas.
    2. In the Error Checking section, click Reset Ignored Errors, and then click OK.
    3. In the Error Checking dialog box, click Resume.

 Note   Resetting ignored errors resets all errors in all worksheets in the active workbook.

  1. Position the Error Checking dialog box 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

  1. Click one of the action buttons in the right side of the dialog box. The available actions differ for each type of error.

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

  1. Click Next, and then continue until the error check is complete.

Top of Page Top of Page

Mark common formula errors on the worksheet and correct them there

  1. Click the File tab, click Options, and then click the Formulas category.
  2. Under Error Checking, make sure that the Enable background error checking check box is selected.
  3. To change the color of the triangle that marks where an error occurs, in the Indicate errors using this color box, select the color that you want.
  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 Button image that appears, and then click the option that you want. The available commands differ 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.

  1. Repeat the two preceding steps.

Top of Page Top of Page

 
 
Applies to:
Excel 2010