#N/A #VALUE #DIV/0 #NAME? #REF #NUM #NULL #### error messages in Microsoft Excel, Error Handling in MS Excel

Hi friends,

While discussing about functions in Excel, often we get #N/A, #VALUE, #DIV/0, #NAME?, #REF, #NUM, #NULL, ####  or other types of errors while calculating or using formulas.

In today's discussion, I'll give you few ideas to handle these errors very efficiently.

Free Download Excel Working File
View in YouTube How to Eliminate Excel Error Very Easily and Quickly

First of all let us know the meaning of these errors, knowing the error type will help you to get rid of this problem very quickly and easily.

From the above table it is very clear why these errors occurs in excel, some are rectifiable within the formula some are not, they needs special care while preparing data or analyzing data.

So, let's discuss one by one...

1. "####" error : it is very simple just widen the column i.e., increase the width of the column.

The row of ##### occurs in a variety of instances, such as when:

a. an entered value is wider than the current cell width for a cell formatted for dates or times.

b. A formula entered into the cell formatted for numbers produces a result that is wider than the cell.

c. A number or text data, in excess of 253 characters,  is entered into a cell formatted for numbers dates, times, or accounting.

d. A negative number resides in a cell that has been formatted for dates or times. Dates and times in Excel must be positive.

2.#VALUE! error : in maximum case you will find this error is because of the different type of data types, for example if you add one cell containing number and the other cell having a text it will show you this error.

3. #DIV/0! error : if you try to divide some value by zero this error displayed, this is always means 'divide by zero' is happening.

The divisor or denominator in a division operation is equal to zero - either explicitly - such as =A5 / 0 - or as the result of a second calculation that has zero for a result. It might occur because a formula referencing a cell that is blank.

4. #N/A error : The most common error found in lookup formulas (VLOOKUP, HLOOKUP, INDEX and MATCH etc.), means lookup value not found, this error needs rectification in your formula and you can use a function to eliminate this error (not only this error but for maximum Excel errors) and it is =IFERROR(value, value_if_error) which I'll discuss letter in this post. 

But remember this error can be very usefully utilized in data analysis to find out the mismatch in two or more complicated and large data containing columns (I'll discuss it in my YouTube Video).

5. #NAME? error : this error is primarily occurs because of the typo error e.g., if you type =COUNTIIF (One 'I' is extra in this example) in place of =COUNTIF or SUUM instead of SUM function you will get this error.

6. #REF! error : reference error, as the name suggest this error is displayed because of non existing reference in the formula or it may display as you delete the data accidentally.

The possible reasons are :

a. individual cells or entire columns or rows containing data referenced in a formula are accidentally deleted.

b. data from one cell is moved (using cut and paste or drag and drop) into a cell that is referenced by a formula.

c. a formula contains a link - using OLE (Object Linking and Embedding) - to a program that is not currently running.

7. #NUM! error : Entering a non-numeric argument to a function that expects a numeric value. A function or a value that evaluates to a numeric value beyond the Excel’s capabilities to handle. Result of a function that could not find a valid value in spite multiple iterations. Very rare in real life data processing if you are a little bit cautious.

8. #NULL! error : occurs when the two or more cell references are separated incorrectly or unintentionally by a space in a formula.

Multiple cell references in a formula are separated by a space instead of a mathematical operator such as a plus sign ( + ) - example: =A1 A3+A5, or

the start and end points of cell ranges are separated by a space instead of by the range operator - the colon ( : ) - example: =SUM( A1 A5) or

individual cell references in a formula are separated by a space instead the union operator - the comma ( , ) - example: =SUM( A1 A3,A5) or

the intersect operator - the space character - is used intentionally, but the specified ranges do not intersect - example: =SUM(A1:A5 B1:B5)

How to Correct these errors ?

There are various techniques to correct this type of error display in Microsoft Excel, the best method we use is a function (and I personally love to use it always while typing a formula, it's a good habit) IFERROR()

Syntax

=IFERROR(value, value_if_true)

Use your formula in 'value' parameter and you can write anything in the parameter 'value_if_true' e.g., 

while calculating numbers you can write it '0' or if you need text to be displayed, you can write any text but within double quote e.g., "Value not found" etc. Please look carefully the below image, it will be more understandable.

#N/A #VALUE #DIV/0 #NAME? #REF #NUM #NULL & #### error messages in Microsoft Excel

You can use another function ISERROR()

Syntax

=ISERROR(VALUE)

Use your formula in 'value' parameter, it will return TRUE if error FOUND and FALSE if error NOT FOUND. But here you need to use IF logical function (I'll discuss in detail about this interesting logical function latter in this blog) to get the better result as described in the below image.

How to use ISERROR function in Excel

Sometimes you may get an #N/A error in VLOOKUP(), HLOOKUP(), MATCH() with INDEX() functions and although you can find or view the existence of the data but is not showing i.e., VLOOKUP returns #N/A error where the value exists by the formula like the image below. 

VLOOKUP #N/A Error in Excel

It's mainly because of the data type change, you may notice a little green triangle in the left-upper corner of the cell, it means that the data is in text format instead of number. A square box appears while this error occurs, you can click inside this box to view the message about the probable cause of this error. 

Youtube Link 1

Please view my YouTube Channel for more information regarding this errors, you'll get adequate information about this topic.

Thanks a lot...