How to Fix #Value Error in Excel (Step by Step) (2023)

If you are an Excel user (even if you are a newbie), you must have encountered Excel #VALUE error ⚡

It looks very tasteless and disturbs the entire leaf.

When does the #VALUE error occur and how to fix it in Excel? If you are looking for answers to these questions, you have come to the right place ✔

Keep reading the guide below to know everything about the #VALUE error in Excel.

And as you scroll downDownload our sample workbook hereto mark along with the guide.

What causes the #VALUE error

The first step in solving a problem is diagnosing the problem. So what causes the #VALUE error?

The #VALUE error tells Excel that something is wrong:

• With how the formula is entered.
• or with thatcell reference

The exact reasons why the #VALUE error occurs depends on the formula used 🔍 However, here are some general reasons why the #VALUE error can occur:

wrong data type

If you specify the wrong data type for a particular formula, you are likely to get a #VALUE error.

For example here:

Excel can certainly summarize numbers, but not text. Because the list contains the text "ten", the formula to sum the cells returns a #VALUE error.

If we had used the SUM function here instead of operators, the result would have been different. Because? We will see that soon 🤔

Incorrect cell references:

This usually happens when you drag and drop the formula into an entire list. See that:

The first formula is to subtract two cells. And the result is correct 🎯

However, if you drag and drop the same formula to all cells, the response will be a #VALUE error.

This is because one of the cells in the second formula does not have the correct value.

Cell B2 contains a hyphen (-). And Excel certainly can't subtract a hyphen (-) from 10, so we get the #VALUE error.

Incorrect function arguments

Every time you end up with a #VALUE error, recheck the precision of your function. More precisely, check logically.

For example, Excel's FIND function finds the position of a character in a text string.

The formula FIND("a", "dark") gives the result 2. Because the substring "a" comes second in the word "dark" 2️⃣

Now try writing this formula like this:

=FIND("y", "dark")

And Excel returns a #VALUE error.

This is because the word "dark" simply does not contain the "j" character, let alone its position within it.

How to fix #VALUE error

We know the most common problems that can cause the #VALUE error. Now it's time to find solutions to these problems.

To correct the #VALUE error try the following tricks 👇

Find special characters:

Make sure your data is not numbers, text, or other characters. They can be spaces or other special characters.

You cannot distinguish between spaces and empty cells in Excel. But you can use the ISTEXT function to identify them. See below:

Cell A1 appears to be empty, but it contains a space. ISTEXT function returns TRUE (Yes, this cell contains a value) 👀

Once you have identified the cells that contain spaces, delete them. So try running your formulas and you'll probably avoid the #VALUE error in your worksheet.

Use Excel functions instead of operators

Use Excel's built-in functions instead of writing formulas with operators. Functions in Excel are generally designed to ignore all non-text values.

Look here:

Here cell B2 consists of a hyphen (-) and is not empty.

Now write the following formula:

= A2*B2

We got a #VALUE error because Excel can't multiply 25 with a hyphen (-) 🙅‍♀️

Now write the PRODUCT function for it:

= PRODUCTO(A2,B2)

The PRODUCT function returns 25, but no #VALUE errors.

Check the format of your data.

For example, in the image below, we apply the WEEKDAY function, which returns the day for any date.

But the function returns a #VALUE error. This is because cell A1 contains the date in a generic format. And this is not a valid date for the WEEKDAY function.

Change the format to date.

Hurrah! No more #VALUE errors 🏆

Replace error #VALUE

Sometimes you know that a #VALUE error will occur. And the problem is not that Excel returns an error.

The problem is that #VALUE looks weird on your spreadsheet.

If that's the case, just replace it with the IFERROR function. For example, the following function returns a #VALUE error.

Just wrap it in the IFERROR function like this 🎁

= SI ERROR ((A1+A2+A3), „-“)

Evaluate the formula

This is the last resort, if you have tried everything but still can't find the cause of the error.

In that case, let Excel evaluate the formula and find the cause.

Select your formula (which returns a #VALUE error). And go toFormulas tab > Evaluate Formula.

Excel would evaluate the formula and tell you what is causing the error.

For example, Excel says that 30 and 90 equal 120. But in the next step, it's the text that's causing the error here.

Examples of formulas that return the #VALUE error

In all honesty, the list of formulas that return the #VALUE error may be too long to include here.

This is because most Excel formulas (that are not aligned or structured correctly) return the #VALUE error.

So, let's look at some examples of these formulas:

Example #1: SUMPRODUCT function

OSUMPRODUCTThe function multiplies two (or more) matrices together. And then add your product.

Let's write it like this:

= SUMMENPRODUKT (A2:A4, B2:B3)

And guess what! We end up with a #VALUE error.

Because? Hard to understand, but we made a very logical mistake. The first area has three cells (A2 to A4).

While the second line only has two (B2 and B3) 😵

Excel multiplies cell A2 by B2, cell A3 by B3, and there is no other cell for cell A4. This causes the #VALUE error.

Add another cell to the second range (make B2:B4 out of B2:B3). And apply the SUMPRODUCT function again:

= SUMMENPRODUKT (A2:A4, B2:B4)

Excel now has all the cells to apply the SUMPRODUCT function to. And even though cell B4 is empty, the SUMPRODUCT function does not return the #VALUE error ✌

Example #2: DATE function

Did you know that Excel treats every date as a serial number?

Where 1/1/1900 is 1, 2/1/1900 is 2, 3/1/1900 is 3, and so on.

So what number is 05/30/2022? Don't keep counting by hand, it will take you hours or maybe days to find the answer ⏳

Instead, write the DATE function as follows:

= DADOS (C2, B2, A2)

Cell A2 contains the date, B2 the month, and C2 the year.

But Excel returns a #VALUE error.

This is because the month is in "text" format. Excel only recognizes it in numbers.

Change the month to numeric format and now apply the DATE function again:

= DADOS (C2, B2, A2)

That's it, now what

We had a very detailed discussion of Excel's #VALUE error in the previous guide.

From diagnosing the problem causing the #VALUE error to resolving it, we've seen it all.

Let's not forget the list of functions that return the #VALUE error if applied incorrectly. You might find this list short but let me burst your bubble 💭

The Excel function library is very extensive. And if you want to learn this already, I suggest you start with the VLOOKUP, SUMIF, and SE functions.

These are some basic but very useful features of Excel. To get your hands on themSign up for my free 30 minute email course today!

Another resources

A smarter approach is to identify incorrect formulas in advance by using the ISERROR function.

Or you can identify the error values ​​and replace them with any value of your choice using the IFERROR function.

Learnhow to use Excel's IFERROR and IFERROR functions here.