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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

remove valu error in excel using SUM function

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

remove value error in excel using IFERROR function


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)

remove value error in excel using IF & ISERROR functions


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)

remove value error in excel using PRODUCT function


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.

remove value error in excel using conditional formatting

  • 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

 

 

ExcelDemy
Logo