Often times in a data set you'll have to handle with errors that can wreck your data, especially if you need to do any analysis on it. There are several ways that you can handle errors so that they don't show up in your data.
IFERROR
The first method is by using the IFERROR Function, which allows you to easily replace the error with whatever you want in its place. If you want a numerical value, you may want to put in a 0, otherwise you can just leave it blank.
I've purposely added various types of errors to my data set:
Here is one of the formulas that's causing an error:
=VLOOKUP(D7,L:L,1,FALSE)
In the above example, I could use the formula =IFERROR(VLOOKUP(D7,L:L,1,FALSE),"") to replace the error with a blank. I could also put a 0 in its place instead of the "".
ISERROR
In older versions of Excel (2003 and earlier), the Iferror Function is not available. However, what you can use is a combination of the IF and ISERROR functions. To recreate the same formula as above, we can use the following:
=IF(ISERROR(VLOOKUP(D7,L:L,1,FALSE)),"",VLOOKUP(D7,L:L,1,FALSE))
The disadvantage of this method is you have to repeat your original argument. First, you are checking if the value is an error, if it isn't, then you have to repeat the formula again to save the value. It's not terribly efficient, and likely why we saw the IFERROR function introduced in newer versions of Excel.
Using the IFERROR or IF(ISERROR()) functions can be useful for eliminating errors, but sometimes it may not be helpful for dealing with specific ones. For example, if your cell is blank or it has an error and is made to look blank, you won't be able to tell the difference just by looking at it. The danger is that you may assume it's a different type of error.
ISNA
What you can also use is the ISNA() function, which can tell you if the cell returns the #N/A error. This way you can trap this error specifically, rather than everything that can be captured by the ISERROR() function.
Getting Rid of Zeros
If you've used a lot of error-handling functions and replace your errors with zeros, you could up with a lot of zero values on your spreadsheet:
The problem if you have a lot of zeros on your spreadsheet, is it can sometimes be a distraction away from what you really want to see - the non-zero values. There are two ways you can get rid of the eyesore:
1. Change the format to Accounting. Doing this will remove the zero values and replace them with a dash, which makes it a bit easier to skip over when doing a review:
However, you may not want to use the Accounting format, and that leads me to the other option:
2. Conditional Formatting. Refer to this post on how to setup rules for this. What I normally do in these cases is set the zero value cells to a light gray color font so that they do not attract your attention:
Common Types of Errors
Here'a list of some of the common types of errors you'll find in Excel:
#REF: This is an error that you'll incur if your range doesn't go far enough and the error relates to your reference. For example, consider a VLOOKUP formula that extracts from column number five but you only specified a range that had four columns, that would result in a REF# error.
#N/A: You'll get this error if your VLOOKUP or MATCH formula is correct, but the value you're looking up isn't found, and hence, not available. However, there's other contexts it can apply to, and it just means that it wasn't able to find the value you were looking for.
#VALUE: This error normally shows up when there is an issue with your actual calculation. For instance, if you're trying to multiply a number by a field that has text.
These are just a few examples of the errors that you'll encounter, but these are also likely the most common that you'll come across.
