Don’t Neglect Excel Errors
However tempting it may be, it is a bad idea to leave standard Excel errors in your workbook. Because after a while, you no longer know whether you left a mistake consciously or whether there is something wrong with your spreadsheet.
Overview Excel Error Messages
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! means a division by zero. Division by zero is not possible in mathematics, so it isn’t in Excel either.
#NAME? appears when a formula refers to a named range while there is no named range with that name in the worksheet. This might be because you accidentally entered the wrong name, or because somewhere in a formula a text is entered that Excel does not recognize as a formula. One of the common problems occur when using Excel in another language than you are used to. Imagine you are accustomed to the English version and write TRUE or FALSE. Only the English version of Excel recognizes this. Different language versions give a #NAME? Error (but then of course in the corresponding language).
#NULL! appears when a space is used in a range instead of a connection in the form of a colon, comma or semicolon (or in an intersection between two ranges without intersection). For example Sum (A1 A3). The error message #NULL! is very rare.
#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!
#VALUE! error occurs when an operation is performed on a cell type that is not suitable for that operation at all. For example, an addition of a range of which one of the cells contains a text.
#REF! means a non existing reference, often one that no longer exists. Often this refers to a row or a column that has been removed, where a cell from that column was used in a formula. It can also be a copied formula where the range 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 a formula in a named ranges. They are hard to fix because you can’t see what the original intention was.
#N/A is the only “error” that you can say it is sometimes not bad if it remains in the spreadsheet. There is a reason that there is no exclamation mark behind it. #N/A 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 because you cannot and do not always want to avoid searching in a range where the outcome does not exist, for example in template spreadsheets that will later be filled with data.
So, if you encounter Excel errors by default, improve your spreadsheet. Excel errors indicate carelessness and poor maintenance. It does not look good if someone else encounters them in your spreadsheet.
Opinions differ on the use of IFERROR to deal with errors. Purists find the circumvention of problems, but we believe that the advantages outweigh the disadvantages. It is efficient and you show future users what you are doing.
How PerfectXL Can Help You
What does PerfectXL do? Of course, PerfectXL automatically reports Excel Errors so you can easily address them one by one.