Amal Nagm

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.

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.

Turn error checking rules on or off

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

2. Under Excel checking rules, select or clear the check boxes of any of the following rules:

   • Cells containing formulas that result in an error The formula does not use the expected syntax, arguments (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 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.

     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 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 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 can 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 ambiguous 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 them to numbers.
   • 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).

     1 2 3 4 5

     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 may 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 and to the cell that contains the formula (A8), and those three cells contain data that should have been referenced in the formula.

     1 2 3 4 5 6 7 8

     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 are locked for protection, so 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, it is not included in the calculation and the result is 22.75. If the third cell contains 0, that cell is included in the calculation and the result is 18.2.

     1 2 3 4 5 6 7

     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.

Correct common formula errors one at a time

Caution 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 On the Formulas tab, in the Formula Auditing group, click the Error Checking in-group button.

   

   The Error Checking dialog box is displayed when errors are found.

4. If you have previously ignored any errors, you can check for those errors again by doing the following:
   1. Click Options.
   2. In the Error Checking section, click Reset Ignored Errors.
   3. Click OK.
   4. Click Resume.

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

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

   

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

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

6. Repeat the two preceding steps.