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.

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. ****Using Excel SUM Function to ****Remove #VALUE! Error**

- 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: **[Fixed!] VALUE Error in Excel

**2. ****Inserting Excel IFERROR Function to ****Remove #VALUE! Error**

- 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")`

**3. Removing #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)`

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

- 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 that 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)`

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

**Download Practice Workbook**

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

**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. Stay with us and keep learning.

**Related Articles**

- How to Calculate Mean Squared Error in Excel
- How to Calculate Root Mean Square Error in Excel
- How to Remove Number Error in Excel
- [Fixed!] NUM Error in Excel
- Excel Error: The Number in This Cell is Formatted as Text

**<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel**