How to Handle Excel Error Messages in Formulas

How to Handle Excel Error Messages Feature Image

Work with Excel formulas long enough, and you are going to bump into Excel error messages. They are annoying and can be time-consuming to deal with. However, there are things that you can do to handle the different types of Excel error messages you might receive. In fact, some of these error messages can be very important to the user of your spreadsheet.

In this blog post, we are going to look at the types of errors that can occur when writing formulas such as #DIV/0!#VALUE!#N/A#NAME?#REF!#NUM!, and #NULL!. We will also look at some of the functions you can use to handle errors such as the IFERRORIFNAISERRORISERR, and ISNA functions.

What Are Excel Error Messages

Excel error messages can be displayed for a multitude of reasons. They can be as a result of data that has been input into a cell that wasn’t in an expected format; for example, entering a text string instead of a number, deleting a cell that a formula in another cell uses, a typo when entering a formula, and so on. Whatever the reason, Excel is trying to do something that it cannot complete. Thankfully, rather than crashing, Excel returns an error message (most of the time).

When working with formulas, Excel has a few error types that are displayed for specific reasons. These reasons help us diagnose the problem and can help us decide how to handle that type of error.

Types of Errors

Let’s take a quick look at the different types of Excel error messages that you might come across.

#DIV/0!

The #DIV/0! Excel error message is displayed when a formula tries to divide a number by zero. It is worth noting that this also happens if a formula tries to divide a number by an empty cell. Excel gets just as confused as you would if you tried to divide a number by zero. For example, if you try to divide 10 by 0 you get the #DIV/0! error message.

#DIV/0! Excel Error Message

#VALUE!

The #VALUE! Excel error message is displayed when a formula includes different data types that are not compatible. Dividing a numeric value by a text string will return the #VALUE! error message. For example, you cannot really divide 7 by the letter T.

#VALUE! Example Error Message

#N/A

The #N/A! Excel error message is usually displayed when a formula is looking for a value that can’t be found. This is quite common in formulas that carry out some form of lookup, such as the HLOOKUP, VLOOKUP, LOOKUP, and MATCH functions. For example, the VLOOKUP function will return this error if it cannot find the lookup value.

Imagine we have a range of cells containing different types of fruit and their price. We use the VLOOKUP function to return the price of a piece of fruit input by the user. If the user inputs a piece of fruit that isn’t in our table, the VLOOKUP function returns the #N/A error, as shown in the image below.

#NA Excel Error Message

Excel has a couple of different functions specifically for dealing with the #N/A Excel error message that we will touch on later in this blog post.

#NAME?

The #NAME? Excel error message is displayed when the text in a formula is not recognized. One of the main reasons for the #NAME? error message is a typo in the formula. The formula thinks you are trying to reference a name, such as a named range, named constant, or another function.

For instance, if we typed the following formula into a worksheet:

=VLOOKUP(B1,DatasSet,2,0)

It will return the #NAME? error, as shown in the image below.

#NAME? Excel Error Message

This is because Excel thinks we have referenced a named range called DataSet. However, the named range DataSet has not yet been defined.

For more information on named ranges, please click here.

#REF!

The #REF! Excel error message is displayed when a reference of some kind, a cell reference for instance, isn’t valid. For example, it can occur with the VLOOKUP function if the col_index_num parameter is higher than the number of columns in the table_array.

Using the previous VLOOKUP example, this had two columns of data in the table_array.

VLOOKUP table_array

If we insert a formula into cell B2, which has the value 3 for col_index_num parameter.

#REF! Excel Error Message

The formula returns the #REF! error message.

This error can also occur when you delete rows, columns, or even a worksheet that is already referenced by a formula, so be certain to check before deleting any rows, columns or worksheets.

For more information on the VLOOKUP function, please click here.

#NUM!

The #NUM! Excel error message appears when a formula contains an invalid numeric value. It can also occur when Excel carries out a calculation that results in a number too high or too small for Excel to handle.

For example, the #NUM! error message will occur if you try to take the square root of a negative number as shown below.

#NUM! Excel Error Message

#NULL!

The #NULL! Excel error message occurs when a formula using the intersect operator specifies the intersection of two cell ranges that don’t actually intersect.

For example, the following formula will return the #NULL! Error message.

=SUM(A4:A8 C6:D6)

As shown in the image below.

#NULL! Excel Error Message

The cell ranges A4:A8 and C6:D6 do not actually intersect.

The intersection operator is a space between two cell ranges. For more information on the different type of operators, please click here.

Error ######

The ###### Excel error message is displayed when a column is not wide enough to display all the characters that are the result of a formula.

For example, the following formula calculates how many presents I want to get this Christmas 🙂

=SUM(A4:A8 C6:D6)

If we insert the following formula in cell B2, and the column width is only 22mm wide, it returns the ###### error message.

