How to Fix #VALUE! Excel Error?
#VALUE! Error Appears When The Formula You Are Using Has The Wrong Type Of Data.
The #VALUE! is also the most commonly occuring Excel Error. This issue occurs when an user provide the wrong type of input data while using Excel Functions or Excel Formula. Simply, Excel expected a number or proper value, but got something else (like text or an empty cell) which it could not understand for the calculation and so Excel returns the #VALUE! error in the cell.
This error is otherwise called as “data type mismatch” error. If you insert text instead of a number in a formula and the Excel will returns #VALUE! error. Excel is strict about using numbers when numbers are required.
| Error Name | Introduced Excel Version |
|---|---|
| #VALUE! | Excel 95 and Excel 97 |
What Causes #VALUE! Error in Excel?
The #VALUE! is a general error and it can occur with multiple functions and formula combinations but not with just a single factor. Some of the common causes are:
- Using TEXT instead of NUMERICAL inputs while using formula or functions.
- Trying to do math with dates or blank cells.
- Using incorrect arguments in functions.
- Using spaces (non-breaking spaces) that look empty but are not.
- Hidden characters in a cell (Occurs when copied from pdf or website directly.)
Real-time Example:
You can understand how this error works by looking at the real-time example below.
| Input 1 | Input 2 | Output Value | Formula Used |
|---|---|---|---|
| Apple | Orange | #VALUE! | =B5+C5 |
In the example above, the formula used is:
=B5+C5 // Returns #VALUE! Error because both are text valuesThe formula shows a #VALUE! error because the input values are text, not numbers. Since the formula tries to add both values, and addition is not possible with text values, Excel returns this error. Now, if you change the values from text value to numerical value it will return the added result value.
How to Prevent or Fix this #VALUE! Error?
- You must always enter correct data types in your cells.
- You should check for hidden spaces or characters in your cells.
- You can use data validation rules to restrict what users can type.
- Use “Paste as Values“, instead of directly copying the values from website or pdf to cells.
You can use the IFERROR function to show something else instead of the error. The updated formula used here is,
=IFERROR(B5 + C5, "Inputs are Non Numerical Values")
In the example shown, the IFERROR function does not show the usual Excel error. Instead, it displays a custom message in the output cell. This helps to hide error messages and show a user-friendly message when there’s an error in the input values.
Common Function/Formula Causing #VALUE! Error:
Here are the some common formula and function causing #VALUE! error while executing it.
While Using LEN Function:
The LEN Function is used to return the length of text of the chosen cell. If you use the LEN function in an invalid expression or formula, then Excel returns the #VALUE! error.
| Input 1 | Input 2 | Output Value | Formula Used |
|---|---|---|---|
| Apple | Orange | #VALUE! | Â =LEN(B5+C5) |
=LEN(B5+C5) //Returns #VALUE! error
To fix this issue, you can use the correct formula in the expression.
=LEN(B5)+LEN(C5) //Returns the value as 11.While Using DATEDIF Function:
DATEDIF Function is used to return days, months, or years between two dates. If the input values are not a valid Excel Date and then this function will return the #VALUE! error in the cell.
| Date 1 | Date 2 | Output Value | Formula Used |
|---|---|---|---|
| 01 January 2025 | Orange | #VALUE! | Â =DATEDIF(B5, C5, “d”) |
Executing the above sheet value in the Excel return the #VALUE! error. This is because the 2nd input value is a text value. DATEDIF function will expect the two input values as a Date values.
To fix this issue, the two input cell must contains a valid Excel date value. You can use the TODAY Function to print the current date in the second input cell “Date 2” and find the difference between the two dates using the DATEDIF function. You can refer to the below example image.
While Using FIND Function:
FIND Function is generally used to return the the position of one text string inside another text string value.
As shown in the example above, the first input returns a #VALUE! error because the FIND function tries to locate a text value in the second cell, which contains a number. Since the function cannot find text in a number, it returns an error. However, in the second and third rows, the inputs are valid, and the FIND function correctly returns the position of the text within the cell. This is how the FIND function works in Excel.
Some Other Causes with Functions:
| Functions Used | Cause of the #VALUE! Error! |
|---|---|
| LEFT Function RIGHT Function | Both function expects, numerical value does not text otherwise it will return the error. |
| TEXTJOIN Function | This function expects to use text as delimiter, like a comma or space. |
| SUMPRODUCT Function | This function expects a numerical value. |
| IF Function | This function expects both input value should be numerical or text value |
| SUBSTITUTE Function | This function needs a text string as the first argument. |
| NETWORKDAYS Function | Enter valid dates in both start and end date cells. Otherwise this error pops up. |
| TEXT Function | You must use valid numeric or date values as the first argument. |
Important Notes:
- The SUM function ignores text, but math operators like +, –, *, and / do not and so it returns #VALUE! Error.
- If you are working with dates, make sure to use a proper Excel Date Value.
- If you’re combining text using &, the #VALUE! error won’t appear, even if you mix numbers and words. For example:
="Hello"&"21"and the formula returns Hello21 as a output.
That’s it.
Reference Articles:
- How do I fix a value error in Excel? by Quora, Retrieved on 01/04/2025: Link.
- How to Fix #VALUE! Error in Microsoft Excel by HELPDESKGEEK, Retrieved on 01/04/2025: Link.
- How to correct a #VALUE! error by Microsoft Support, Retrieved on 01/04/2025: Link.
- How to correct a #VALUE! error by Microsoft Answers, Retrieved on 01/04/2025: Link.
- Worksheet suddenly giving me #VALUE error by Reddit Community, Retrieved on 01/04/2025: Link.
- How to Fix #VALUE! Errors in Excel by HowtoExcel.net, Retrieved on 01/04/2025: Link.
