Excel Formula Errors - Feature Image

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.

son i am disappoint

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.

Understanding Excel Formula Errors

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

Excel Formula Errors

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

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

NA Excel Formula Errors Example

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

NA Excel Formula Errors Example part 2

How to Fix

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")

NA Excel Formula Errors Example - using iferror to fix

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

##### Excel Formula Errors Example

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

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

You can multiply two numbers together like:

=10*5

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

=10*"Five"

VALUE Excel Formula Errors Example

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 col_index_num 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:

VALUE Excel Formula Errors Example part 2

How to Fix

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

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.

NAME Excel Formula Errors Example

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

NAME Excel Formula Errors Example Named Range

How to Fix

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:

NAME Excel Formula Errors Example Named Range how to fix

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

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.

REF Excel Formula Errors Example

How to Fix

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:

REF Excel Formula Errors Example How to Fix

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

REF Excel Formula Errors Example

How do you handle Excel Formula Errors?

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

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!