#VALUE! Error in Excel | MyExcelOnline

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

DOWNLOAD EXCEL WORKBOOK

 

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.

#value error in excel

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.

#VALUE! Error in Excel | MyExcelOnline

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.

#VALUE! Error in Excel | MyExcelOnline

 

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!

#value! error in excel

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.

#VALUE! Error in Excel | MyExcelOnline

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

#VALUE! Error in Excel | MyExcelOnline

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

#VALUE! Error in Excel | MyExcelOnline

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

#VALUE! Error in Excel | MyExcelOnline

All the errors will now disappear!

#VALUE! Error in Excel | MyExcelOnline

 

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.

#value error

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

STEP 1: Select the cells containing dates.

#VALUE! Error in Excel | MyExcelOnline

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

#VALUE! Error in Excel | MyExcelOnline

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

#VALUE! Error in Excel | MyExcelOnline

STEP 4: Press Replace All button.

#VALUE! Error in Excel | MyExcelOnline

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

#VALUE! Error in Excel | MyExcelOnline

 

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:

Microsoft Excel Training

You can learn more about how to use Excel by viewing our FREE Excel webinar training on FormulasPivot TablesPower Query, and Macros & VBA!

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn