It doesn’t matter how much we hate errors but they won’t leave us alone in case we make some mistake or when we don’t play by excel rules.
In this chapter we will discuss some common types of error in excel and how to handle them.
Types of Error
#NAME? Error
#NAME error occurs when we do some syntactical mistake or when excel doesn’t recognize some name that we provide e.g. if we use write COUT function instead of correct COUNT function to count the number of values, excel will throw #NAME error as shown below.
To fix #NAME error, just try to look the misspelled name and correct it to COUNT function.
As you could see, the #NAME error is resolved now when we have corrected the name of the function.
#DIV/0! Error
#DIV/0! Error- also known as division error in excel, which you get when you try to divide a value by 0 or by some blank cell.
Below you could see this error when we try to divide the value in cell A1 by B1 and the value in A2 by B2.
To fix division error, display blank value in place of error that we will see below in a bit.
#VALUE! Error
#VALUE! Error known as value error thrown by excel when we have data type mismatch in excel formula e.g. if we try to sum the text values in a range using + operator, we will get value error.
The exact reason for value error may not be defined accurately as several scenarios result in value error, but in most cases value error tells you that there is something wrong with the formula argument.
In the example above, I tried to divide a numeric value by a text value and hence value error as the argument is wrong for a division operation.
To fix that value error, check the arguments to the function or use error handling functions which we will see below in a bit.
#REF! Error
#REF! Error known as reference error that you get when the reference used in the formula is not valid e.g. cell reference is not valid; range reference is not valid etc.
When excel doesn’t find the cell that it looks for, it throws the reference error.
You could see in the figure above, cell A4 has the formula that is adding values in cell A1, A2 and A3.
If we delete a cell say A2, then excel formula ill try to look for cell A2 and it will throw reference error as the reference will become invalid in the formula.
To resolve #REF! Error, either delete the invalid reference from the formula or rewrite the entire formula with new cell references.
#N/A Error
#N/A error known as not available error in excel which is thrown by VLOOKUP function when it can’t find a value.
In the figure above , as you could see I have tried to look up the country’s capital city name from data table in E2 to F5 and VLOOKUP function has thrown #NA error as it was not able to find capital city of UK in the given data.
To fix #NA error in VLOOKUP we could use IFERROR function explained below in a bit.
##### Error
Actually it is not an error per se, but an error code that suggests that the column is not wide enough to display the value in its current format.
To see the value correctly just increase the width of the column by double clicking the column border as shown below.
After double clicking the column readjusts itself to accommodate all the values and display them properly.
Error Handling In Excel
IFERROR
To suppress error values like #N/A, #VALUE, #DIV error in excel, use IFERROR function to display blank value in place of error as shown below in an example.
Use the formula =IFERROR (A1/B1,””) to display an empty string in place of error.
We could use IFERROR function in case of any error and to display any value in place of an empty string.
I could have also displayed 0 in place of blank by writing the formula =IFERROR (A1/B1,”0″).
ISERROR
The output of ISERROR function is Boolean i.e. it returns TRUE or FALSE.
We will take the above example only to show how to use ISERROR function in excel.
Write the formula as =IF(ISERROR(A1/B1),””,A1/B1), now the ISERROR function returns the value TRUE or FALSE based on whether the result of the division operation returns error or not and based on that IF function displays the actual result or empty string.
ISNA
ISNA function is specific to #N/A error in excel which is thrown by VLOOKUP.
The output of ISNA function is Boolean i.e. it returns TRUE or FALSE.
We will see the same example which we saw while discussing #N/A error type earlier in this section.
To suppress #N/A value, we will write VLOOKUP function like
=IF(ISNA(VLOOKUP(A2,$E$2:$F$5,2,0)),””,VLOOKUP(A2,$E$2:$F$5,2,0))
In this formula , with the help of ISNA will return TRUE or FALSE based on whether the output of VLOOKUP is #N/A or not and IF function helps us in displaying empty string if there is an error or the actual value if it is available.
ISERR
The output of ISERR function is Boolean i.e. it returns TRUE or FALSE. ISERR function works with all type of excel error except #N/A error.
Write the ISERR formula as to display the empty string in place of error in excel.
The example is similar to what we have discussed earlier.
