#VALUE! Error in Excel (Understanding and Fixing)

In Excel, the #VALUE! Error occurs when the type of the input value is not correct. Let’s say you want to sum two numbers from two cells, but the value is a text in the second cell. So, performing this calculation is impossible because you can’t calculate a number or a text.

In this tutorial, we will learn how to fix the error while working with formulas.

Steps to Fix #VALUE! Error in Excel

Here are a few points and steps you can follow to fix the value error:

1. Incorrect Data Types: The first thing you must fix is the data type. For example, if you’re adding or multiplying, the cells should contain numbers, not text or dates.
2. Wrong Reference: Check whether the value you specified in an argument is valid. For Example, if I want to extract the 25^th character from a text but that text has only 20 characters.
3. Dimension of the Arrays: After that, check the dimensions of the arrays you have referred to. For example, you have ten cells in one range, and in the second cell, you have 12. This also leads to the #VALUE! Error.
4. Array Formula Issues: In earlier Excel versions, to apply array formulas correctly, you need to press Ctrl + Shift + Enter instead of Enter.
5. Error Already Exists: Check if any value in your input data has a #VALUE! Error already. This can also lead to having a #VALUE! Error in the result.
6. Spaces or Non-visible Characters: Sometimes, cells might have invisible characters like spaces. Use the TRIM function to remove them and avoid errors.

Above are some initial checks you can perform to fix the error. To understand it further, we will look at a few real-life examples.

#VALUE! Error With SUMPRODUCT

In the following example, we have used the SUMPRODUCT function. In the second array, we have referred to the range with ten cells, but we mistakenly referred to the B1:B9 range, which has nine cells. This leads to having a #VALUE!

This can happen with other functions, like SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS.

#VALUE! Error While Working Dates

In the following example, the date in cell A1 is stored as text and in the wrong format. If we try to use this date in a calculation, it will return #VALUE!

The reason is that when you try to do a calculation, and that date is a test, that function or even formula can’t identify that date, even though the user can read the date format.

To fix this problem, you need to use functions like DATEVALUE or VALUE.

=DATEVALUE(A1) + 1
=VALUE(A1) + 1

If dates are entered in a format that doesn’t match your system’s regional settings, Excel may not recognize them as valid dates. For example, if you enter dates as DD/MM/YYYY but your system is set to use MM/DD/YYYY, Excel will return a #VALUE! Error. In this case, adjust the date format in Excel to match your regional settings or convert the dates using the DATE function.

#VALUE! When Including a Space Value in a Calculation

In the following example, when trying to add cells A1 and A2, I got the #VALUE! Error. Since cell A1 contains a space, that explains why the #VALUE! Error appears.

When Excel encounters a space (or any text character) in a calculation where a number is expected, it cannot operate, resulting in this error. Spaces in Excel cells can act as text, which makes a formula fail and return an error.

Now, to fix this: If the space is only in a single cell, delete it from A1, and the formula will work correctly. If you suspect that spaces might be a recurring issue in your dataset, you can use the TRIM or VALUE function to clean the data automatically:

=TRIM(A1) + A2
=VALUE(A1) + A2

Using functions like TRIM and VALUE helps clean the data and avoid the #VALUE! Error. If A1 contains only a space, it should ideally be replaced with a blank to ensure the formula works smoothly.

Read Also – Remove Extra Spaces in Excel