Correct a ##### error

This error indicates that a column is not wide enough to display all of its content, or that a negative date or time is used in a cell.

Symptom

Excel displays ##### in one or more cells on a worksheet.

Causes

• The column is not wide enough to display the content.
• Dates and times are negative numbers.

Resolution

• Increase the width of the column to fit the text by doing the following:
  1. Select the column by clicking the column header.
  2. On the Home tab, in the Cells group, click Format.

3. Under Cell Size, click AutoFit Column Width.
• Shrink the text size of the contents to fit the column by doing the following:
  1. Select the column by clicking the column header.
  2. On the Home tab, in the Alignment group, click the Dialog Box Launcher next to Alignment.

3. Under Text control, select the Shrink to fit check box.
• Apply a different number or date format.

In some cases, you can change the number or date format of a cell to make its contents fit within the existing cell width. For example, you can decrease the number of decimal places after the decimal point or switch from a Long Date to a Short Date format.

• 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 a formula that you use to calculate dates or times is correct but results in a negative value, do the following to display that value in a format that is not a date or time format:
  1. On the Home tab, in the Number group, click the Dialog Box Launcher next to Number.

2. In the Category box, click a number format that is not a date or time format.

See Also

• Find and correct errors in formulas

Excel > Formula and name basics > Correcting formulas

Correct a #DIV/0! error

Excel 2007

Excel displays the #DIV/0! error when a number is divided either by zero (0) or by a cell that contains no value.

Symptom

Excel displays the #DIV/0! error in one or more cells on a worksheet.

Causes

• Entering a formula that performs explicit division by zero (0) — for example, =5/0.
• Using a reference to a blank cell or to a cell that contains zero as the divisor in a formula or function that performs division.
• Running a macro that uses a function or a formula that returns the #DIV/0! error.

Example

When you copy the example data to a blank worksheet, the formulas in cells A3, A4, and A5 all return a #DIV/0! error.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Resolution

• Make sure that the divisor in the function or formula is not zero (0) or blank.
• Change the cell reference in the formula to another cell that does not contain a zero or a blank value.
• Enter the value #N/A in the cell that is referenced as the divisor in the formula.

Entering #N/A will change the result of the formula to #N/A from #DIV/0! to indicate that the divisor value is not available.

• Prevent the error value from being displayed by using the IF worksheet function. You can then display 0 or any string as the result.

For example, if the formula that produces the error is =A1/A2, use =IF(A2=0,"",A1/A2) to return an empty string, or =IF(A2=0,0,A1/A2) to return 0.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• Getting #DIV/0!, how to get 0%?
• How to sum cells and ignore the #div/0! 's ?
• Hiding Formulas when the value is "0"?

See Also

• Find and correct errors in formulas
• Overview of formulas

Excel > Formula and name basics > Correcting formulas

Correct a #N/A error

Excel 2007

This error indicates that a value is not available to a function or formula.

Symptom

Excel displays #N/A in one or more cells on a worksheet.

Causes

• Data is missing, and #N/A or NA() has been entered in its place.
• An inappropriate value was given for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function.
• The VLOOKUP, HLOOKUP, or MATCH worksheet function was used to locate a value in an unsorted table.
• An array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.) is using an argument that is not the same number of rows or columns as the range that contains the array formula.
• One or more required arguments were omitted from a built-in or custom worksheet function.
• A custom worksheet function that you use is not available.
• A macro that you run enters a function that returns #N/A.

Resolution

• Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error , click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

Tip  Review the following resolutions to help determine which option to click.

• If you manually entered #N/A in a cell, replace it with actual data if that data is now available. For example, if you entered #N/A in cells where data is not yet available, formulas that refer to those cells also return #N/A instead of attempting to calculate a value. If you enter a value instead, the error should be resolved in the cells that contain the formulas.
• Make sure that the lookup_value argument (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.) that you entered in a HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function is the correct type of value. For example, verify that you entered a value or a cell reference instead of a range reference.

For information about using the correct arguments with functions, see HLOOKUP function, LOOKUP function, MATCH function, or VLOOKUP function.

