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.
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.
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
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 #####.
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.
You can multiply two numbers together like:
But you can’t multiply a number with text, like:
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:
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.”
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
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.
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
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!