Don’t neglect Excel errors
However tempting it may be, it is a bad idea to leave standard Excel errors in your workbook. Please take the time to clean up, because after a while, you no longer know 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? What can you do to address the error?
#DIV/0! means that there is a division by zero. Division by zero is not possible in mathematics, and isn’t possible in Excel either.
#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. 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 causes of this is when you use 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 divider in the form of a colon, comma or semicolon, for example =Sum(A1 A3). The error message #NULL! is fairly 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!. Essentially mathematically impossible calculations.
#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 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! means there is a non-existing reference, often one used to, but 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 range. They are hard to fix because you can’t always see what the original intention was.
#N/A is the only “error” that you can say is sometimes not bad. 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 values looked up need to be filled in later.
So, if you encounter Excel errors, improve your spreadsheet. Excel errors generally indicate carelessness and poor maintenance. It does not look good when someone else encounters them in your spreadsheet. So just clean up your spreadsheets, and you don’t have to do it alone, PerfectXL will help you.
- Avoid >70 types of risks
- Improvement suggestions
- Quality reports
Read more about risks in Excel files
On this page you will find a step-by-step walkthrough of the PerfectXL Risk Eliminator.
Here you will find information like system requirements, release notes, and answers to most questions about our software.
VLOOKUP and the combination of INDEX and MATCH are well known ways to search in Excel, and to our great frustration, VLOOKUP is used a lot more often.
Our software inspects spreadsheets on all kinds of spreadsheet risk. Read more about some of the risks we detect in spreadsheets here.
Excel horror stories are often related to accidentally changing, shifting or changing units or number formats.
Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, most people do not know about them, and do not understand them.
(Hidden) circular references occur when Excel tries to compute a result of a cell that’s already been visited during the calculation round. Excel doesn’t warn us of conditional circular references.
Whenever possible, keep your Excel formulas short and simple. Avoid long formulas; they are harder to read, harder to understand, difficult to check and tough to improve.
The use of fixed numbers is a bad idea. A future user will not know where the number came from, and fixed numbers don’t change automatically, and thus might be overlooked when a change is made.
Do not hide anything except some sheets. Excel has too many attractive options that look good but may be risky in the long run.
Sometimes, you need to use the exact same formula, based on the same numbers, in several other formulas. You might be tempted to calculate this formula twice, but resist the temptation. Calculate your formulas only once!
It is so tempting to merge cells in Excel so that they form a header above two or more columns. Yes, we must admit, it looks nice, but resist the temptation because it can be dangerous!
Place a formula close to its input variables. You reduce the chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else.
To be able to trust a spreadsheet, a thorough check for errors and mistakes is essential. There are lots of different ways a simple mistake can destroy the validity of a spreadsheet.
Reporting is an integral part of Excel. Reports are generated in Excel, reports are built in Excel, and in many cases reports are a form of documentation required to properly use certain models in Excel.
Good, error-free spreadsheets are essential for using them reliably in business. This is why risk detection is an important aspect of spreadsheet validation.
Do you wish to refine PerfectXL Risk Eliminator’s analysis to cater for your specific requirements? PerfectXL allows for full customization of your user interface.