Contents

This post teaches you how to correct Excel formula errors.

## Correct common errors in formulas

- Start every function with the equal sign (=).
- Match all open and close parentheses.
- Use a colon to indicate a range.
- Enter all required arguments.
- Enter the correct type of arguments.
- Nest no more than 64 functions.
- Enclose other sheet names in single quotation marks.
- Place an exclamation point (!) after a worksheet name when you refer to it in a formula.
- Include the path to external workbooks.
- Enter numbers without formatting.
- Avoid dividing by zero.

## Correct an error value in formula

### ##### error

#### Excel displays ##### error when

- A column is not wide enough to display all the characters in a cell.
- A cell contains negative date values.
- A cell contains negative time values.

#### How to correct a ##### error

- Adjust the column width.
- If the error is due to negative date or time values, make them positive.

### #DIV/0! error

#### Excel displays this error when

- A number is divided by zero (0).
- A number is divided by a cell that contains no value.

#### How to correct a #DIV/0! error

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

### #N/A error

#### Excel displays this error when

- Data is missing, and
**#N/A**or**NA()**has been entered in its place. - An inappropriate value was given for the
argument in the*lookup_value***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 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**.

#### How to correct a #N/A error

- 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
that you entered in a*lookup_value***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. - By default, functions that look up information in tables must be sorted in ascending order. However, the
**VLOOKUP**and**HLOOKUP**worksheet functions contain aargument 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*range_lookup***FALSE**.The**MATCH**worksheet function contains aargument 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*match_type***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.

### #NAME error

#### Excel displays this error when

- The
**EUROCONVERT**function is used in a formula, but the**Euro Currency Tools**add-in is not loaded. - A formula refers to a name 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.
- Text may have been entered 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 calls a user-defined function (UDF) that is not available on your computer.

#### How to correct a #NAME error

- The
**EUROCONVERT**function requires that the Euro Currency Tools add-in is installed on your computer. - Make sure that a name that you refer to in a formula does in fact exist.
- Correct the spelling of a misspelled name that you referred to in a formula.
- Insert the correct function name in the formula that results in the error.

### #NULL! error

#### Excel displays this error when

- You may have used an incorrect range operator.
- The ranges that you specified in a formula do not intersect

#### How to correct a #NULL! error

- Make sure that you use a correct range operator.
- Change the reference so that the ranges intersect.

### #NUM! error

#### Excel displays this error when

- The wrong data type might be supplied in a function that requires a numeric argument.
- The formula might use a worksheet function that iterates, such as
**IRR**or**RATE**, and that function cannot find a result. - The result of a formula might produce a number that is too large or too small to be represented in Excel.

#### How to correct a #NUM! error

- Make sure that the arguments 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
- Change the formula so that its result is between -1*10
^{307}and 1*10^{307}.

### #REF! error

#### Excel displays this error when

- Cells may have been deleted that were referred to by other formulas, or cells may have been pasted on top of other cells that were referred to by other formulas.
- There may be an Object Linking and Embedding (OLE) link to a program that is not running.
- There may be a link 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.
- There may be a macro in the workbook that enters a function on the worksheet that returns a
**#REF!**error.

#### How to correct a #REF! error

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

### #VALUE! error

#### Excel displays this error when

- 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**/**). - 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,"FIX")**returns the #VALUE! error because the**PRODUCT**function requires numbers as arguments. - Your workbook uses a data connection, and that connection is unavailable.

#### How to correct a #VALUE! error

- 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 directly in the function. If your formula uses a function, and that function refers to a cell that contains text, that cell is ignored, and no error is displayed. - 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.

## Leave a Reply