###### Excel Error Message

This error can easily be resolved by setting the column width to an appropriate size.

It’s worth thinking beforehand about the type of data and the typical length of the formula result that will appear in a cell.

Handling Errors

Excel has a few functions that can help you handle errors when they occur. They can basically be broken into two groups; the IF functions IFERROR and IFNA, or the IS functions, ISERROR, ISERR, and ISNA.

The IF functions take two parameters, a value parameter, and the value_if_error parameter. The value parameter is the expression that is evaluated for an error; if no error occurs, then the result of the expression is returned. If an error does occur, then control is handed over to the value_if_error parameter.

The IS functions have just one parameter, a value parameter. The expression in the value parameter is evaluated to see if an error has occurred. If an error occurs, then the IS functions return TRUE; otherwise, they return FALSE.

Let’s take a closer look at the functions.

IFERROR

The IFERROR function was introduced in Excel 2007. The IFERROR function can be used to handle all formula error types mentioned above. The IFERROR function allows you to capture an error message in a formula and return a value of your own choosing.

The syntax of the IFERROR function is IFERROR(value, value_if_error).

Parameter Description
valueThe value that is displayed if there is no error. This is generally some kind of function that is calculating a value.
value_if_errorThe value that should be used if an error occurs.

When we use the IFERROR function in a formula, we need to decide what to do if an error is returned.

For instance, we want a simple formula that calculates the monthly payments for a sales item. To work this out, we have two values, the sales price and the number of months. We could work this out using the following formula:

=A2/B2

Cell A2 contains the Sales Price and cell B2 the number of months. However, if we insert zero for the number of months we get the #DIV/0! Error message.

IFERROR function Example 1

To get around this, we can use the IFERROR function to display the error message, “Incorrect Value Entered.” When we’ve finished, our formula will look like this:

=IFERROR(A2/B2, “Incorrect Value Entered”)

The formula now returns the text string Incorrect Value Entered.

IFERROR Function Example 2

The IFERROR function needs to be used with some caution. For example, simply replacing an error message such as the #DIV/0! or #N/A error message with a blank cell, or a generic message might be masking a problem that the spreadsheet user needs to know about.

In the case of the #DIV/0! error message, replacing the error message with a zero might impact the precision of dependent formulas.

For the #N/A error message, Excel provides specific functions for managing it, which we will touch on next.

IFNA

The IFNA function is another useful function for handling errors. It is more specific than the IFERROR function and only tests to see whether an expression returns the #N/A error message. If it does return the #N/A error message, you can specify your own value to be returned instead. If the expression returns anything other than the #N/A error message, then the IFNA function returns the original error message of the expression.

The syntax of the IFNA function is IFNA (value, value_if_na)

Parameter Description
valuethe value parameter contains the expression that is checked for the #N/A error. This parameter is required.
value_if_errorthe value_if_error parameter contains the value that will be returned if the expression in the value parameter returns the #N/A error . This parameter is required.

One practical example of when the IFNA function is used is when working with the VLOOKUP function.

If a VLOOKUP up function can’t find the lookup value in the specified range of cells, then it returns the #N/A error.

In the following example, an Excel user can enter a type of fruit in cell B1. Cell B2 displays the price of the type of fruit entered into cell B1 and contains the following formula:

=IFNA(VLOOKUP(B1,A4:B7,2,0),”Fruit could not be found”)

In the example below, Grapefruit has been entered in to cell B1. Because Grapefruit isn’t in the list of fruits, the message, “Fruit could not be found” is displayed.

IFNA Example 2

 

Let’s break this formula down.

  • value – the value parameter of the IFNA function, is the expression containing the VLOOKUP function.
  • lookup_value – the lookup_value parameter of the VLOOKUP function is the value entered in cell B1.
  • table_array – the table_array parameter of the VLOOKUP function is the range of cells A4:B7.
  • col_index_num – the col_index_num parameter value of the VLOOKUP function is 2 (column B). This contains the price of each type of fruit in our table_array.
  • range_lookup – the range_lookup parameter of the VLOOKUP function is the value 0. Using the value 0 means we only want to return a value if we get an exact match to the lookup_value parameter entered in cell B1.
  • value_if_error – the value_if_error parameter of the IFNA function displays a message saying that the text string Fruit could not be found if the VLOOKUP function returns the error message #N/A.

ISERROR

The ISERROR function can be used to detect whether an expression returns an error. As already mentioned, it can return one of two values: TRUE if an error is detected or FALSE if no error is detected.

The ISERROR function doesn’t differentiate between the different error types, therefore, will return TRUE for all error types.

The syntax of the ISERROR function is ISERROR(value).

ParameterDescription
valuethe value parameter contains the expression that is checked for an error. It returns TRUE if an error is detected; otherwise, it returns FALSE

