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 **IFERROR**, **IFNA**, **ISERROR**, **ISERR**, 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.

## #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**.

## #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.

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 n**amed range**, n**amed 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.

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**.

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

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.

## #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.

The cell ranges **A4:A**8 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.

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_erro**r 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 |
---|---|

value | The value that is displayed if there is no error. This is generally some kind of function that is calculating a value. |

value_if_error | The 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.

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.**

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 I**FNA** function returns the original error message of the expression.

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

Parameter | Description |
---|---|

value | the value parameter contains the expression that is checked for the #N/A error. This parameter is required. |

value_if_error | the 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**.

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)**.

Parameter | Description |
---|---|

value | the 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.

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.**

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)**.

Parameter | Description |
---|---|

value | the 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.

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.

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.

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.

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)**.

Parameter | Description |
---|---|

value | the 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**.

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.

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.

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.

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