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.
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.
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.
Related Content: REF Error in Excel (9 Suitable Examples)
- 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.
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.
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.
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.