If you are an Excel user (even if you are a newbie), you must have encountered Excel #VALUE error ⚡
It looks very tasteless and disturbs the entire leaf.
When does the #VALUE error occur and how to fix it in Excel? If you are looking for answers to these questions, you have come to the right place ✔
Keep reading the guide below to know everything about the #VALUE error in Excel.
And as you scroll downDownload our sample workbook hereto mark along with the guide.
What causes the #value error
How to fix the #value error
Examples of formulas that return the #value error
What causes the #VALUE error
The first step in solving a problem is diagnosing the problem. So what causes the #VALUE error?
The #VALUE error tells Excel that something is wrong:
- With how the formula is entered.
- or with thatcell reference
The exact reasons why the #VALUE error occurs depends on the formula used 🔍 However, here are some general reasons why the #VALUE error can occur:
wrong data type
If you specify the wrong data type for a particular formula, you are likely to get a #VALUE error.
For example here:
Excel can certainly summarize numbers, but not text. Because the list contains the text "ten", the formula to sum the cells returns a #VALUE error.
If we had used the SUM function here instead of operators, the result would have been different. Because? We will see that soon 🤔
Incorrect cell references:
This usually happens when you drag and drop the formula into an entire list. See that:
The first formula is to subtract two cells. And the result is correct 🎯
However, if you drag and drop the same formula to all cells, the response will be a #VALUE error.
This is because one of the cells in the second formula does not have the correct value.
Cell B2 contains a hyphen (-). And Excel certainly can't subtract a hyphen (-) from 10, so we get the #VALUE error.
Incorrect function arguments
Every time you end up with a #VALUE error, recheck the precision of your function. More precisely, check logically.
For example, Excel's FIND function finds the position of a character in a text string.
The formula FIND("a", "dark") gives the result 2. Because the substring "a" comes second in the word "dark" 2️⃣
Now try writing this formula like this:
And Excel returns a #VALUE error.
This is because the word "dark" simply does not contain the "j" character, let alone its position within it.
How to fix #VALUE error
We know the most common problems that can cause the #VALUE error. Now it's time to find solutions to these problems.
To correct the #VALUE error try the following tricks 👇
Find special characters:
Make sure your data is not numbers, text, or other characters. They can be spaces or other special characters.
You cannot distinguish between spaces and empty cells in Excel. But you can use the ISTEXT function to identify them. See below:
Cell A1 appears to be empty, but it contains a space. ISTEXT function returns TRUE (Yes, this cell contains a value) 👀
Once you have identified the cells that contain spaces, delete them. So try running your formulas and you'll probably avoid the #VALUE error in your worksheet.
Use Excel functions instead of operators
Use Excel's built-in functions instead of writing formulas with operators. Functions in Excel are generally designed to ignore all non-text values.
Here cell B2 consists of a hyphen (-) and is not empty.
Now write the following formula:
We got a #VALUE error because Excel can't multiply 25 with a hyphen (-) 🙅♀️
Now write the PRODUCT function for it:
The PRODUCT function returns 25, but no #VALUE errors.
Check the format of your data.
For example, in the image below, we apply the WEEKDAY function, which returns the day for any date.
But the function returns a #VALUE error. This is because cell A1 contains the date in a generic format. And this is not a valid date for the WEEKDAY function.
Change the format to date.
Now try applying the WEEKDAY function again:
Hurrah! No more #VALUE errors 🏆
Replace error #VALUE
Sometimes you know that a #VALUE error will occur. And the problem is not that Excel returns an error.
The problem is that #VALUE looks weird on your spreadsheet.
If that's the case, just replace it with the IFERROR function. For example, the following function returns a #VALUE error.
Just wrap it in the IFERROR function like this 🎁
= SI ERROR ((A1+A2+A3), „-“)
The IFERROR function replaces #VALUE with a hyphen (-). You can also specify a value instead of a hyphen if necessary.
Alternatively, you can use the find and replace function to replace the #VALUE error.
squeezeControl + H I liketo open the Find and Replace dialog box. And replace the word #VALUE with any other value you like 🔁
kasper langmann,Microsoft Office Specialist
Evaluate the formula
This is the last resort, if you have tried everything but still can't find the cause of the error.
In that case, let Excel evaluate the formula and find the cause.
Select your formula (which returns a #VALUE error). And go toFormulas tab > Evaluate Formula.
Excel would evaluate the formula and tell you what is causing the error.
For example, Excel says that 30 and 90 equal 120. But in the next step, it's the text that's causing the error here.
Fix the cause of the error identified by Excel. And run the formula again to see if it works.
Examples of formulas that return the #VALUE error
In all honesty, the list of formulas that return the #VALUE error may be too long to include here.
This is because most Excel formulas (that are not aligned or structured correctly) return the #VALUE error.
So, let's look at some examples of these formulas:
Example #1: SUMPRODUCT function
OSUMPRODUCTThe function multiplies two (or more) matrices together. And then add your product.
Let's write it like this:
= SUMMENPRODUKT (A2:A4, B2:B3)
And guess what! We end up with a #VALUE error.
Because? Hard to understand, but we made a very logical mistake. The first area has three cells (A2 to A4).
While the second line only has two (B2 and B3) 😵
Excel multiplies cell A2 by B2, cell A3 by B3, and there is no other cell for cell A4. This causes the #VALUE error.
Add another cell to the second range (make B2:B4 out of B2:B3). And apply the SUMPRODUCT function again:
= SUMMENPRODUKT (A2:A4, B2:B4)
Excel now has all the cells to apply the SUMPRODUCT function to. And even though cell B4 is empty, the SUMPRODUCT function does not return the #VALUE error ✌
Example #2: DATE function
Did you know that Excel treats every date as a serial number?
Where 1/1/1900 is 1, 2/1/1900 is 2, 3/1/1900 is 3, and so on.
So what number is 05/30/2022? Don't keep counting by hand, it will take you hours or maybe days to find the answer ⏳
Instead, write the DATE function as follows:
= DADOS (C2, B2, A2)
Cell A2 contains the date, B2 the month, and C2 the year.
But Excel returns a #VALUE error.
This is because the month is in "text" format. Excel only recognizes it in numbers.
Change the month to numeric format and now apply the DATE function again:
= DADOS (C2, B2, A2)
May 30, 2022 is under serial number 44711 🚀
That's it, now what
We had a very detailed discussion of Excel's #VALUE error in the previous guide.
From diagnosing the problem causing the #VALUE error to resolving it, we've seen it all.
Let's not forget the list of functions that return the #VALUE error if applied incorrectly. You might find this list short but let me burst your bubble 💭
The Excel function library is very extensive. And if you want to learn this already, I suggest you start with the VLOOKUP, SUMIF, and SE functions.
These are some basic but very useful features of Excel. To get your hands on themSign up for my free 30 minute email course today!
A smarter approach is to identify incorrect formulas in advance by using the ISERROR function.
Or you can identify the error values and replace them with any value of your choice using the IFERROR function.
Learnhow to use Excel's IFERROR and IFERROR functions here.
How to avoid #VALUE error in Excel?
Try the following to avoid the #VALUE error in Excel:
- Check the text, special characters or spaces in the relevant cells using the ISTEXT function.
- Check your data for strings other than numbers or text strings.
- Use Excel built-in functions instead of operators.
What is the #VALUE error in Excel VLOOKUP?
This is an indication that the VLOOKUP function is incorrect. This could have the following two reasons:
- The lookup value has at least 255 characters. Shorten it or use the INDEX and MATCH functions.
- The col_index argument is set to zero or is text.