How to Remove Value Error in Excel (4 Quick Methods)

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

  • In the example below, 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.
  • Replace the formula in cell F6 with the following one. This will ignore text values in the cell range and remove the #VALUE! Error from the output cell.

remove valu error in excel using SUM function

Read More: [Fixed!] VALUE Error in Excel

Method 2 – Use the IFERROR Function in Excel to Remove #VALUE! Error

  • In the example below, cell D6 is empty. Excel takes the values of empty cells as 0, and Excel should not show any error here. So, why is it showing a #VALUE! error here?

  • Because cell D6 is not actually empty although it appears to be empty. It contains some spaces in there. This is why Excel is displaying the #VALUE! error.

  • Here, we will use the IFERROR function to remove the error. Apply the following formula in cell F6 to fix the error.
=IFERROR(C6+D6+E6,"Check Data")

remove value error in excel using IFERROR function

Method 3 – Removing #VALUE! Error with IF & ISERROR Functions

  • In the following example, the #VALUE! error occurs because the date in cell B6 is not formatted properly.

  • You can use the IF and ISERROR functions together to remove the #VALUE!. Change the formula in cell D6 with the one below to remove the error.

remove value error in excel using IF & ISERROR functions

Method 4 – Using Excel PRODUCT Function to Remove #VALUE! Error

  • In the following example, the #VALUE! error occurs because of the text in cell C6.

  • Use the PRODUCT function to multiply only the numeric values. This function ignores text and other non-numeric entries. So if your input cells have any value other than a number, you can use this function to avoid #VALUE! error.
  • Use the formula below in cell F6. Excel will not show the #VALUE! error anymore.

remove value error in excel using PRODUCT function

Download Practice Workbook

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

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF