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.

cell with a formula problem Cell with a formula error

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.What do you want to do?

Change which common errors Excel checks for

  1. Click the Microsoft Office Button button image, 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 the Data Validation in-group button 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 button 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 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 On the Formulas tab, in the Formula Auditing group, click the Error Checking in-group button.

    excel ribbon image

    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 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 button image, 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 button imagethat 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.