• By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain arange_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 value of the match_type argument. To find an exact match, set the match_type argument to 0.

• If an array formula has been entered into multiple cells, make sure that the ranges that are 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 that is 15 rows high (C1:C15) and the formula refers to a range that is 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).

For more information about working with array formulas, see Guidelines and examples of array formulas.

• Enter all required arguments in the function that returns the error.
• Make sure that the workbook that contains the worksheet function is open and that the function is working properly.
• Make sure that the arguments in the function are correct and are used in the correct position.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How to remove #n/a error in excel vlookup b/c value is not found?

See Also

• Find and correct errors in formulas
• Overview of formulas

Excel > Formula and name basics > Correcting formulas

Correct a #NAME? error

Excel 2007

Tags  errors; formula; functions; misspelling

What are tags?

This error occurs when Microsoft Office Excel does not recognize text in a formula.

Symptom

Excel displays #NAME? in one or more cells on a worksheet.

Causes

• The EUROCONVERT function is used in a formula, but the Euro Currency Tools add-in is not loaded.
• A formula refers to a name (name: A word or string of characters in Excel that represents a cell, range of cells, formula, or constant value.) that does not exist.
• A formula refers to a name that is not spelled correctly.
• The name of a function that is used in a formula is not spelled correctly.
• You may have entered text in a formula without enclosing it in double quotation marks.
• A colon (:) was omitted in a range reference.
• A reference to another sheet is not enclosed in single quotation marks (').
• A workbook that you open calls a user-defined function (UDF) that is not available on your computer.

Resolution

• Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error , click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

Tip  Review the following resolutions to help determine which option to click.

• The EUROCONVERT function requires that the Euro Currency Tools add-in (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) is installed on your computer. To install this add-in, do the following:
  1. Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.
  2. In the Manage list box, click Excel Add-ins, and then click Go.
  3. In the Add-Ins available list, select the Euro Currency Tools check box, and then click OK.
• Make sure that a name (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) that you refer to in a formula does indeed exist by doing the following:
  1. On the Formulas tab, in the Defined Names group, click Name Manager.
  2. In the Name Manager dialog box, verify that the name is listed.
  3. If the name is not listed, close the dialog box and then add the name by clicking Define Name in the Defined Names group.
• Correct the spelling of a misspelled name that you referred to in a formula by doing the following:
  1. Select the cell that contains the formula that you want to check for misspelled names.
  2. In 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.) , select the misspelled name in the formula, and then press F3.
  3. In the Paste name box, click the name that you want to use, and then click OK.
• Insert the correct function name in the formula that results in the error by doing the following:
  1. Select the cell that contains the formula containing the misspelled function.
  2. In 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.) , select the misspelled function name.
  3. In the Name Box (to the left of the formula bar), click the arrow and then select a function from the list that Excel suggests.

If you can't find the function that you want to use, click More Functions to see all available worksheet functions. Then locate the function that you want to use.

4. In the Function Arguments dialog box, review the argument values and then click OK to complete the formula.
• Enclose text in the formula in double quotation marks. For example, the following formula joins the text "The total amount is " with the value in cell B50:

="The total amount is "&B50

• Make sure that all range references in the formula use a colon (:). For example, SUM(A1:C10).
• If the formula refers to values or cells in other worksheets or workbooks, and the name of the other worksheet or workbook contains a nonalphabetical character or a space, you must enclose its name within single quotation marks ( ' ) in the formula.
• When a user-defined function (UDF) that is not available on your computer is called from a workbook that you open, you (or a developer) can implement a UDF in several ways. For more information, see Visual Basic Help and the Microsoft Office SharePoint Server 2007 Software Development Kit (SDK).

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• #NAME? Some users get error some don't?

See Also

• Find and correct errors in formulas
• Overview of formulas

Excel > Formula and name basics > Correcting formulas

Correct a #NULL! error

Excel 2007

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

Symptom

Excel displays #NULL! in one or more cells on a worksheet.

Causes

• You may have used an incorrect range operator.
• Ranges do not intersect.

Resolution

• Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error , click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

Tip  Review the following resolutions to help determine which option to click.

• Make sure that you use a correct range operator by doing the following:
  • 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)).
