When Excel detect any error in formula then it gives a message by presenting different type of error. Every excel errors contain the reason of error, that is helpful to understand the problem and useful to fix the error.
Below is table of Excel errors for quick reference: –
|#DIV/0||–||Division by zero|
|#NULL!||–||In a formula cell references get separated in a wrong way|
|#N/A||–||No value available|
|#NAME?||–||Excel doesn’t recognize function name|
|#NUM!||–||Problem with a number in the formula|
|#REF!||–||Invalid cell reference|
|#VALUE!||–||wrong type of operator or argument in function|
|########||–||Column is not wide enough to accommodate a large value|
|Circular Reference||–||Define the result in a cell which is the part of formula|
In Microsoft Excel #DIV/0 error occurs when we divide any value with zero, then Excel shows this error from #DIV/0. Whenever you get this error check denominator value in data.
For example, if cell A1 contains the value 6 and cell B1 contains 0 then function will return below shown result: –
Solution: – If you want to ignore this error then use IFERROR function or ISERROR function along with IF Function.
Excel returns #NULL! Error when 2 or more cell references are separated in wrong way or space as operator in a formula.
For example, If we enter a formula in cell A1 =SUM(C1 D1) then we will get the #NULL! error, because space is used as intersect operator.
Solution: – Separate all cell references correctly in a formula.
Excel formula #N/A error returns when we check for values in another data from which few values are not available in the data. For example: – Below we have billing data to a shop sold items, other side we have a Billing ID for which we want to check sold unit. We have used VLOOKUP function to check the value but formula is giving #N/A error, it means this Billing ID is not available in data.
Solution: – If you want to ignore this error and print something else then use IFERROR function along with VLOOKUP function.
When we enter incorrect formula name or the formula which is not exist in excel then Excel gives #NAME? error. This #NAME? error can appear due to several reasons. In which most common reason is misspelling of function.
For Example: – In Cell A1 we have entered COUNT function but due to mis-spell in function it is returning #NAME? error.
Solution: – Whenever you enter any formula Excel shows the list of formula in drop down, you can pick formula name from the list by pressing “TAB”.
This error occurs when Excel encountered invalid number in a formula or a large value. For example: – In square root formula we have entered negative value which is not valid for square root function, this is the reason function is returning #NUM! error.
Solution: – Before entering numeric value in the argument check each value. If you have entered a nested formula and it is returning #NUM! error then check for numeric value instead of formula.
In Excel #REF! error occurs due to invalid cell reference. There are 2 common reasons to cause of Excel formula error: –
- When entered cell get deleted
- When we supplied the range to the edge of the spreadsheet
For Example: – We have taken the sum of the value of Cell A1 and Cell B1 in Cell C1, after deleting cell B1 function is giving #REF! error as result.
Solution: – Whenever you get #REF! error, then check for cell references in calculation to resolve the error.
In Excel #VALUE! Error occurs when in a formula referred cell does not contain any numeric value. It could be text or special character instead of numeric value.
For Example: – In below image when we have entered text or special character while calculation then formula returns #VALUE! Error and when we supply the numeric value it returns proper result.
Solution: – Whenever you get #VALUE! error, then check for cell values to resolve the error or if you want to ignore this error then you can use IFERROR function along with used function.
This error occurs when a number’s length is too large and it doesn’t fit into a cell. For Example: – If we enter 76475702706101102873846056538674356 in a cell it will appear as ###
Circular Reference Error
Circular Reference error occurs rarely in Microsoft Excel. When we define the same cell in formula in which we put the formula then it gives an error that is called Circular Reference error.
For Example: – We have inserted COUNT function in cell A7 and A7 is the part of formula, that is circular reference.
Solution: – Don’t include the cell as reference in which formula is calculating.