Excel Formula Errors [Infographic]

Excel Formula Errors are a major pain. Picture this: you’re writing a long, complicated formula - including IFs, Index+Match, and Structured Refernces. You make sure you have all your parenthesis in check. Everything looks good. You press Enter and BAM! You’re met with a #VALUE! error.

You can practically feel how disappointed Excel is with you.

But don’t get too upset because in this post we’re going to look at the different Excel formula errors and how you can fix them.

Does this article help you? If so, please consider supporting me with a coffee ☕️

Understanding Excel Formula Errors

Here’s a fun infographic to help you remember what the most common formula errors mean:

Let’s go over these errors in depth and how to fix them.

The #N/A Error

You get this error when there is a function that can’t find the data that it’s told to look for.

Example #N/A Error

In this VLOOKUP example, when you tell VLOOKUP to find something that doesn’t exist in the data, you get the #N/A error.

This can also happen if the data was in the list, but then removed.

How to Fix the #N/A Error

If the data your lookup function is trying to find is not in the table, you can either:

• Add it back into the table
• OR maybe it’s OK to not have that data in the table in the first place

If you want to let your users know that the data is missing, you can make a simple message by wrapping your function around the IFERROR() function:

=IFERROR(VLOOKUP("Oranges",Fruits,1,FALSE),"Can't find Oranges")

The ##### Error

When a cell doesn’t have enough room to properly show a value, you get the ##### error. For example, if you have a date and the column is too short to show the full date, Excel will fill the cell with #####.

Example ##### Error

You may be wondering, “why not just cut off the text?”

If you told Excel to format the cell to show the full date (including the year), and Excel couldn’t do that because the cell wasn’t large enough, you probably don’t want Excel to shortchange you by cutting off the year.

How to Fix the ##### Error

Simply adjust the column size to fit and you’re good to go.

The #VALUE! Error

This error comes up whenever you have given a function an incorrect argument.

Example #VALUE! Error

You can multiply two numbers together like:

=10*5

But you can’t multiply a number with text, like:

=10*"Five"

The two data types (integer and string) are incompatible for the operation (multiplication).

You can also get the #VALUE! error when you give an Excel Function an incorrect argument. This could mean that the argument is the wrong data type, or that your value for the argument is “out of bounds” (i.e. out of the range that the argument is willing to accept).

For example, the VLOOKUP() function expects the following arguments:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup = TRUE])

The colindexnum argument expects a number greater than 0, since a column number starts at 1 in Excel. If you provide a number less than 1, you have given the VLOOKUP() function an incorrect argument value:

How to Fix the #VALUE! Error

There are several ways to fix this error, depending on which function you’re using.

In general, here are some quick tips to figure out where the issue is:

• If you are using multiple functions within one formula, use the Evaluate Formula tool to figure out which function is returning the #VALUE! error
• Look at the data you are sending to the function that is causing the error. Are you giving the function bad data in one of the arguments?
• Click here for help with the #VALUE! error for a specific formula

The #NAME? Error

The #NAME? error is shown when you accidentally make a typo in the function name you are trying to use.

Think of it like Excel saying “I don’t know which function name you’re talking about.”

Example #NAME? Error

In this example I messed up tying the IF function’s name. I also made up a function called ADD. Both of these will result in the #NAME? error.

This error can also come up with you have a Named Range that was deleted while there was a reference to it.

How to Fix the #NAME? Error

First, check if you misspelled a function name; that would be the easiest thing to check. Once you find the culprit, fix the spelling and you’re good to go.

If you have the Named Range problem, simply check the formula and compare it to the Name Manager. If you like, you can re-add the Named Range like I show you here:

The #REF! Error

The most common time that this error comes up is when you delete cells or worksheets. If you have a formula that references a cell that ends up getting deleted, you’ll get the #REF! error.

Example #REF! Error

In this example, I show how a cell references another. Once that referenced cell is deleted, the cell that was referring to it gets the #REF! error.

How to Fix the #REF! Error

This can be pretty difficult to fix. If it happened soon enough like in this example, then you can hit Ctrl+z to undo it. If not, you may be in for some re-writing of your formulas.

Another way to prevent this from happening is if you use a range in a function like I show here:

However, if the whole range that this function is using is deleted, you’ll get the #REF! error again:

How do you handle Excel Formula Errors?

Join the conversation in the comments below and let us know!