• Home
• Income Tax
• PPF
• Forms
• Widgets
• Tax Planning
• Forex
• Learn PC
• Miscellaneous
• Blog

Excel Tutorial

Excel Formula Audit

Find and correct errors in formulas

Formula errors can result in error values as well as cause unintended results. Below are some tools to help find and investigate errors.

If a formula cannot properly evaluate a result, Microsoft Excel will display an error value. Each error type has different causes, and different solutions.

1. Error Value#####
2. Error Value#VALUE!
3. Error Value#DIV/0!
4. Error Value#NAME?
5. Error Value#N/A
6. Error ValueREF!
7. Error Value#NUM!
8. Error Value#NULL!
9. Correct common problems in formulas
10. Mark common formula problems on the worksheet and correct them there

Error Value#####

Occurs when a column is not wide enough, or a negative date or time is used.

Possible causes and solutions 1. 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.

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

Error Value#VALUE!

Occurs when the wrong type of 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 (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.

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

Make sure the dimensions of the matrix (matrix: A rectangular array of values or a range of cells that is combined with other arrays or ranges to produce multiple sums or products. Excel has predefined matrix functions that can produce the sums or products.) 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.

Error Value#DIV/0!

• Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.

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

Error Value#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. Possible causes and solutions.

• Using a function that is part of the Analysis Toolpak add-in, without the add-in being loaded.

  Install and load the Analysis Toolpak add-in.

• Using a name that does not exist

  Make sure the 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.) 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.

• Misspelling the name

  Verify the spelling. Select the name 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.) , 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 non-alphabetical character or a space, you must enclose its name within single quotation marks ( ' ).

Error Value#N/A

Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears.

Possible causes and solutions.

• Missing data, and #N/A or NA() has been entered in its place

  Replace #N/A with new data.
  NoteYou 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.

• Giving an inappropriate value for the lookup_value argument in the HLOOKUP, LOOKUP, MATCH, or VLOOKUP worksheet function

  Make sure the lookup_value argument is the correct type of value- for example, a value or a cell reference, but not a range reference.

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

• Using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula

  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.

Error ValueREF!

Occurs when a 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.

Click the cell that displays the error, click the button that appears , and then click Trace Error if it appears. Possible causes and solutions

• Deleting cells referred to by other formulas, or pasting moved cells over cells referred to by other formulas
• Change the formulas, or restore the cells on the worksheet by clicking Undoimmediately 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.

Error Value#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. 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 by

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

Error Value#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. 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.

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

  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 (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 directly provides data to the active cell, click Trace Precedentson the Formula Auditing toolbar.
  To identify the next level of cells that provide data to the active cell, click Trace Precedentsagain.
  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 Dependentson the Formula Auditing toolbar.
  To identify the next level of cells that depend on the active cell, click Trace Dependentsagain.
  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 Arrowson the Formula Auditing toolbar.
  NoteRed 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 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 Precedentstwice.

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.

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