When you see the #VALUE error, it means that there is something wrong with the way the formula is typed, or there is something wrong with the cells you are referencing. The #VALUE error is very common in Microsoft Excel, so it’s kind of hard to find the exact cause for it.
How to fix the #VALUE error in Excel
Follow the solutions below to fix the #VAUE in Excel:
- An unexpected text value
- The input of special characters
- The function argument is not the expected type
- Dates stored as text.
1] An unexpected text value
One of the issues that may trigger the error is when you try to type the reference as =B2+B3+B4 with a text in the cost area by mistake.
To fix this issue, remove the text and add the cost price.
If you are using the Sum function, it will ignore the text and add the numbers of the two items in the spreadsheet.
2] The input of special characters
Inputting a special character into the data that you want to calculate can cause the #VALUE error.
To fix the issue, remove the space character.
3] The function argument is not the expected type
The error can occur when you input the wrong data that does not match with the function reference, for example, in the photo above the text, Orange does not go along with the YearFrac function. YearFrac is a function that returns a decimal value that represents the fractional years between two dates.
To fix this issue, enter the right data into the cell.
4] Dates stored as text
If dates are written in text, as seen in the photo above, you will see the Value error occur.
Enter the format correctly.
We hope this tutorial helps you understand how to fix the #Value error in Excel; if you have questions about the tutorial, let us know in the comments.
What is error in spreadsheet?
In Microsoft Excel, users will sometimes experience errors in Excel formulas. Errors usually occur when a reference becomes invalid (when there is an incorrect reference in your functions, or columns are rows are removed.)
How do you show errors in Excel?
If you have lots of data or a large table containing data on your spreadsheet and you want to find errors in it, follow the steps below.
- Click the Formulas tab.
- Click the Error Checking button in the Formula Auditing group and click Error Checking in the drop-down list.
- An Error Checking dialog box will appear.
- Click Next.
- Then, you will see the error in the spreadsheet.
Hope this helps.