Don’t ignore Excel errors

It is a bad idea to leave standard Excel errors in your workbook. Take time to clean it up, because after a while, you won’t remember whether you left a mistake consciously or if there is something wrong with your spreadsheet.

Overview of Excel Errors

Let’s consider all the possible Excel errors one by one. What does the error message mean and what can you do to address the error?

#DIV/0!

#DIV/0! means that there is a division by zero. Division by zero is not possible in mathematics, so it isn’t possible in Excel either.

#NAME?

#NAME? appears when a formula refers to a named range, but Excel cannot find that range, usually because there is no named range with that name in the worksheet. You might have accidentally entered the wrong name, or somewhere in a formula there is a text that Excel doesn’t recognize as a formula. One of the common causes is when Excel is used in a different language than your default language. When you are accustomed to the English version and you write TRUE or FALSE in a formula, only the English version of Excel will recognize this. Other language versions will return a #NAME? error (in the corresponding language of course).

#NULL!

#NULL! appears when a space is used in a range instead of a divider in the form of a colon, comma or semicolon, for example =Sum(A1 A3). The error message #NULL! is fairly rare.

#NUM!

#NUM! Appears when an impossible calculation is made with a numerical value. For example, the root of -4 does not exist, so the formula SQRT(-4) results in #NUM!. Essentially mathematically impossible calculations.

#VALUE!

#VALUE! error occurs when an operation is performed on a cell type that is not suitable for that operation. For example, an addition of a range in which one of the cells contains a text. Maybe you know what “apple + 320.93” is but Excel doesn’t, so it gives you a #VALUE! error.

#REF!

#REF! means there is a non-existing reference. Maybe it used to exist, but it no longer does. Often it refers to a row or a column that has been removed, from which a cell was used in a formula. It can also concern a copied formula where the range that is used in the copied formula is impossible. For example, copy the formula in C1: =Sum(A1: B1) to B2: =Sum(#REF: A2). #REF errors often occur in poorly maintained spreadsheets. You also see them appear regularly within formulas in named ranges. They are hard to fix because it’s not always clear what the original intention was.

#SPILL!

#SPILL! errors occur in case of dynamic arrays. When a formula returns multiple results, but Excel can’t return them to the grid. For example, when the grid isn’t empty or contains merged cells.

#CALC!

Excel will display a #CALC! error when it runs in to a calculation that is not supported by its calculation engine. You can use Microsoft’s Evaluate Formula tool to identify the location of the #CALC! error within your nested formulas.

#N/A

#N/A is the only “error” that is not always a problem (there is a reason that there is no exclamation mark behind it). #N/A just stands for ‘Not Available’. It often occurs in search functions such as the VLOOKUP and MATCH. In addition to the general IFERROR function, Excel also has an IFNA function for error handling, specifically designed to be able to indicate how to act in case of a #N/A. This is because you cannot and do not always want to avoid searching in a range where the outcome does not exist, like in Excel models where the lookup values need to be filled in later.

Conclusion

If you encounter Excel errors, improve your spreadsheet. Excel errors generally indicate negligence and poor maintenance. It doesn’t look good when someone else encounters them in your spreadsheet. So just clean up your spreadsheets. PerfectXL will help you.