Excel Error and Related Information

While working with Microsoft Excel, we come with various type of error. This Article will guide you through the different error in excel with example and fix for each of these error.

#DIV/0!

This error comes when something is divided with 0.

Example: A Cell with the Formula =15/0 would return in this error.

Fix: Do not divide the value by Zero.

#NULL!

This is rare error. When you use incorrect range operators often displays this error.

Example: The Formula =SUM(A1:A10 C1:C10) returns a #NULL! error because there is no seperator between the two Range.

Fix: Make sure to mentioned the ranges properly.

#VALUE!

This error is seen when you use text parameters to a Function that accepts numbers.

Example: The Formula =SUM(“a”,”b”) returns #VALUE! error.

Fix: Make sure your Formula parameters have correct Data Types. If you are using Functions that work on numbers like SUM, AVERAGE then the parameters should be numbers.

#NAME?

This error comes when something is misspelled in a formula or Named Range. This error will also occur when we forget to close a text in double quotes or omit the range operator.

Example: =SUMM(a1:a10), =sum(range1, UndefinedName), =SUM(a1a10), =GOOD

Fix: Make sure you have mentioned the correct Formula name. In 2007 or later version of Excel, while typing the Formula, Excel shows all the matched ones. In earlier versions of Excel, if we use correct Formulas, they will be automatically capitalized. For example, if you type =sum(1,2,3) in a Celland press enter, it will be changed to =SUM(1,2,3). You can use this way to correct formulas. Make sure you have defined all the Named Ranges you are using in the formula. Make sure any User Defined Functions (UDF) you are using are properly installed. Double check the ranges and string parameters in your formulas.

#NUM!

This error is seen when a wrong data type is supplied in a Function that requires a numeric Argument. This error is also seen when a 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.

Example: The Formula=4389^7E+37 returns a #NUM! error.

Fix: 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. Make your numbers smaller or provide right starting values to your iterative formulas.

#N/A

This is one of the frequent errors you see while using any Lookup Functions. This error is shown when some data is missing, or inappropriate arguments are passed to the Lookup Functions such as VLOOKUP, HLOOKUP, MATCH.

Example: =MATCH(“India”, A1:A10,0). If this formula returns the #N/A error, it means that India is not there in A1:A10 Range.

Fix: Use some Error Handing tricks such as IFERROR. We can modify the above Formula as =IFERROR(MATCH(“India”, A1:A10,0),”Not Found”). This will print Not Found whenever the Formula returns any error including #N/A.

#REF!

This is one of the most common error messages we see when we play with a Worksheet full of Formulas. We get #REF! error when one of the formula parameters is pointing to an invalid range. This can happen because we deleted the cells.

Example: Try to write a forumla like =A1 and then delete the Column A or Row 1. Immediately the Formula returns #REF! error.

Fix: First Undo the actions you have performed. And then rewrite the Formula in a better way.

######

A Cell full of # symbols is seen when the contents cannot fit in the Cell. This is also seen when you format negative numbers as Dates.

Example: A Long number like 12223456345 entered in a small cell will show ####.

Fix: Adjust the Column width. And if the error is due to negative dates, make them positive.