Site icon ExcelNotes

8 Common Errors in Excel

#DIV/0!#N/A Error#NAME?#NULL!#NUM!#REF!#VALUE!#######

When working with Excel, one or more errors may appear in your data or analysis. Each error has its own reason.

1. What is #DIV/0! Error?

"#DIV/0!" appears when a number is divided either by zero (0) or by a cell that contains no value. Please check your denominator when this error appears.

If you want to correct this error, you can use the IF Function to return blank when the denominator is 0 or blank.

= IF(B1<>0,A1/B1,"")

2. What is #N/A Error?

"#N/A" appears when a value is not available to a function or a formula.

For example, when using the Vlookup function, the #N/A error value indicates that the exact number isn't found. In the following table, the value of cell A3 is 78 which is not in the column D. The result returns #N/A.

3. What is #NAME? Error?

"#NAME?" appears when you incorrectly type a range name or a function name. For example, to average 3 and 4, the formula should be "=average(3,4)". "#NAME?" will appear when you type a wrong formula "=averag(3,4)" where the last letter "e" is missing.

4. What is #NULL! Error?

"#NULL!" appears when you specify an intersection of two areas with incorrect intersection operator. For example, you will get #NULL! error when the comma is missing in the formula "=SUM(A1:A2 B1:B2)".

5. What is #NUM! Error?

"#NUM!" appears when a formula or function contains invalid numeric values. For example, LOG Function requires a positive real number. "#NUM!" will appear when you enter a negative number (e.g., -10).

6. What is #REF! Error?

"#REF!" appears when a cell reference is not valid, for example, in the cell A2, type formula "=B2+D9", then delete column D, the value in A2 will return #REF! error.

7. What is #VALUE! Error?

"#VALUE!" appears when a different type of data is included in one formula. For example, formula "=A1+A2+A3+A4+A5" returns #VALUE! because both Text and Numbers are included.

8. What is "#######" Error?

"#######" appears when a column is not wide enough to display all the characters in a cell. For example, cell A2 "5/30/2016" will return "#######" when you narrow column A.

Download Common Errors

Exit mobile version