In our first ISERROR example, we are going to use the ISERROR function to detect an #DIV/0! error in cell A2 where we are trying to divide 10 by 0. To do this, we use the following formula:

=ISERROR(A2)

We simply insert the formula in cell B2 as shown below.

ISERROR Function Example 1

The formula returns the value TRUE. This can be useful if you want to make something happen based on the value returned by the ISERROR function.

Prior to the IFERROR function, the ISERROR function was used in combination with the IF function to achieve the same result as the IFERROR function. For example, if we add the IF function to our previous example, we get the following formula:

=IF(ISERROR(A2),”An Error Has Occurred”, “No Errors Detected”)

If we take our formula and insert it into cell B2, we now get the message “An Error Has Occurred”, which is probably more useful to a user than the text TRUE or FALSE.

ISERROR and IF Functions Example

Breaking this formula down

  • logical_test – the logical_test parameter of the IF function checks what value is returned by the ISERROR function. TRUE or FALSE
  • value – the value parameter of the ISERROR function detects if an error is returned by the formula in cell A2.
  • value_if_true‌ – the value_if_true parameter of the IF function displays the text An Error Has Occurred if the ISERROR function returns the value TRUE.
  • value_if_false – the value_if_false parameter of the IF function displays the text No Errors Detected if the ISERROR function returns the value FALSE.

The same result can be achieved using the IFERROR function. Perhaps, the major benefit of the ISERROR function now is that it can return the values TRUE or FALSE. These values can be used by other functions.

ISERR

The ISERR function works in much the same way as the ISERROR function; it detects if an error has occurred, with the exception of the #N/A error message. Again, it can return either TRUE or FALSE. The value is TRUE if an error is detected with the exception of the #N/A error message or FALSE if no error is detected or the #N/A error message is returned.

The syntax of the ISERR function is ISERR (value).

ParameterDescription
valuethe value parameter contains the expression that is checked for an error. It returns TRUE if an error is detected with the exception of the #N/A error; otherwise, it returns FALSE.

Like the previous ISERROR example, if we use the ISERR function to detect an #DIV/0! error in cell A2 where we are trying to divide 10 by 0, we can use the following formula:

=ISERR(A2)

We simply insert the formula in cell B2 as shown below.

ISERR Function Example 1

The formula returns the value TRUE. However, if we change our example to return a #N/A error message using the VLOOKUP function, we get a different result.

In the following example, an Excel user can enter a type of fruit in to cell B1. Cell B2 contains the following formula:

=ISERR(VLOOKUP(B1,A4:B7,2,0))

This formula uses an expression containing the VLOOKUP function to search the first column of the range of cells A4:B7 for the text entered by the user in to cell A2. If the VLOOKUP function can’t find a fruit that matches the text entered, it returns the #N/A error message. When we insert the ISERR function in front of the VLOOKUP function, the formula now returns the value TRUE or FALSE. TRUE if an error message is returned with the exception of the #N/A error, and FALSE if either no error is found, or the #N/A error is returned.

In the example below, Grapefruit has been entered in cell B1. Because Grapefruit isn’t in our list of Fruit, the value FALSE is returned. This is because the VLOOKUP function is returning the #N/A error.

ISERR Function Example 2

This isn’t really of much use to the user. However, like the ISERROR function, it can be used in conjunction with the IF function to do something a little more useful. If we add the IF function to our formula and add a messages to be displayed if an error is returned, we get the following formula:

=IF(ISERR(VLOOKUP(B1,A4:B7,2,0)), “Error Detected”,(VLOOKUP(B1,A4:B7,2,0)))

Now, if we insert this formula into cell B2 of our previous example and enter Grapefruit into cell B1, it will display the #N/A error.

ISERR Function Example 3

This is correct because Grapefruit isn’t on our list of fruits.

If we force our formula to return an error by changing the value in cell B4 to return the #DIV/0! error and enter the value Apple into cell B1, the formula will display our text string Error Detected, as shown below.

ISERR Function Example 4

Let’s break the formula down. The expression containing the VLOOKUP function is used twice in this formula, so I will explain it separately below instead of repeating it twice.

  • lookup_value – the lookup_value parameter is the value entered in cell B1.
  • table_array – the table_array parameter is the range of cells A4:B7 containing our list of fruits and price information.
  • col_index_num – the col_index_num parameter value is 2 (column B). This contains the price of each type of fruit in our table_array.
  • range_lookup – the range_lookup parameter is the value 0, which means we only want to return a value if we get an exact match to the lookup_value parameter entered in cell B1.

The IF and ISERR functions use the value returned by the VLOOKUP function as follows:

  • logical_test – the logical_test parameter of the IF function checks what value is returned by the ISERR function. TRUE or FALSE.
  • value – the value parameter of the ISERR function detects if an error is returned by the first expression containing the VLOOKUP function.
  • value_if_true – the IF function parameter value_if_true displays the text string Error Detected if the ISERR function returns the value TRUE.
  • value_if_false – the value_if_false parameter of the IF function displays the value returned by the second expression containing the VLOOKUP function if the ISERR function returns the value FALSE.

