How to fix the #VALUE error in Excel formulas

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

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!

Follow this detailed tutorial on #value Excel and download this Excel workbook to practice along understand better:

 

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.

 

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.

 

HELPFUL RESOURCE:

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

You can learn more about how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables, Power Query, and Macros & VBA!