Formula errors can result in error values as well as cause unintended results. Below are some tools to help find and investigate errors.
Correct an error value, such as #NAME?
If a formula cannot properly evaluate a result, Microsoft Excel will display an error value. Each error type has different causes, and different solutions.
#####
Occurs when a column is not wide enough, or a negative date or time is used.
Review the possible causes and solutions.
Possible causes and solutions
Column is not wide enough to display the content
Increase the width of the column Select the column, point to Column on the Format menu, click Width, and then enter a number.
Shrink the contents to fit the column Select the column, then on the Format menu, click Cells, click the Alignment tab, and then select the Shrink to fit check box.
Apply a different number format In some cases, you can change the cell's number format to make the number fit within the existing cell width. For example, decrease the number of decimal places after the decimal point.
Dates and times are negative numbers
 If you are using the 1900 date system, dates and times in Microsoft Excel must be positive values.
 When you subtract dates and times, make sure you build the formula correctly.
 If the formula is correct, although the result is a negative value, you can display the value by formatting the cell with a format that is not a date or time format. Click Cells on the Format menu, click the Number tab, and then select a format that is not a date or time format.
#VALUE!
Occurs when the wrong type of 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.) or operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) is used.
 Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.
 Review the possible causes and solutions.
Possible causes and solutions
Entering text when the formula requires a number or a logical value, such as TRUE or FALSE
Microsoft Excel cannot translate the text into the correct data type. Make sure the formula or function is correct for the required operand or argument, and that the cells that are referenced by the formula contain valid values. For example, if cell A5 contains a number and cell A6 contains the text "Not available", the formula =A5+A6 will return the error #VALUE!.
Entering or editing an array formula, and then pressing ENTER
Entering a cell reference, a formula, or a function as an array constant
Supplying a range to an operator or a function that requires a single value, not a range
 Change the range to a single value.
 Change the range to include either the same row or the same column that contains the formula.
Using a matrix that is not valid in one of the matrix worksheet functions
Running a macro that enters a function that returns #VALUE!
Make sure the function is not using an incorrect argument.
#DIV/0!
Occurs when a number is divided by zero (0).
 Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.
 Review the possible causes and solutions.
Possible causes and solutions
Entering a formula that contains explicit division by zero (0) — for example, =5/0
Change the divisor to a number other than zero.
Using the cell reference to a blank cell or to a cell that contains zero as a divisor
Note If the operand (operand: Items on either side of an operator in a formula. In Excel, operands can be values, cell references, names, labels, and functions.) is a cell that is blank, Microsoft Excel interprets the blank as zero.
 Change the cell reference to another cell.
 Enter a value other than zero in the cell used as a divisor.
 Enter the value #N/A into the cell referenced as the divisor, which will change the result of the formula to #N/A from #DIV/0! to denote that the divisor value is not available.
 Prevent the error value from displaying, using the IF worksheet function. For example, if the formula that creates the error is =A5/B5, use =IF(B5=0,"",A5/B5) instead. The two quotation marks represent an empty text string.
Running a macro that uses a function or a formula that returns #DIV/0!
Make sure the divisor in the function or formula is not zero or blank.
#NAME?
Occurs when Microsoft Excel doesn't recognize text in a formula.
 Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.
 Review the possible causes and solutions.
Possible causes and solutions
Using a function that is part of the Analysis Toolpak addin, without the addin being loaded
Install and load the Analysis Toolpak addin (address: The path to an object, document, file, page, or other destination. An address can be a URL (Web address) or a UNC path (network address), and can include a specific location within a file, such as a Word bookmark or an Excel cell range.).
How?
 On the Tools menu, click AddIns.
 In the AddIns available list, select the Analysis ToolPak box, and then click OK.
 If necessary, follow the instructions in the setup program.
Using a name that does not exist
Misspelling the name
Using a label in a formula, without labels being allowed
Allow labels to be used. On the Tools menu, click Options, and then click the Calculation tab. Under Workbook options, select the Accept labels in formulas check box.
Misspelling the name of a function
Correct the spelling. Insert the correct function name into the formula by clicking Function on the Insert menu.
Entering text in a formula without enclosing the text in double quotation marks
(Excel tries to interpret your entry as a name even though you intended it to be used as text.)
Enclose text in the formula in double quotation marks. For example, the following formula joins the piece of text "The total amount is " with the value in cell B50:
="The total amount is "&B50
Omitting a colon (:) in a range reference
Make sure all range references in the formula use a colon (:); for example, SUM(A1:C10).
Referencing another sheet not enclosed 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 nonalphabetical character or a space, you must enclose its name within single quotation marks ( ' ).
#N/A
#REF!
#NUM!
Occurs with invalid numeric values in a formula or function.
 Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.
 Review the possible causes and solutions.
Possible causes and solutions
Using an unacceptable argument in a function that requires a numeric argument
Using a worksheet function that iterates, such as IRR or RATE, and the function cannot find a result
Use a different starting value for the worksheet function.
Change the number of times Microsoft Excel iterates formulas.
How?
 On the Tools menu, click Options, and then click the Calculation tab.
 Select the Iteration check box.
 To set the maximum number of times Microsoft Excel will recalculate, type the number of iterations in the Maximum iterations box. The higher the number of iterations, the more time Excel needs to calculate a worksheet.
 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 Excel needs to calculate a worksheet.
Entering a formula that produces a number that is too large or too small to be represented in Microsoft Excel
Change the formula so that its result is between and .
#NULL!
Occurs when you specify an intersection of two areas that do not intersect. The intersection operator is a space between references.
 Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.
 Review the possible causes and solutions.
Possible causes and solutions
Using an incorrect range operator
 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 a comma separates the two ranges (SUM(A1:A10,C1:C10)).
Ranges do not intersect
Change the reference so that it intersects.
How?
When you enter or edit 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 (=).), cell references and the borders around the corresponding cells are colorcoded.
Colorcoded cell references
If there are no squares at each corner of the colorcoded border, then the reference is to a named (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easytounderstand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) range.
Change references that are not to a named range
 Doubleclick the cell that contains the formula you want to change. Microsoft Excel highlights each cell or range of cells with a different color.
 Do one of the following:
 To move a cell or range reference to a different cell or range, drag the colorcoded 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.
 Press ENTER.
Change references that are to a named range
 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.
 On the Insert menu, point to Name, and then click Apply.
 In the Apply names box, click one or more names.
Watch a formula and result of a cell
Watch 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 (=).) on 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.), even when the cells are out of view.
Watch Window toolbar
 Select the cells you want to watch.
To select all cells on a worksheet with formulas, click Go To on the Edit menu, click Special, and then click Formulas.
 On the Tools menu, point to Formula Auditing menu, and then click Show Watch Window.
 Click Add Watch .
 Click Add.
 Move the Watch Window toolbar to the top, bottom, left, or right side of the window.
 To change the width of a column, drag the boundary on the right side of the column heading.
 To display the cell that an entry in Watch Window toolbar refers to, doubleclick the entry.
Note Cells that have links (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.
Calculate a nested formula one step at a time
You can see the different parts of 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 (=).) evaluated in the order the formula is calculated. For example, you can see this in the following formula where the function AVERAGE(F2:F5) is shown as its value, 80.
=IF(AVERAGE(F2:F5)>50,SUM(G2:G5),0) as
=IF(80>50,SUM(G2:G5),0)
 Select the cell you want to evaluate. Only one cell can be evaluated at a time.
 On the Tools menu, point to Formula Auditing menu, and then click Evaluate Formula.
 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.
 Continue until each part of the formula has been evaluated.
 To see the evaluation again, click Restart.
To end the evaluation, click Close.
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.
Trace the relationships between formulas and cells
You can display both precedent cells (precedents: 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.) and dependent cells (dependents: Cells that contain formulas that refer to other cells. For example, if cell D10 contains the formula =B5, cell D10 is a dependent of cell B5.) of 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 (=).).
 On the Tools menu, click Options, and then click the View tab.
 Check that Show all or Show placeholders is selected under Objects.
 On the Tools menu, point to Formula Auditing, and then click Show Auditing Toolbar.
 Do one of the following.
Trace cells that provide data to a formula (precedents)
Trace formulas that reference a particular cell (dependents)
 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, click Remove All Arrows on the Formula Auditing 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.).
Note 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 Microsoft Excel can trace these dependencies, however.
Tips
Correct common problems in formulas
Like a grammar checker, Microsoft Excel uses certain rules to check for problems in formulas. These rules do not guarantee that your spreadsheet is problemfree, but they can go a long way to finding common mistakes. You can turn these rules on or off individually. Both methods used below present the same options.
Change which common problems Excel checks for
 On the Tools menu, click Options, and then click the Error Checking tab.
 Select or clear the check box you want.
Correct common formula problems one at a time, like a spelling checker
If the worksheet has previously been checked for problems and the problems were ignored, the problems will not appear until the ignored problems have been reset.
 Select the worksheet you want to check for errors.
 If the worksheet is manually calculated, press F9 to recalculate now.
 On the Tools menu, click Error Checking.
 If you have previously ignored errors, and want to recheck them, click Options, click Reset Ignored Errors, click OK, and then click Resume.
 Position the Error Checking dialog 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.) . The formula bar is where it is most convenient to make changes to a formula while using the Error Checker.
 Click a button on the right of the dialog. The options are different for each type of problem.
If you click Ignore Error, the problem is marked to be ignored for each consecutive check.
 Click Next.
 Continue until the error check is complete.
Mark common formula problems on the worksheet and correct them there
If a cell contains a formula that breaks one of the rules, a triangle appears in the topleft corner of the cell.

Cell with a formula problem
 On the Tools menu, click Options, and then click the Error Checking tab.
 Select the Enable background error checking check box.
 To change the color of the triangle that marks where a problem occurs, in the Error Indicator Color box, select a new color.
 Select a cell with a triangle in the topleft corner of a cell.
 Next to the cell, click the button that appears , and then click the option you want. The options are different for each type of problem, and the first entry describes the problem.
If you click Ignore Error, the problem is marked to be ignored for each consecutive check.
 Repeat the two previous steps.