Amal Nagm

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.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

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.

Correct a #N/A error

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

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

Correct a #NAME? error

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

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

Correct a #NULL! error

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

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.

Correct a #NUM! error

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

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*10³⁰⁷ and 1*10³⁰⁷.

Correct a #REF! error

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.

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?