# Excel Errors and How to Fix Them

0
681

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

#### #DIV/0 Error

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.

#### #NULL! Error

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.

#### #N/A Error

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.

#### #NAME? Error

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”.

#### #NUM! Error

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.

#### #REF! Error

In Excel #REF! error occurs due to invalid cell reference. There are 2 common reasons to cause of Excel formula error: –

1. When entered cell get deleted
2. 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.

#### #VALUE! 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.

#### ######## Error

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 ###