FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

#VALUE! Error in Excel

Updated on December 26, 2023
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What is #Value! Error in Excel?

Error is a common and integral part of Excel formulas and functions, but fixing those errors is what makes you a pro in Excel. As a beginner, finding those errors and restoring them to work correctly is not easy. Every error occurs only because of a user’s mistakes. So, it is essential to know our mistakes and why those errors are happening. In this article, we will show you why we get #VALUE! Errors in Excel and how to fix them.

For example, the simple formula may return as the #VALUE! Error if a space character in any cell is created to clear a cell. To fix the error, the cell which contains a space character needs to be selected and then pressed the Delete key.

How to Fix #Value! Error in Excel?

You can download this Error Value Excel Template here – Error Value Excel Template

Case #1

#VALUE!: This excel errorExcel ErrorErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more occurs for multiple reasons depending upon the formula we use. The most common reason for this error is the wrong data type used in the cell references.

Follow the below steps to fix the value error in Excel.

  1. Look at the below formula for adding different cell values.


    Excel Error in Value Example 1.0.0
    In the above basic Excel formula, we are trying to add numbers from A2 to A6 in cell A7, and we have got the result of #VALUE! Error. In cell A5, we have a value as “Forty,” which is the wrong data type, so it returns #VALUE!

  2. To get the correct sum of these numbers, we can use the SUM function in excel.


    Excel Error in Value Example 1.1

  3. We get the following result.


    Excel Error in Value Example 1.2
    The SUM function has ignored the wrong data type in cell A5, adding the remaining cell values and giving the total.

  4. Otherwise, we can change the text value in cell A5 to get the correct result.


    Excel Error in Value Example 1.3
    For the earlier formula, we have only changed the A5 cell value to 4,000. So now, our previous function is working properly.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Case #2

Now, we will see the second case of #VALUE! Error in Excel formulas.

Example
  • Look at the below formula.
Excel Error in Value Example 2.0.0

We have divided column B with column A. And we have got three different results.

  • Result 1 says B2/A2. In both cells, we have numerical values, and the result is 80%.
Example 2.0
  • Result 2 says B3/A3. Since there is no value in cell B3, we have got the result of 0%.
Excel Error in Value Example 2.1
  • Result 3 says B4/A4. It is also the same case as Result 2.
Example 2.2
  • We have got an #VALUE! Error, so curious case.
Excel Error in Value Example 2.3
  • The main reason for this error is that the empty cell is not truly blank because there could be an errant space character.
Example 2.4

In cell B4, we have a space character that is not visible to naked eyes. However, it is the reason why we have got #VALUE! Error.

We can use the LEN Excel FunctionLEN Excel FunctionThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more or ISBLANK Excel function to deal with these unnoticeable space characters.

  • LEN function will give the number of characters in the selected cell. LEN considers a space character as a single character.
Excel Error in Value Example 2.5

Look at the above function. We have got one character resulting in cell B4, which confirms B4 cell is not an empty cell.

  • Similarly, the ISBLANK function shows TRUE if the cell is empty. Otherwise, it shows FALSE.
Example 2.6

Look at the above result. We have got FALSE as a result for the B4 cell. So, we can conclude that cell B4 is not an empty cell.

Case #3

Another case of resulting #VALUE! Excel error is because the function argument data type is wrongly stored.

Example

Look at the below image.

Excel Error in Value Example 3.0
Example 3.1.0
  • The first two cells got the result, but we have an error result of #VALUE!
Excel Error in Value Example 3.3

The end date in the B4 cell has the value of NA, the non-date value, resulting in #VALUE!

  • We need to enter the proper date value in cell B3 to correct this error.
Example 3.4

There is also another chance of getting the same error even though we have the date value.

  • For example, look at the below cell.
Excel Error in Value Example 3.6

Even though we have a date in cell B4, we still have the #VALUE! Error. In cell C4, a date is not a date stored as a text value, so we need to convert this to a proper date format to get the correct result.

Things to Remember Here

  • Other error values are explained in separate articles. This article is dedicated to #VALUE! Error.
  • The #VALUE! Error occurs for multiple reasons. We have listed above all the possible scenarios of this error.

This article has been a guide to Excel #VALUE! Error. Here, we discuss fixing a value error in Excel with examples and a downloadable Excel template. You may learn more about excel from the following articles: –