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.
Occurs when the wrong type of argument or operand 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
Select the cell or range of cells that contains the array formula, press F2 to edit the formula, and then press CTRL+SHIFT+ENTER.
Entering a cell reference, a formula, or a function as an array constant
Make sure the array constant is not a cell reference, formula, or function.
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
Make sure the dimensions of the matrix are correct for the matrix arguments.
Running a macro that enters a function that returns #VALUE!
Make sure the function is not using an incorrect argument.
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)
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 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.
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 name that does not exist
Make sure the name exists. On the Insert menu, point to Name, and then click Define. If the name is not listed, add the name by using the Define command.
Verify the spelling. Select the name in the formula bar
, press F3, click the name you want to use, and then click OK.
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 ( ' ).
Occurs when a value is not available to a function or 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
Missing data, and #N/A or NA() has been entered in its place
Replace #N/A with new data.
Note You can enter #N/A in those cells where data is not yet available. Formulas that refer to those cells will then return #N/A instead of attempting to calculate a value.
Make sure the lookup_value argument is the correct type of value
Using the VLOOKUP, HLOOKUP, or MATCH worksheet function to locate a value in an unsorted table
By default, functions that look up information in tables must be sorted in ascending order. However, the VLOOKUP and HLOOKUP worksheet functions contain a range_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 match_type argument. To find an exact match, set the match_type argument to 0.
If the array formula has been entered into multiple cells, make sure the ranges 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 15 rows high (C1:C15) and the formula refers to a range 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).
Omitting one or more required arguments from a built-in or custom worksheet function
Enter all arguments in the function.
Using a custom worksheet function that is not available
Make sure the workbook that contains the worksheet function is open and the function is working properly.
Running a macro that enters a function that returns #N/A
Make sure the arguments in the function are correct and in the correct position.
Occurs when a cell reference is not valid.
-
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
Change the formulas, or restore the cells on the worksheet by clicking Undo
immediately after you delete or paste the cells.
Using a link to a program that is not running
Start the program.
Linking to a Dynamic Data Exchange (DDE) topic such as "system" that is not available
Make sure that you're using the correct DDE topic.
Running a macro that enters a function that returns #REF!
Check the function to see if an argument refers to a cell or range of cells that is not valid. For example, if the macro enters a function 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.
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
Make sure the arguments used in the function are numbers. For example, even if the value you want to enter is $1,000, enter 1000 in the formula.
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.
-
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.
Change the formula so that its result is between 
and 
.
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)).
Change the reference so that it intersects.
When you enter or edit a formula, cell references and the borders around the corresponding cells are color-coded.
Color-coded cell references
If there are no squares at each corner of the color-coded border, then the reference is to a named range.
Change references that are not to a named range
-
Double-click 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 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.
-
-
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 on the Watch Window toolbar, 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, double-click the entry.
Note Cells that have links 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 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 and dependent cells of formulas.
- 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)
- Select the cell that contains the formula for which you want to find precedent cells.
- To display a tracer arrow to each cell that directly provides data to the active cell, click Trace Precedents
on the Formula Auditing
toolbar. -
To identify the next level of cells that provide data to the active cell, click Trace Precedents
again. -
To remove tracer arrows one level at a time, starting with the precedent cell farthest away from the active cell, click Remove Precedent Arrows
. To remove another level of tracer arrows, click the button again.
Trace formulas that reference a particular cell (dependents)- Select the cell for which you want to identify the dependent cells.
- To display a tracer arrow to each cell that is dependent on the active cell, click Trace Dependents
on the Formula Auditing
toolbar. -
To identify the next level of cells that depend on the active cell, click Trace Dependents
again. -
To remove tracer arrows one level at a time, starting with the dependent cell farthest away from the active cell, click Remove Dependent Arrows
. To remove another level of tracer arrows, click the button again.
-
To remove all tracer arrows on the worksheet, click Remove All Arrows
on the Formula Auditing
toolbar.
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.
-
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 and then double-click the reference you want in the Go to list.
-
To see all the relationships on a worksheet, in an empty cell type = (equal sign), and then click the Select All button. Select the cell, and press Trace Precedents
twice.
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 problem-free, but they can go a long way to finding common mistakes. You can turn these rules on or off individually. Both methods used below present the same options.
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
Caution 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
. 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 top-left 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 top-left 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.