• Change the reference so that the ranges intersect. An intersection is a point in a worksheet where data in two or more ranges cross, or "intersect." An example of a formula that includes intersecting ranges is =CELL("address",(A1:A5 A3:C3)). In this example, the CELL function returns the cell address at which the two ranges intersect — A3.

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, the reference is to a named  range.
• If there are squares at each corner of the color-coded border, the reference is not to a named range.

Do one of the following:

• Change references that are not to a named range by doing the following:
  1. Double-click the cell that contains the formula you want to change. 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 by doing the following:
  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.

See Also

• Find and correct errors in formulas
• Overview of formulas

Excel > Formula and name basics > Correcting formulas

Correct a #NUM! error

Excel 2007

This error indicates that a formula or function contains invalid numeric values.

Symptom

Excel displays #NUM! in one or more cells on a worksheet.

Causes

• You may have used an unacceptable argument in a function that requires a numeric argument.
• You may have used a worksheet function that iterates (iteration: Repeated calculation of a worksheet until a specific numeric condition is met.), such as IRR or RATE, and the function cannot find a result.
• You may have entered a formula that produces a number that is too large or too small to be represented in Excel.

Resolution

• Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error , click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

Tip  Review the following resolutions to help determine which option to click.

• Make sure that the 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.) that are used in the function are numbers. For example, even if the value that you want to enter is $1,000, enter 1000 in the formula.
• Use a different starting value for the worksheet function.
• Change the number of times that Excel iterates formulas by doing the following:
  1. Click the Microsoft Office Button , click Excel Options, and then click the Formulas category.
  2. Under Calculation options, select the Enable iterative calculation 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.
• Change the formula so that its result is between -1*10307 and 1*10307.

See Also

• Find and correct errors in formulas
• Overview of formulas

Excel > Formula and name basics > Correcting formulas

Correct a #REF! error

Excel 2007

This error occurs when a cell reference (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) is not valid.

Symptom

Excel displays #REF! in one or more cells on a worksheet.

Causes

• 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.
• You may have used an Object Linking and Embedding (OLE) link to a program that is not running.

 Note    OLE is a technology that you can use to share information between programs.

• You may have linked to a Dynamic Data Exchange (DDE) topic (a group or category of data in the server part of a client/server application), such as "system," that is not available.

 Note    DDE is an established protocol for exchanging data between Microsoft Windows-based programs.

• You may have run a macro that enters a function on the worksheet that returns a #REF! error.

Example

Copy the example data to a blank worksheet, and then delete column D (the entire column). The formulas, which were originally in column E, shift to column D and they all display a #REF! error. If you select cell D2, Excel displays the formula =SUM(B2,C2,#REF!) in the formula bar.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

An error is displayed because the formulas in column E referred to column D and, because column D was deleted, the formula is no longer valid. Instead of repairing the formulas to refer to a different cell — which may not be what you want anyway — Excel displays this error to prompt you to correct the formulas yourself so that you don't get unexpected results. In this case, you repair the formulas by removing ",#REF!" from the formula in D2, and then dragging the formula down to the cells below.

Resolution

• Optionally, if error checking is turned on in Excel, click the button that appears next to the cell that displays the error , click Show Calculation Steps if it appears, and then click the resolution that is appropriate for your data.

Tip  Review the following resolutions to help determine which option to click.

• Change the formulas, or restore the cells on the worksheet by clicking Undo on the Quick Access Toolbar immediately after you delete or paste the cells.
• Start the program that is called for by an Object Linking and Embedding (OLE) link.
• Make sure that you are using the correct Dynamic Data Exchange (DDE) topic.
• Check the function to see if an argument (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.) refers to a cell or range of cells that is not valid. For example, if a macro enters a function on the worksheet 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.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• Can you make a cell = 0 if original function is creating a"#ref!"?
• REF# error on summing ranges

See Also

• Find and correct errors in formulas
• Overview of formulas

Excel > Formula and name basics > Correcting formulas

Correct a #VALUE! error

Excel 2007

Tags  errors; formula; troubleshoot; VALUE

