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 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 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 lookup_value 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.
- 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 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.
#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*10307 and 1*10307.
#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.