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.

**Table of Contents**hide

**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.

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.

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.

**Similar Readings**

**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**).

`=SUM(D4:D17*E4:E17)`

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.

**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.