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.
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.
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.
Spaces existing in blank cells.
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.
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.
After inspecting cell references (i.e., B6 and B19), you can see a leading space in cell B6.
To avoid the #VALUE error, just remove the leading space and Subtraction will come up with the result you require.
- How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)
- Excel VBA: Turn Off the “On Error Resume Next”
- How to Fix #REF! Error in Excel (6 Solutions)
- [Fixed] Excel Print Error Not Enough Memory
- [Fixed] Excel Found a Problem with One or More Formula References in This Worksheet
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.
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.
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.
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).
Then press ENTER. You’ll encounter #VALUE error similar to the following picture.
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.
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.
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.
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.
Step 3: Click on Replace All.
Existing all commas will go away and you’ll have the number of operation days in the desired cell (i.e., C2).
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.
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.
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.
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.