What Are Excel Formula Errors?

When using any software, we may encounter various errors, and Excel is no exception in this. When we make a mistake entering the formula in an Excel cell, Excel warns us about the error by placing symbols in the cell. Knowing common Excel errors helps us understand the problem and correct the formulas we have written. In this blog, we will discuss these errors and the reason each of them occurs.

#VALUE

This error appears when you use an unexpected or wrong type of data as the input of the formula. For instance, you have given a text to a mathematical function, or a number to a text function.

Example: #VALUE error in B4 has appeared because cell B3 contains the text “a.”

As another example, we can see that MINUTE function returns #VALUE error because it can’t extract the minute from the text “a.”

#NAME

Excel assumes that any unrecognized string which is not the name of a function is a name range, and when it doesn’t recognize a name range, #NAME error appears. This error occurs when you misspell a function name in the formula, do not enter the text values used in the formula into the mark “, or leave blank parentheses for the function.

Example: #NAME error appeared in A4 because the AVERAGE function is misspelled “average.”

#NUM

This error occurs when your numeric value is not valid. It is too large, too small, or the calculation is not possible. For instance, when you have used a function that made a loop and Excel can’t finish the calculation.

Example: #NUM error occurred because “-4” is not valid for the SQRT function.

#DIV/0

This error indicates that your formula is dividing by zero or an empty cell. Excel considers empty cells as zero, and if you divide a number by an empty cell, this error appears.

Example: As can be seen below, #DIV/0 error appeared in B4 because B2 is divided by an empty cell.

#REF!

This error occurs when a reference becomes invalid, and it happens when the reference of a cell is deleted or transferred. It also happens when you copy a formula containing relative references to a cell where the references are invalid. Excel tries to automate all references, but when it can’t, the actual cell reference is replaced by an error.

Example: The formula in C8 is copied to E3. As can be seen, the reference is relative and when we copy the formula, it becomes invalid and #REF! error appears.

#N/A

This error usually appears when the value you are referring to in a formula cannot be found. The most common cause of #N/A error is when the LOOKUP functions (i.e., VLOOKUP, HLOOKUP LOOKUP, and MATCH) can’t find the matching value.

Example: as can be seen below, #N/A error appears, because there is no “Don” in the lookup table.

#NULL

This error appears when you have used space character instead of mathematical symbols, comma (,) or colon (:). This is because space character is “range intersect” which returns a reference to the intersection of two ranges and when two areas don’t intersect, #NULL error appears.

Example: As can be seen below, in F4 space character is used instead of “,”

####

Technically, this is not an error. When this sign appears, it means that Excel has been able to calculate the formula successfully, but has not been able to display the calculated value in the cell.

Example: Here, we can see that nothing is wrong with the formula we have written. But since the cell is not large enough to display the result, #### appears in B2.

 As you have read, many errors may occur while working with Excel. Some of which are cumbersome and can annoy users, but if you are a little familiar with the structure of Excel and have read the article carefully, you can easily fix Excel errors.

You can connect with us and ask our experts for your inquiries and get more Excel Support Service.

Reduce cost, accelerate tasks, and improve quality with Excel Automation Service.