#VALUE! Error In Excel – How To Fix

Excel doesn’t make mistakes, but you as a user, might. If you’ve entered a formula, and Excel can’t understand what one or more of the elements in the formula are trying to do, or if they are incorrectly entered, you’ll receive an error. The fix for each type of error is different.

In this article, we’ll walk you through how you can resolve the #VALUE! error, if you have one of those on your worksheet.

#VALUE! Error In Excel

What is #VALUE! Error in Excel?

A #VALUE! error, as the name suggests, results when you erroneously enter an incorrect value in an Excel formula. The value could either be explicitly entered as an argument in the formula or supplied as a cell reference. However, fixing it just requires correctly inserting the value that you’ve added erroneously.

If you have a #VALUE! error on your sheet, we’re going to help you get rid of it in a couple of minutes. But before that, let’s talk about what causes a #VALUE! error.

Why Does it Appear?

A #VALUE! error occurs when one of the values supplied isn’t the value that the formula was expecting. For instance, leaving a referenced cell blank when the formula expected a value, or referencing to a cell that contains text when the formula expected a numeric value could both give you a #VALUE! error.

You can solve most Excel errors swimmingly, but there’s a caveat with the #VALUE! error. The thing is, some Excel functions automatically ignore inconsistent values. For instance, say cell B2 and B3 have the values 2 and "ABC", respectively. When you simply use an operator to add these values [i.e., =B2+B3], you’ll get a #VALUE! error.

However, if you use the SUM function [=SUM(B2:B3)], it will simply ignore the text string and return 2.

Examples

Let’s walk through a few formulas that could give you a #VALUE! error, and ways to fix it.

1. Number + Text

Adding a number to a text string is going to make Excel unhappy. If you’ve added a number and a text string, you’ll see a #VALUE! error on your worksheet. Let’s build upon the example we used in the previous section.

#Value!-Error-In-Excel-Example-01

You have the number 2.49 in cell B2 and a text string "NA" in cell B3. You simply add an operator between cell references to get the total, like so:

=B2+B3

Naturally, you end up with a #VALUE error.

The fix here is simple. Just change the value in cell B3 to a number. For instance, changing the value in cell B3 to 3.99 solves the error, and the formula then returns 6.48.

#Value!-Error-In-Excel-Example-02

Alternatively, you could use the SUM function, like so:

=SUM(B2:B3)

#Value!-Error-In-Excel-Example-03

The SUM function will ignore any non-numeric values. Therefore, in this case, it will return 2.49.

2. Number + Space

This one can sometimes be tricky, especially if you have a large data set that has a lot of blank cells. If one of those cells has a space character, you’re going to pull your hair out finding that cell… unless you use a function like ISBLANK or LEN.

If your data set is indeed large with a lot of blank cells, just add a helper column next to it and apply the following formula to the entire column:

=ISBLANK(B2)

#Value!-Error-In-Excel-Example-04

The formula will return TRUE for all cells that are actually empty, and FALSE if there’s a space character in any of those cells. In the example, notice how even though cell B3 looks blank, the ISBLANK function tells us that it has some value, which in all likelihood, is a space character.

So, circling back to the #VALUE! error and how it relates to a space character, it works pretty much the same way as the previous example. Instead of a text string, you’re now adding a space character to a numeric value, which Excel doesn’t understand.

Simply adding an arithmetic operator to add these values will return a #VALUE! error. However, again, the SUM function will work just fine. Alternatively, you could just remove the space character to make the formula with the arithmetic operator work.

#Value!-Error-In-Excel-Example-05

3. Invalid argument value

If you’ve entered an inconsistent argument value in a formula, it will give you a #VALUE! error. For instance, say you’re trying to use the EOMONTH function. You have a column full of dates. So, you just apply the formula to the entire column, but a couple of them show #VALUE! errors.

#Value!-Error-In-Excel-Example-06

Your inner Sherlock wants to dig a little and see what’s amiss. You find that a few of those columns have the employee's last name instead of a date. Since the name of an employee is an incorrect argument to supply to the EOMONTH function, it returns a #VALUE! error.

Now, you don’t actually need to be Mr. Holmes to realize what will fix the problem. All you need to do is replace the employee’s name with a valid date.

#Value!-Error-In-Excel-Example-07

If it didn’t resolve the error, read through the next example.

4. Dates formatted as text

When you enter a date, and you receive a #VALUE! error referencing it in a formula, check the cell formatting. Let's take the last example and say we added a new employee to our list.

While trying to find the end of the first month you again run into a #VALUE! error.

#Value!-Error-In-Excel-Example-08

The Joining date in this case looks like a valid date. Then what's the problem?

The thing is, the dates have been imported from another file and are formatted as text. So the solution is simple – we just need to convert the text formatted date to an actual date that excel can recognize.

Now, there are a ton of ways you can convert text or numbers into dates, so look at our comprehensive tutorial on converting text and numbers to dates to correctly format your dates in a jiffy. Once you’ve done that, your #VALUE! error should disappear.

#Value!-Error-In-Excel-Example-09

Alright, that’s all the ways you can kick the #VALUE! error out of your worksheet and make room for a valid output. While you get up to speed with these techniques, we’ve got something in the oven for you. When you’re done, you know where to find us.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.

Speak Your Mind

*