MS Excel Tutorial - Fixing and Handling Errors

Excels Error Messages - What They Mean and How To Fix Them

Oct 5, 2009 Martin Bell

MS Excel provides seven different error types. These help the user to find errors and handle them, by either fixing the problem or by replacing the error message.

Almost every user of Microsoft Excel has come across at least one of the seven possible error types. The user can fix some of these error messages. Other error types just have to be “lived with”, or handled by amending the function or formula that produced them. Each is described briefly, with possible causes and solutions.

Excel Error #NULL

This error indicates that Excel cannot determine which cell is being referred to. For example, the function

=SUM(A1 A12) will return this error message. The correct syntax is:

=SUM(A1, A12) to sum the two cells A1 and A12, or;

=SUM(A1:A12) to sum the values in the range A1 to A12.

Excel Error #DIV0!

This error is returned when a formula tries to divide a number by zero. The zero may be explicit, or may be the result of a formula. For example, any formula that divides one cell value by another cell needs to ensure that the divisor is not equal to zero. The formula

=A1/A2 will give the #DIV0! Error if A2 equals zero.

To fix this, the function should be

=IF(A2=0,””,A1/A2)

Now, the formula will return blank if A2 is zero, but will calculate A1÷A2 if it is not.

Excel Error #VALUE!

This is returned when a cell value is the wrong data type for a function. The user needs to take care when using some formula types. The formula

=1/”A1” will give the #VALUE error, and the formula should read

=1/A1

Excel Error #REF!

This is displayed when a cell initially contains a reference to another cell, but the other cell is deleted. The easiest solution is to replace the deleted cells, if possible. If that is not possible, then the formula within the cell can be entered again manually.

This error may also happen when a VLOOKUP is incorrectly defined. For example the function

=VLOOKUP(7,A:B,3,FALSE) means “Look for number 7 in column A, and when it is found, return the corresponding value in column C”. The problem is that column C is not part of the lookup table (which is columns A to B). This error may be fixed by changing the function to

=VLOOKUP(7,A:C,3,FALSE)

Excel Error #NAME?

This error indicates that a function is trying to do a mathematical operation on text, or is referring to a cell that does not exist.

Example 1: Convert the hexadecimal number A to decimal

=HEX2DEC(A) will give the #NAME! Error, and should read

=HEX2DEC(“A”), which gives the correct answer 10.

Example 2:

The size of an Excel sheet is limited. Excel 97 has columns A to IV, and row 1 to 65536. If the function

=A65537 is entered, the #NAME! Error will display.

Each Excel version has a different maximum for row and column number, and this can be checked as a possible cause of this error.

Excel Error #NUM!

This error shows that one of the arguments in a function is out of the valid range for that function. Examples are

=SQRT(-1), or

=DEC2BIN(512,8) since this function has a limit of 511

When the #NUM! error is found, the user should check what limits the function has. See also this article on #NUM! and #NAME? errors.

Excel Error #N/A

This error indicates that the result does not exist. It is common when using the VLOOKUP (and other LOOKUP functions). The user may not mind that the error occurs, but it may be handled by using the IF and ISERROR functions.

In this example, the value in column A is found in column B, and the corresponding value for column C is returned. The IF function checks if there is an error (using the ISERROR function), and if there is, a blank is returned.

=IF(ISERROR(VLOOKUP(A1,B:C,2,FALSE),””, VLOOKUP(A1,B:C,2,FALSE))

There are other reasons why the VLOOKUP function may give the N/A error.

Excel Errors - Summary

There are seven error types supported with Excel. Examples of each error type, with possible causes and fixes are given in this tutorial, along with some functions that change undesirable error messages into blank cells.

More information about Excel functions is available free on-line, or in Excel for Dummies by Greg Harvey.

Share Article |

The copyright of the article MS Excel Tutorial - Fixing and Handling Errors in Computer Software is owned by Martin Bell. Permission to republish MS Excel Tutorial - Fixing and Handling Errors in print or online must be granted by the author in writing.

Excel Error Types

What do you think about this article?