What are tags?

Excel can display the #VALUE! error if your formula includes cells that contain different data types. If smart tags are turned on and you position the mouse pointer over the smart tag, 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.

Symptom

Excel displays the #VALUE! error in one or more cells in a worksheet.

Causes

• One or more cells that are included in a formula contain text, and your formula performs math on those cells by using the standard arithmetic operators (+, -, *, and /).

For example, the formula =A1+B1, where A1 contains the string "Hello" and B1 contains the number 3, returns the #VALUE! error.

• A formula that uses a math function, such as SUM, PRODUCT, or QUOTIENT, contains an argument that is a text string instead of a number.

For example, the formula PRODUCT(3,"Hello") returns the #VALUE! error because the PRODUCT function requires numbers as arguments.

• Your workbook uses a data connection, and that connection is unavailable.

Example

When you copy the example data to a blank worksheet, the formulas in cells A4 and A5 return a #VALUE! error; however, cells A6 and A7 return the correct value of 30.

How do I copy an example?

1. Select the example in this article.

Important  Do not select the row or column headers.

Selecting an example from Help

2. Press CTRL+C.
3. In Excel, create a blank workbook or worksheet.
4. In the worksheet, select cell A1, and press CTRL+V.

Important  For the example to work properly, you must paste it into cell A1 of the worksheet.

5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Resolution

• Instead of using arithmetic operators, use a function, such as SUM, PRODUCT, or QUOTIENT to perform an arithmetic operation on cells that may contain text, and avoid using arithmetic operators in the function. Instead, separate the arguments by using commas.
• Ensure that none of the arguments in a math function, such as SUM, PRODUCT, or QUOTIENT, contain text as an argument.
• If your workbook uses a data connection, take the steps that are required to restore the data connection or, if it is possible, consider importing the data.

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do I get a value instead of an error or txt in an Excel cell?

See Also

• Find and correct errors in formulas
• Overview of formulas

Excel > Formula and name basics > Correcting formulas

Remove or allow a circular reference

Excel 2007

Tags  calculation;  circular reference; equation; formula

 What are tags?

When a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) refers back to its own cell, either directly or indirectly, it creates a circular reference. A circular reference can have a significant impact on performance because it can iterate indefinitely. Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met. By default, iterative calculations are turned off in Microsoft Office Excel. You can handle a circular reference by doing one of the following: remove the circular reference or enable iterative calclulations.

What do you want to do?

Locate and remove a circular reference

Make a circular reference work by changing the number of times that Excel iterates formulas

Locate and remove a circular reference

If an error message about creating a circular reference appears while editing a formula, then you probably have created an unintended circular reference. In this case, you can locate and remove the incorrect reference.

1. On the Formulas tab, in the Formula Auditing group, click the arrow on the Error Checking in-group button, point to Circular References, and then click the first cell listed in the submenu.

Tip  You can move between cells in a circular reference by double-clicking the tracer arrows.

2. Review the formula in the cell. If you cannot determine whether the cell is the cause of the circular reference, click the next cell in the Circular References submenu.

 Note    The status bar displays the word "Circular References" followed by a reference to one of the cells contained in the circular reference.

3. Continue to review and correct the circular reference until the status bar no longer displays the word "Circular References."

 Top of Page

Make a circular reference work by changing the number of times that Excel iterates formulas

If you want to keep the circular reference, you can enable iterative calculations but you must determine how many times the formula should recalculate. When you turn on iterative calculations without changing the values for maximum iterations or maximum change, Office Excel stops calculating after 100 iterations or after all values in the circular reference change by less than 0.001 between iterations, whichever comes first. However, you can control the the maximum number of iterations and the amount of acceptable change.

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 calculation check box.
3. To set the maximum number of times that Office 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 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.

 Top of Page

See Also

• Change formula recalculation, iteration, or precision

Excel > Formula and name basics > Correcting formulas

Display the relationships between formulas and cells

Excel 2007

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

5. Select the cell for which you want to identify the dependent cells.
6. 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.

7. To identify the next level of cells that depend on the active cell, click Trace Dependents again.
8. 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

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

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