ISNA

The ISNA is our last IS function that we will discuss in this post. It is basically the opposite of the ISERR function. It detects if the #N/A error message has occurred. If the #N/A error message is detected, then it returns the value TRUE; otherwise, it returns the value FALSE.

The syntax of the ISNA function is ISNA(value).

ParameterDescription
valuethe value parameter contains the expression that is checked for the #N/A error message. It returns TRUE if the #N/A error is detected; otherwise, it returns FALSE.

Using our fruit example, if we want to detect if the #N/A error message has been returned by an expression we can use the following formula:

=ISNA(VLOOKUP(B1,A4:B7,2,0))

Like before we insert this formula in cell B2. Then, if we type Apple in cell B1 it returns the value FALSE.

ISNA Function Example 1

 

This is because the lookup_value, Apple, is in our list of fruit. However, if we type Grapefruit into cell B1 it returns the value TRUE, this is because Grapefruit isn’t in our list of fruit and therefore the VLOOKUP function returns the #N/A error message.

ISNA Function Example

Let’s break this formula down and look at what is happening:

  • value – the value parameter of the ISNA function detects if the VLOOKUP function returns the #N/A error message or not. If the VLOOKUP function returns the #N/A error message, then the ISNA function returns the value TRUE. However, if the VLOOKUP function returns any other error message or the actual price of a piece of fruit, the ISNA function returns the value FALSE.
  • lookup_value – the lookup_value parameter is the value entered in cell B1.
  • table_array – the table_array parameter is the range of cells A4:B7 used by the VLOOKUP function.
  • col_index_num –  the col_index_num parameter value is 2 (column B). This contains the price of each type of fruit in our table_array.
  • range_lookup – the range_lookup parameter is the value 0, which means we only want to return a value if we get an exact match to the lookup_value parameter entered in cell B1.

Like the other IS functions in this blog post, the result isn’t really of much use to the user. In fact, the original value returned by the VLOOKUP function would probably be of more use to the spreadsheet user than the values TRUE or FALSE. However, like the previous IS functions, it can be used in combination with the IF function to achieve something more useful.

If we modify our formula as follows:

=IF(ISNA(VLOOKUP(B1,A4:B7,2,0)),”Enter a Valid Fruit”, VLOOKUP(B1,A4:B7,2,0))

This formula will display the text string Enter a Valid Fruit if the #N/A error message is returned by the VLOOKUP function. If we insert the formula into cell B2 and enter the value Grapefruit into cell B1, then the text string Enter a Valid Fruit is displayed.

ISNA function Example 3

This is more useful to the spreadsheet user than the values TRUE or FALSE.

The formula works in much the same way as the previous ISERR example. Therefore, I won’t go over again how the two expressions containing the VLOOKUP function work.

The IF and ISNA functions use the value returned by the expressions containing the VLOOKUP function as follows:

  • logical_test – the logical_test parameter of the IF function checks what value is returned by the ISNA function. TRUE or FALSE.
  • value – the value parameter of the ISNA function detects if the #N/A error message is return by the first VLOOKUP expression.
  • value_if_true – the value_if_true parameter of the IF function displays the text string, Enter a Valid Fruit, if the ISNA function returns the value TRUE.
  • value_if_false – the value_if_false parameter of the IF function displays the value returned by the second expression containing the VLOOKUP function if the ISNA function returns the value FALSE.

The same result can be achieved using the IFNA function. Like the ISERROR function, perhaps the major benefit of the ISNA function is that it can return the values TRUE or FALSE. These values can be used by other functions.

Wrap-up

Ok, that was a long blog post, and you probably need to read it a couple of times. However, understanding and handling Excel error messages are things that will improve the quality of your spreadsheets and save you a lot of time.

In this post, we looked at the different types of Excel error messages that can occur, including the #DIV/0!, #VALUE!, #N/A, #NAME?, #REF!, #NUM!, and #NULL! error messages. We also looked at the IFERROR, IFNA, ISERROR, ISERR, and ISNA functions and how they can be used to handle error messages.

Whenever possible, I would stick to the IFNA and IFERROR functions. Remember to think about the possible error messages that might occur in your formulas and how best to handle them. There may be times when the user needs to know that an error has occurred. When you are replacing an error message with a default value, such as zero or one, think about how this might affect formulas in other cells that are dependent on the result of the formula in this cell.

That’s it for this post.  If you’d like a copy of the spreadsheet used in this post, please sign up for our newsletter.

For tools and resources to help you learn Excel, check out my resources page.

Share on FacebookTweet about this on TwitterShare on LinkedInPin on PinterestShare on Reddit