Pinterest Pixel

How to fix the #VALUE error in Excel formulas

John Michaloudis
#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!

#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!").

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.

How to fix the #VALUE error in Excel formulas

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.

How to fix the #VALUE error in Excel formulas

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.

How to fix the #VALUE error in Excel formulas

 

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!

How to fix the #VALUE error in Excel formulas

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.

How to fix the #VALUE error in Excel formulas

STEP 2: Press Ctrl + F to open the Find & Replace dialog box and select Replace tab.

How to fix the #VALUE error in Excel formulas

STEP 3: Type in an extra space in Find what field and keep Replace field blank.

How to fix the #VALUE error in Excel formulas

STEP 3: Press Replace All button. This is remove all the hidden spaces in the selected cells and leave them blank.

How to fix the #VALUE error in Excel formulas

All the errors will now disappear!

How to fix the #VALUE error in Excel formulas

 

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.

How to fix the #VALUE error in Excel formulas

Let’s change the delimiter from decimal point (.) to hyphen (-)!

STEP 1: Select the cells containing dates.

How to fix the #VALUE error in Excel formulas

STEP 2: Press Ctrl+H to open Find & Replace dialog box.

How to fix the #VALUE error in Excel formulas

STEP 3: Type decimal point in Find What field and hyphen in Replace With field.

How to fix the #VALUE error in Excel formulas

STEP 4: Press Replace All button.

How to fix the #VALUE error in Excel formulas

This will replace decimal point with hyphen. Excel will now treat them as dates and make your formula work perfectly.

How to fix the #VALUE error in Excel formulas

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.

If you like this Excel tip, please share it


Founder & Chief Inspirational Officer

at

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.

See also  Microsoft Excel Online Course Private Access - 20+ Hours Beginner to Advanced Course

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...