12. References to text boxes, embedded charts , or pictures on worksheets
13. PivotTable reports 
14. References to named constants 
15. 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.

Excel > Formula and name basics > Correcting formulas

Evaluate a nested formula one step at a time

Excel 2007

Sometimes, understanding how a nested formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) 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.

Excel > Formula and name basics > Correcting formulas

Find and correct errors in formulas

Excel 2007

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.

What do you want to do?

Learn how to enter a simple formula

Correct common errors when entering formulas

Correct common problems in formulas

Turn error checking rules on or off

Correct common formula errors one at a time

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

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 5 to the product of 2 and 3, for a result of 11.

=5+2*3

Show me 

                             Video created by Office Online staff writers

 Problems watching the video? Try our troubleshooting tips.

A formula can also contain any or all of the following: functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.), references, operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.), and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.).

Parts of a formula

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

References: A2 returns the value in cell A2.

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

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

 Top of Page

Correct common errors when entering formulas

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

 Top of Page

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

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

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

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

• 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

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

 Top of Page

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.

 Top of Page

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.

The following demo shows how to fix a #REF! error.

Show me 

                             Video created by Office Online staff writers

 Problems watching the video? Try our troubleshooting tips.

 Top of Page

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 (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) in the Watch Window toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.). 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 (docked: Fixed to the top, bottom, or side of the window. You can dock a task pane, the toolbox, the menu bar, and all toolbars.) 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.

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 (external reference: A reference to a cell or range on a sheet in another Excel workbook, or a reference to a defined name in another workbook.) to other workbooks are displayed in the Watch Window toolbar only when the other workbooks are open.

 Top of Page

Remove cells from the Watch Window

1. If the Watch Window toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.) 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 .

 Top of Page

Evaluate a nested formula one step at a time

Sometimes, understanding how a nested formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) 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 — 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.

 Top of Page

Display the relationships between formulas and cells

Sometimes, checking formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) 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 (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.).

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 make sure that All or Placeholders 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 (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.) 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 (tracer arrows: Arrows that show the relationship between the active cell and its related cells. Tracer arrows are blue when pointing from a cell that provides data to another cell, and red if a cell contains an error value, such as #DIV/0!.) 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 (embedded chart: A chart that is placed on a worksheet rather than on a separate chart sheet. Embedded charts are beneficial when you want to view or print a chart or a PivotChart report with its source data or other information in a worksheet.), or pictures on worksheets
  2. PivotTable reports (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.)
  3. References to named constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.)
  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.

 Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do I replace "#N/A" error, to continue my formula w/o error?
• Formulas stop working after saving?
• Can you have fixed cell reference when dragging/copying formulae?
• Formula displayd as text ???

See Also

• Correct a #DIV/0! error
• Correct a #NAME? error
• Correct a #NUM! error
• Correct a #REF! error
• Correct a #VALUE! error
• Examples of commonly used formulas
• Overview of formulas
• Remove or allow a circular reference

Excel > Formula and name basics > Correcting formulas

Hide or display formulas

Excel 2007

For security or privacy reasons, you may want to hide a formula from users of your worksheet. Or you may be auditing formulas and you need to see all the formulas on the worksheet. You can use the following procedures to control the hiding or displaying of formulas.

What do you want to do?

Switch between displaying formulas and their values on a worksheet

Prevent a formula from displaying in the formula bar

Show formulas that were previously hidden by removing protection

Switch between displaying formulas and their values on a worksheet

