VALUE Error in Excel: 7 Reasons with Solutions

Every now and then we get a VALUE error in Excel. According to the Microsoft site, “VALUE is Excel’s way of saying, there’s something wrong with the way your formula is typed. Or, there’s something wrong with the cells you are referencing”. In this article, we discuss multiple cases of VALUE errors occurring day to day uses.


Dataset for Download


7 Easy Ways to Solve Value Error in Excel

Case 1: Text Used in Arithmetic Operations

The #VALUE error occurs in operations whenever Text references are used in arithmetic operations. Addition (+), Subtraction (), Multiplication (*), Division (/), Percent (%), and Exponentiation (^) are Arithmetic Operations.

In the following dataset, we want to calculate the Total Price for each product by the days. But somehow user enters text characters in the Unit Price column resulting #VALUE error.

Here, we enter Arithmetic Operation Product (i.e.,=cell reference*cell reference) to calculate Total Price in the range. As we have some text entries some of the Total Price values result in #VALUE error.

Text in arithmetic-VALUE Error in Excel

The simple way to avoid any text entries not hamper calculation is to use functions. In this case, we use the PRODUCT function to remove the #VALUE errors.

Text in arithmetic Result -VALUE Error in Excel

Not just the PRODUCT function, you can use SUM, SUMIF, or any other functions that replace the particular kind of Arithmetic Operations.


Case 2: Blank Cell Contains Hidden Spaces

Arithmetic Operations returns #VALUE error if there are any spaces in blank cells. Users may enter Space or Spaces in blank cells unconsciously then apply Arithmetic Operations to bring out Values instead they encounter #VALUE error.

Hidden spaces-VALUE Error in Excel

Spaces existing in blank cells.

Hidden Spaces

To avoid this kind of error for any Arithmetic Operations, use respective functions. The respective functions can be SUM function in case of Addition, PRODUCT function in case of Multiplication, etc. For easy representations, we use the PRODUCT function in the case of Multiplication.

Hidden spaces Result


Case 3: Cell Contains Leading Space

Data containing leading space cause any outcome values result in #VALUE error. In this case, we use Subtraction () as Arithmetic Operation to come up with Days from given Dates.

From the dataset, we want the Days between the First and Last sales that occurred. Thus, we use Subtraction () to do the job. However, it results in #VALUE error.

leading space-VALUE Error in Excel

After inspecting cell references (i.e., B6 and B19), you can see a leading space in cell B6.

Leading space

To avoid the #VALUE error, just remove the leading space and Subtraction will come up with the result you require.

leading space result-VALUE Error in Excel


Similar Readings


Case 4: Data Stored as Text Causing VALUE Error in Excel

Often, users input texts in cells that require values. Existing texts in value cells jeopardize Arithmetic Operations multiple functions such as EDATE, NETWORKDAYS, etc. For keeping things simple, we use Multiplication (*) to describe the case type.

We want the Total Price using Multiplication (*) but it comes with #VALUE error similar to the image below.

Data stored as text -VALUE Error in Excel

If you look carefully, an Apostrophe () precedes the entries transforming them into text. Excel stores data in text format automatically if it contains any of the text characters.

Apostrophe precedes data

Use the alternation function options for calculation in case you use different operations. We use the PRODUCT function to negate the effect of text format in those cells.

Data stored as text result-VALUE Error in Excel


Case 5: Performing an Array Formula

From time to time, we use various functions that need different kinds of execution keys to perform operations. Array formulas are one of them. You have to Press CTRL+SHIFT+ENTER altogether to execute a formula.

In this case, from the dataset, we want to sum all the Product Sale multiplying Quantity reference (D4:D17) with Unit Price reference (E4:E17).

Type the following formula in any blank cell (i.e., C19).

=SUM(D4:D17*E4:E17)

Then press ENTER. You’ll encounter #VALUE error similar to the following picture.

Array formula-VALUE Error in Excel

The main thing missing here is that the formula is an Array formula. You have to press CTRL+SHIFT+ENTER altogether to bring up any resultant value.

Clicking on the formula, Press CTRL+SHIFT+ENTER simultaneously, the desired value appears like the following image.

Array formula result


Case 6: Function Argument not Desired Type Causing VALUE Error in Excel

Now, from the dataset, we want the total number of days Sales conducted from the First day to the Last. For this reason, we use Subtraction () to count the days but it returns #VALUE error.

Excel requires cell references to be in the same formats as the desired types. As you want days, you have to keep the reference cells (i.e., B6 and B19) in Date format unless #VALUE error occurs.

You can see the Commas (,) between days, months, and year making an entry except for the date type.

Different argument -VALUE Error in Excel

You can remove the error by replacing all the Commas (,) with Hyphen () or Slash (/). In this case, we use Excel’s Find and Replace feature to do the job.

Step 1: Go to Home Tab > Select Find & Select (from Editing section) > Choose Replace.

Find and Replace feature

Step 2: The Find and Replace window opens up.

In the Find and Replace window,

Insert Comma (,) in Find what command box and Hyphen () or Slash (/) in Replace with command box.

Click Find All.

All existing cells containing Commas are lined up below the Find and Replace command box.

Find and Replace window

Step 3: Click on Replace All.

Replace all command

Existing all commas will go away and you’ll have the number of operation days in the desired cell (i.e., C2).

Different argument result-VALUE Error in Excel


Case 7: Ultimate Solution for All Error (IFERROR Function)

To handle #VALUE error of errors in Excel, we can use the IFERROR function. The IFERROR function offers an operation to execute and a preselected value to show if the operation returns Errors of any kind.

Here, we want the Total Price multiplying Quantity and Unit Price. However, we encounter #VALUE error for whatever reason.

IFERROR function -VALUE Error in Excel

And we just want to ignore the errors and move on. To do so, we want a specific value to appear in the Error cells. Let’s say, the user wants to check if the input values are in the required types. In this case, we use the IFERROR function. The syntax of the IFERROR function is

IFERROR (value, value_if_error)

In the formula,

value; can be a value, reference or formula.

value_if_error; is the value IFERROR function returns if any error occurs.

Step 1: Paste the following formula in any cell (i.e., F4).

=IFERROR(D4*E4,"Check the Value Type")

Inside the formula,

D4*E4; is the value.

“Check the Value Type”; is the value_if_error value if any error occurs.

IFERROR Formula

Step 2: Press ENTER and Drag the Fill Handle to display the value_if_error (i.e., “Check the Value Type”) value in any cell where the error occurs.

IFERROR function result-VALUE Error in Excel

Read More: Errors in Excel and Their Meaning (15 Different Errors)


Conclusion

In the article, we describe multiple cases behind the #VALUE error to occur. We discuss data type, spaces, leading space or spaces, varying arguments, and array formula which is the prominent reasons #VALUE error to occur. We also discuss the IFERROR function; an ultimate solution for all errors. Hope you find your case among the described ones. Comment, if you have further queries or have something to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo