Free Practice Workbook#VALUE error in Excel is displayed when the variable provided in the formula is not a supported type or when the cell referred to in the formula is invalid. It is Excel’s way of telling the user that the right kind of argument is not provided. There can be several reasons why Excel displays this error and you need to find the exact reason for the specific error to fix it!
Key Takeaways
- #VALUE! Error Occurs Due to Incorrect Data Type – This error typically happens when a formula includes text in a calculation that requires numbers, such as adding a number to a text value.
- Check for Extra Spaces or Hidden Characters – Sometimes, extra spaces or non-printable characters in a cell can cause the #VALUE! error. Use the TRIM or CLEAN function to remove them.
- Verify Function Arguments – Some functions require specific argument types. For example, the DATE function requires numeric values, and entering text will result in a #VALUE! error.
- Ensure Cell References Are Correct – If a formula refers to a blank cell or a cell with text instead of a number, Excel may return a #VALUE! error. Double-check referenced cells for correct data.
- Use Error Handling Functions – To prevent disruption, use IFERROR or IFNA to replace the #VALUE! error with a custom message or alternative value. Example:
=IFERROR(A1+B1, "Check data!").
Table of Contents
What is #VALUE error in Excel?
When you enter unexpected data in a formula, it might display a #VALUE! error. This Excel error can occur because of one of the following reasons:
- Text is used in Arithmetic Operations; or
- Cell contains hidden spaces; or
- Date stored as text
Let’s consider different examples to examine each of these causes and learn how to fix them!
How to fix #VALUE error in Excel?
Example 1: Text is used in Arithmetic Operations
Here, we are trying to calculate the total sales amount by adding the sales achieved in both regions on different dates.
As you can see when cell D7 adds B7 and C7, it returns #VALUE! error. This is because cell B7 contains text instead of a number.
Let’s fix this!
Change the text nil to number 0.
You can also use the function SUM instead of using the addition operator (+). As formulas with operators will not calculate cells with text and instead display VALUE error.
If you use functions they will simply ignore text values and calculate everything else.
Example 2: Cell contains hidden spaces
Sometimes, VALUE error will be displayed when the cell contains hidden spaces. These spaces will look like the cell is blank but in fact, it contains a space instead.
In this example, you can see even though cell B7 looks like it is a blank, value is cell D7 is displaying an error. This is because cell B7 actually contains hidden space!
There can be numerous cells that may contain hidden spaces and it may be difficult to spot and remove them.
So, follow these steps below to find extra spaces and remove them!
STEP 1: Select the range that contains hidden spaces.
STEP 2: Press Ctrl + F to open the Find & Replace dialog box and select Replace tab.
STEP 3: Type in an extra space in Find what field and keep Replace field blank.
STEP 3: Press Replace All button. This is remove all the hidden spaces in the selected cells and leave them blank.
All the errors will now disappear!
Example 3: Date stored as text
In this example, we are trying to add duration to the start day of the project in order to get the project’s end date
Here, cells C7 and C11 are displaying #VALUE! error as Excel cannot recognize the value as a date. This is because date is separated using the decimal points as delimiters.
Let’s change the delimiter from decimal point (.) to hyphen (-)!
STEP 1: Select the cells containing dates.
STEP 2: Press Ctrl+H to open Find & Replace dialog box.
STEP 3: Type decimal point in Find What field and hyphen in Replace With field.
STEP 4: Press Replace All button.
This will replace decimal point with hyphen. Excel will now treat them as dates and make your formula work perfectly.
Frequently Asked Questions
What causes the #VALUE! error in Excel?
The #VALUE! error occurs when a formula includes an incorrect data type. For example, adding numbers to text, using non-numeric values in a mathematical operation, or providing the wrong argument type in a function can trigger this error.
How do I fix the #VALUE! error caused by extra spaces or hidden characters?
Use the TRIM function to remove extra spaces and the CLEAN function to remove non-printable characters. Example:
=TRIM(A1) or =CLEAN(A1).
Can incorrect cell references cause a #VALUE! error?
Yes, if a formula references a blank cell or a text value instead of a number, Excel may return a #VALUE! error. Check that all referenced cells contain the correct data type for the formula.
How can I prevent the #VALUE! error from affecting my calculations?
Use the IFERROR function to catch the error and replace it with a custom message or alternate value. Example:
=IFERROR(A1+B1, "Invalid data").
Does the #VALUE! error appear in all Excel functions?
No, but it commonly occurs in functions that expect numeric values, such as SUM, DATE, and VLOOKUP. Always ensure the function arguments are of the correct data type.
Conclusion
Value error in Excel occurs when the value provided in the formula is not the expected type. And, once you fix that value the error will disappear.
Click here to learn about the Top 20 Common Errors that you may encounter while working on Excel.
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
