Type Of Errors And Error Handling In Excel

You are here:
< Back

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.

#NAME error in excel

To fix #NAME error, just try to look the misspelled name and correct it to COUNT function.

fixing #NAME error in excel

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.

#DIV ERROR IN EXCEL

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.

#VALUE error in excel

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.

#REF error in excel

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.

fixing #REF error in excel

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.

#NA error in excel

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.

#### error in excel

To see the value correctly just increase the width of the column by double clicking the column border as shown below.

fixing #### in excel

After double clicking the column readjusts itself to accommodate all the values and display them properly.

#### resolved in excel

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.

explaining IFERROR function in excel

Use the formula =IFERROR (A1/B1,””) to display an empty string in place of error.

using IFERROR function to suppress error in excel

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.

Explaining ISERROR function in excel

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.

ISNA function in excel

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.

handling error with ISNA function in excel

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.

ISERR function in excel

The example is similar to what we have discussed earlier.

Random Posts

  • Area Function In Excel

    Area function is a very significant function when it comes to determine how many references have been selected in the […]

    Share The Knowledge
  • Sum Numbers Separated By Symbol In Excel

    sum numerical values separated by symbol in excel, excel formula to sum numerical values separated by symbol in excel, excel formula to remove symbol and calculate sum, sum numerical values separated by @ in excel,
    sum numerical values separated by comma in excel

    Share The Knowledge
  • Find Some Values And Make Them Bold in Excel

    find some values inn excel and make them bold, how to make some values bold in excel, vba to find some values and make them bold, make a list of values bold in excel

    Share The Knowledge
  • Get Previous Month From A Date In Excel

    We will see how we could find the previous month from a date in excel. We will see a simple […]

    Share The Knowledge