How to fix the #VALUE error
We know the common problems that may potentially cause the #VALUE error. Now is the time to seek the solution to these problems.
To fix the #VALUE error, try out the hacks below 👇
Check for any special characters:
Check out your data for any non-numbers or texts, or other characters. These might be space characters or other special characters.
You cannot differentiate between space characters and blank cells in Excel. But you can use the ISTEXT function to identify them. See below:
Cell A1 is apparently empty but contains a space character. The ISTEXT function gives the result TRUE (Yes, this cell contains some value) 👀
Once you have identified the cells that contain space characters, clear them out. Then try running your formulas and you’ll likely steer clear of the #VALUE error from your spreadsheet.
Use Excel functions instead of operators
Use in-built functions of Excel instead of writing formulas using operators. Functions in Excel are usually developed to ignore any non-text value.
Check out here:
Cell B2 here consists of a dash (-) and is not empty.
Now write the following formula:
= A2 * B2
We have a #VALUE error as Excel cannot multiply 25 with a dash (-) 🙅♀️
Now write the PRODUCT function for the same:
The PRODUCT function returns 25 but not a #VALUE error.
Double-check the format of your data.
For example, in the image below, we have applied the WEEKDAY function that returns the day for any given date.
But the function gives a #VALUE error. This is because Cell A1 contains the date in a general format. And that is not a valid date for the WEEKDAY function.
Change the format to date.
And now retry applying the WEEKDAY function:
Yay! No more #VALUE errors 🏆
Replace the #VALUE error
Sometimes, you know a #VALUE error would occur. And the problem is not with Excel returning an error.
The problem is with the #VALUE making a distasteful appearance on your worksheet.
If that’s the case, you can simply replace it using the IFERROR function. For example, the function below returns a #VALUE error.
Simply wrap it in the IFERROR function as follows 🎁
= IFERROR ((A1+A2+A3), “-“)
The IFERROR function will replace #VALUE with a dash (-). You can specify and value instead of a dash, as desired.
Alternatively, you can replace the #VALUE error using the Find and Replace feature.
Press the Control + H key to launch the Find and Replace dialog box. And replace the word #VALUE with any other desired value 🔁
Evaluate the Formula
This is the last resort – when you have tried everything else but have still not found the cause of the error.
In such a case, let Excel evaluate the formula and spot the cause.
Select your formula (that returns a #VALUE error). And go to the Formulas Tab > Evaluate Formula.
Excel would evaluate the formula and tell you what’s causing the error.
For example, Excel tells that 30 and 90 are added up to make 120. But in the next step, it’s the text causing the error here.
Remove the cause of the error as identified by Excel. And rerun the formula to see if it works.