The **#VALUE!** error is a common occurrence in excel. It means that there are problems with the values that are used in the formula. This article illustrates how to remove value error in excel in different cases. The following picture gives an idea of the purpose of this article.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook from the download button below.

**4 Methods to Remove #VALUE! Error in Excel**

Here, I’m going to highlight 4 methods you can follow to know how to remove value errors in excel. So, let’s begin.

**1. Remove #VALUE! Error Using Excel SUM Function**

- Consider the following example. The
**#VALUE!**occurs because there is a text in cell**D6**.

- Here, you can use the
**SUM function**instead of using the addition operator (**+**) to get the total. - So, let’s replace the formula in cell
**F6**with the following one. This will ignore the text in cell range and hence remove the**#VALUE!**Error from the output cell.

`=SUM(C6:E6)`

**Read More: VALUE Error in Excel: 7 Reasons with Solutions**

**2. Remove #VALUE! Error Using Excel IFERROR Function**

- Now, observe the following
**#VALUE!**. This occurred probably because cell**D6**is empty. But, Excel takes the values of empty cells as 0. Then, Excel should not show any error here.

- But, cell
**D6**is not actually empty although it looks like so. There are some spaces in there. That’s why excel is showing the**#VALUE!**

- Here, we will use the
**IFERROR function**to remove the error. Now, apply the following formula in cell**F6**instead of the earlier one. After that, you will see that the error is fixed.

`=IFERROR(C6+D6+E6,"Check Data")`

**Related Content: REF Error in Excel (9 Suitable Examples)**

**Similar Readings:**

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

**3. Remove #VALUE! Error with IF & ISERROR Functions**

- Now, consider the following example. Here, the #VALUE! error occurs because the date in cell
**B6**is not formatted properly.

- You can also use the
**IF**and**ISERROR**functions together to remove the**#VALUE!**. Now, change the formula in cell**D6**to the one given below. Then, you will see that the error is removed.

`=IF(ISERROR(C6-B6),"Invalid",C6-B6)`

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

**4. Remove #VALUE! Error Using Excel PRODUCT Function**

- Now, think about the following example. Here, the
**#VALUE!**error occurs because of the text in cell**C6**.

- You can use the
**PRODUCT function**that ignores the arguments which are not numbers and multiply only the numeric values. So if your input cells have any value other than a number, you can use this function to avoid**#VALUE!**error. - Now, use the formula given below in cell
**F6**. Then, excel will not show the**#VALUE!**error anymore.

`=PRODUCT(C6:D6)`

**Read More: Reasons and Corrections of NAME Error in Excel (10 Examples)**

**Hide #VALUE! Error in Excel with Conditional Formatting**

- You can hide all types of errors in excel using
**Conditional Formatting**. Now, consider the following example. Here, the**#VALUE!**error occurs because the text-formatted date in cell**B6**is formatted as**mm/dd/yyyy**. But the system (computer) date format is**dd/mm/yyyy**.

- First, select
**Conditional Formatting****>> New Rule**from the**Home**tab as shown in the following picture.

- Then select
**Format only cells that contain**in the**Select a Rule Type:**After that, choose**Errors**for**Format only cells with**using the dropdown arrow as shown below. Next, click on**Format**.

- Now, you will see the
**Format Cells**dialog box is open. Then, go to the**Font**After that, choose the**Color**as white using the dropdown arrow. Then, hit the**OK**button.

- After that, you will see that the preview box is empty. Then, hit
**OK**

- Now, you will see that the
**#VALUE!**error is no longer there. But, the error signs are still visible as shown in the following image.

- To fix this problem, go to
**File >> Options**. Then, uncheck**Enable background error checking**. After that, click on the**OK**button.

- Finally, the error is no longer visible.

- Alternatively, you can just change the date format in cell
**B6**to**dd/mm/yyyy**to remove the error.

**Things to Remember**

- You can write anything you want inside the quotation marks (
**“ ”**) in the formulas. - Always try to use functions instead of operators to avoid
**#VALUE!**. - The best way to remove any error in excel is to find out the root cause of the error and remove that.

**Conclusion**

Now you know how to remove value error in excel. Hope this article has helped you to solve your problem. Please use the comment section below for further queries or suggestions. You can also visit our **ExcelDemy** blog to learn more about excel every day. Stay with us and keep learning.