• Press CTRL + ` (grave accent).

 Top of Page

Prevent a formula from displaying in the formula bar

 Caution    This procedure also prevents the cells that contain the formula from being edited.

1. Select the range of cells whose formulas you want to hide. You can also select nonadjacent ranges (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) or the entire sheet.
2. On the Home tab, in the Cells group, click Format, click Format Cells, and then click the Protection tab.

3. Select the Hidden check box.
4. Click OK.
5. On the Home tab, in the Cells group, click Format, and then click Protect Sheet.

6. Make sure that the Protect worksheet and contents of locked cells check box is selected.

 Top of Page

Show formulas that were previously hidden by removing protection

1. On the Review tab, in the Changes group, click Unprotect Sheet.

2. Select the range of cells whose formulas you want to unhide.
3. On the Home tab, in the Cells group, click Format, click Format Cells, and then click the Protection tab.
4. Clear the Hidden check box.

 Top of Page

Excel > Formula and name basics > Correcting formulas

Replace a formula with its result

Excel 2007

You can "freeze" a formula so that it no longer recalculates by replacing a formula with its calculated value. If you want to freeze only part of a formula, you can replace only the part you don't want to recalculate. Replacing a formula with its result can be helpful if there are many or complex formulas in the workbook and you want to improve performance by creating static data.

Important  Make sure you examine the impact of replacing a formula with its results, especially if the formulas reference other cells that contain formulas. It's a good idea to make a copy of the workbook before replacing a formula with its results.

What do you want to do?

Replace a formula with its calculated value

Replace part of a formula with its calculated value

Replace a formula with its calculated value

 Caution    When you replace a formula with its value, Microsoft Office Excel permanently removes the formula. If you accidentally replace a formula with a value and want to restore the formula, click Undo immediately after you enter or paste the value.

1. Select the cell that contains the formula.

If the formula is an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), select the range that contains the array formula.

How to select a range that contains the array formula

1. Click a cell in the array formula.
2. On the Home tab, in the Editing group, click Find & Select, and then click Go To.
3. Click Special.
4. Click Current array.
2. Click Copy .
3. Click Paste .
4. Click the arrow next to Paste Options , and then click Values Only.

Tip  When you are editing a cell with a formula, you can press F9 to permanently replace the formula with its calculated value.

 Top of Page

Replace part of a formula with its calculated value

 Caution    When you replace part of a formula with its value, it cannot be restored.

1. Click the cell that contains the formula.
2. In 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.) , select the portion of the formula that you want to replace with its calculated value. When you select the part of the formula that you want to replace, make sure that you include the entire operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.). For example, if you select a function, you must select the entire function name, the opening parenthesis, the 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.), and the closing parenthesis.
3. To calculate the selected portion, press F9.
4. To replace the selected portion of the formula with its calculated value, press ENTER.

If the formula is an array formula (array formula: A formula that performs multiple calculations on one or more sets of values, and then returns either a single result or multiple results. Array formulas are enclosed between braces { } and are entered by pressing CTRL+SHIFT+ENTER.), press CTRL+SHIFT+ENTER.

 Top of Page

Excel > Formula and name basics > Correcting formulas

Watch a formula and its result by using the Watch Window

Excel 2007

When cells are not visible on a worksheet, you can watch those cells and their formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) in the Watch Window toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.). 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 (docked: Fixed to the top, bottom, or side of the window. You can dock a task pane, the toolbox, the menu bar, and all toolbars.) 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.

What do you want to do?

Add cells to the Watch Window

Remove cells from the Watch Window

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 & Replace, 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 (external reference: A reference to a cell or range on a sheet in another Excel workbook, or a reference to a defined name in another workbook.) to other workbooks are displayed in the Watch Window toolbar only when the other workbook is open.

 Top of Page

Remove cells from the Watch Window

1. If the Watch Window toolbar (toolbar: A bar with buttons and options that you use to carry out commands. To display a toolbar, press ALT and then SHIFT+F10.) 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 .

 Top of Page

See Also

• Ribbon, Help, and other Microsoft Office keyboard shortcuts

Excel > Formula and name basics > Correcting formulas

Correct common errors in formulas

Excel 2007

Tags  errors; formula

What are tags?

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

Correct common formula errors one at a time

Mark common formula errors on the worksheet and correct them there

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

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

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

• 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

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.

 Top of Page

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.

 Top of Page

Related Office Online discussions

Read related questions and answers from other Microsoft Office customers.

• How do I replace "#N/A" error, to continue my formula w/o error?

See Also

• Correct a ##### error
• Correct a #DIV/0! error
• Correct a #N/A error
• Correct a #NAME? error
• Correct a #NULL! error
• Correct a #NUM! error
• Correct a #REF! error
• Correct a #VALUE! error