Pinterest Pixel

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... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

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:

See also  Master Excel: How to Use VLOOKUP in Excel - Quick Guide

download excel workbook Value-Error.xlsx

 

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!

See also  DAYS Formula in Excel

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.

See also  Excel INDIRECT Function Using Sheet References

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

 

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

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

See also  Excel INDIRECT Function Using Sheet References
Founder & Chief Inspirational Officer at MyExcelOnline.com

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 Academy Online Course.

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!