﻿ Find and correct errors in formulas

# Find and correct errors in formulas

Formulas can sometimes result in error values in addition to returning unintended results. The following are some tools that you can use to find and investigate the causes of these errors and determine solutions.

## Learn how to enter a simple formula

Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formula adds 3 to 1.

=3+1

A formula can also contain any or all of the following: functions, references, operators, and constants.

Parts of a formula

1. Functions: The PI() function returns the value of pi: 3.142...

2. References: A2 returns the value in cell A2.

3. Constants: Numbers or text values entered directly into a formula, such as 2.

4. Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies two or more numbers.

## Correct common errors when entering formulas

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

 Make sure that you… More information Start every function with the equal sign (=) If you omit the equal sign, what you type may be displayed as text or as a date. For example, if you type SUM(A1:A10), Microsoft Excel displays the text string SUM(A1:A10) and does not calculate the formula. If you type 11/2, Excel displays a date, such as 2-Nov or 11/02/09, instead of dividing 11 by 2. Match all open and close parentheses Make sure that all parentheses are part of a matching pair. When you use a function in a formula, it is important for each parenthesis to be in its correct position for the function to work correctly. For example, the formula =IF(B5<0),"Not valid",B5*1.05) will not work because there are two closing and only one open parenthesis. The correct formula looks like this: =IF(B5<0,"Not valid",B5*1.05). Use a colon to indicate a range When you refer to a range of cells, use a colon (:) to separate the reference to the first cell in the range and the reference to the last cell in the range. For example, A1:A5. Enter all required arguments Some worksheet functions have required arguments, and some functions (such as PI) take no arguments. Also, make sure that you have not entered too many arguments. For example, the UPPER function accepts only one string of text as its argument. Enter the correct type of arguments Some worksheet functions, such as SUM, require numerical arguments. Other functions, such as REPLACE, require a text value for at least one of their arguments. If you use the wrong type of data as an argument, Excel might return unexpected results or display an error. Nest no more than 64 functions You can enter, or nest, no more than 64 levels of functions within a function. For example, the formula =IF(SQRT(PI())<2,"Less than two!","More than two!") contains three functions: The PI function is nested inside the SQRT function, which is in turn nested inside the IF function. Enclose other sheet names 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 non-alphabetical character, you must enclose its name within single quotation marks ( ' ). Place an exclamation point (!) after a worksheet name when you refer to it in a formula For example, to return the value from cell D3 in a worksheet named Quarterly Data in the same workbook, use this formula: ='Quarterly Data'!D3. Include the path to external workbooks Make sure that each external reference contains a workbook name and the path to the workbook. A reference to a workbook includes the name of the workbook and must be enclosed in brackets ([]). The reference must also contain the name of the worksheet in the workbook. For example, to include a reference to cells A1 through A8 on the worksheet named Sales in the workbook (that is currently open in Excel) named Q2 Operations.xlsx, the formula looks like this: =[Q2 Operations.xlsx]Sales!A1:A8. If the workbook that you want to refer to is not open in Excel, you can still include a reference to it in a formula. You provide the full path to the file, such as in the following example: =ROWS('C:\My Documents\[Q2 Operations.xlsx]Sales'!A1:A8). This formula returns the number of rows in the range that includes cells A1 through A8 in the other workbook (8). Note    If the full path contains space characters, as does the preceding example, you must enclose the path in single quotation marks (at the beginning of the path and after the name of the worksheet, before the exclamation point). Enter numbers without formatting Do not format numbers when you enter them in formulas. For example, if the value that you want to enter is \$1,000, enter 1000 in the formula. If you enter a comma as part of a number, Excel treats it as a separator character. If you want numbers displayed so that they show thousands or millions separators, or currency symbols, format the cells after you enter the numbers. For example, if you want to add 3100 to the value in cell A3, and you enter the formula =SUM(3,100,A3), Excel adds the numbers 3 and 100 and then adds that total to the value from A3, instead of adding 3100 to A3. Or, if you enter the formula =ABS(-2,134), Excel displays an error because the ABS function accepts only one argument. Avoid dividing by zero Dividing a cell by another cell that either contains zero or no value can result in a #DIV/0! error.

## Correct common problems in formulas

You can implement certain rules to check for errors in formulas. These rules act like a spelling checker that checks for errors in data that you enter in cells. These rules do not guarantee that your worksheet 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 enter data. Either way, a triangle appears in the top-left corner of the cell when an error is found.

Cell with a formula error

You can resolve an error by using the options that Excel displays, 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 File tab, click Options, and then click the Formulas category.

2. Under Error 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, or data types. Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Each of these error values have different causes and are resolved in different ways.

Note    If you enter an error value directly in a cell, it is stored as that error value but is not marked as an error. However, if a formula in another cell refers to that cell, the formula returns the error value from that cell.

• 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 results, 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 the cell that contains the formula (A8), and those 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 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, 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 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.

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 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. Click OK to close the Excel Options dialog box.

4. To correct an error in a worksheet, 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.

## Correct an error value

If a formula cannot correctly evaluate a result, Excel displays an error value, such as #####, #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. Each error type has different causes, and different solutions.

The following table contains links to articles that describe these errors in detail, and a brief description to get you started.

 Link to in-depth article Description Excel displays this error when a column is not wide enough to display all the characters in a cell, or a cell contains negative date or time values. For example, a formula that subtracts a date in the future from a date in the past, such as =06/15/2008-07/01/2008, results in a negative date value. Excel displays this error when a number is divided either by zero (0) or by a cell that contains no value. Excel displays this error when a value is not available to a function or formula. This error is displayed when Excel does not recognize text in a formula. For example, a range name or the name of a function may be spelled incorrectly. Excel displays this error when you specify an intersection of two areas that do not intersect (cross). The intersection operator is a space character that separates references in a formula. For example, the areas A1:A2 and C3:C5 do not intersect, so entering the formula =SUM(A1:A2 C3:C5) returns the #NULL! error. Excel displays this error when a formula or function contains invalid numeric values. Excel displays this error when a cell reference is not valid. For example, you may have deleted cells that were referred to by other formulas, or you may have pasted cells that you moved on top of cells that were referred to by other formulas. Excel can display this error if your formula includes cells that contain different data types. If error checking for formulas is enabled, the ScreenTip displays "A value used in the formula is of the wrong data type." You can typically fix this problem by making minor changes to your formula.

## 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 Windowtoolbar. 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 have only 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.

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 workbooks are open.

### Remove cells from the Watch Window

1. If the Watch Windowtoolbar 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:

 Steps shown in the dialog box Description =IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) The nested formula is initially displayed. The AVERAGE function and the SUM function are nested within the IF function. =IF(40>50,SUM(G2:G5),0) The cell range F2:F5 contains the values 55, 35, 45, and 25, and so the result of the AVERAGE(F2:F5) function is 40. =IF(False,SUM(G2:G5),0) Because 40 is not greater than 50, the expression in the first argument of the IF function (the logical_test argument) is False. 0 The IF function returns the value of the third argument (the value_if_false argument). The SUM function is not evaluated because it is the second argument to the IF function (value_if_true argument), and it is returned only when the expression is True.
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.

6. To end the evaluation, click Close.

Notes

• Some parts of formulas that use the IF and CHOOSE functions are not evaluated — in these cases, #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 dialog box 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 File tab, click Options, and then click the Advanced category.

2. In the Display options for this workbook section, select the workbook you want, and then make sure that All or Nothing (hide objects) is selected under For objects, show.

3. If formulas reference cells in another workbook, open that workbook. Excel cannot go to a cell in a workbook that is not open.

4. Do one of the following:

• To trace cells that provide data to a formula (precedents), do the following:

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.

• To trace formulas that reference a particular cell (dependents), do the following:

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.

5. To remove all tracer arrows on the worksheet, on the Formulas tab, in the Formula Auditing group, click Remove Arrows .

• To see all the relationships on a worksheet, do the following:

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.

• 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:

1. References to text boxes, embedded charts, or pictures on worksheets

2. PivotTable reports

3. References to named constants

4. Formulas located in another workbook that refer to the active cell if the other workbook is closed

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.

Applies To: Excel 2010, Excel Starter

﻿