How to Remove #VALUE! Error in Excel

From this tutorial, you will understand how to remove #VALUE! error in Excel.

#VALUE! is probably one of the most common error types that we get when we produce an error from our formulas in Excel. It can be frustrating if we don’t know the cause and how to fix this error. If we don’t fix it, it might be hard for us to get the results we want from our data processing in Excel.

If you find this #VALUE! error too in your Excel work and don’t know how to solve it, then you have come to the right place. Here, Compute Expert will discuss this #VALUE! deeply and give common solutions that are usually able to fix it.

Want to know what is exactly this #VALUE! error and how to remove it? Read this tutorial until its last part.

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more

Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!

What is #VALUE! Error in Excel?

#VALUE! error in Excel is an error type that is caused by the data type of one or more inputs in a formula that doesn’t match what the formula expects.

For example, take a look at the screenshot below.



Here, we want to add the number of our apples and oranges. However, since the quantity of the apples in the worksheet here is written NA (not a number), we get a #VALUE! error from our calculation formula.

What are the Causes of #VALUE! Error in Excel?

There can be many causes of #VALUE! error in Excel. However, here are four that we think are the most common ones.

• Number is written as text
• Date has a data type other than a date
• Wrong input type in a function
• Hidden space characters in a blank cell

How to Remove #VALUE! Error in Excel?

To understand the way to remove #VALUE! errors in Excel, let’s take a look at each of the common causes that we have discussed previously and create a solution for them.

The first common cause is the number(s) written as text. To understand this cause, take a look at the screenshot below.



Here, the cause of the error is that we write our apple quantity number in text, not in numbers. To fix this, we just have to write that quantity in number and our formula will produce the correct result.



What about the second cause, our date has a data type other than date? Take a look at the screenshot below.



Here, we want to calculate the difference in days between the expiry date and today’s date. However, we wrongly write our expiry date in a text format that is unrecognizable by Excel as date data.

As a result, Excel doesn’t change our expiry date into its default date format automatically and our date is recognized as text instead. This makes our calculation formula cannot do its subtraction process and produces the #VALUE! error.

What is the solution here? Well, it is almost the same as the previous cause. We should change our date by writing it in a format recognizable as a date by Excel (e.g. mm/dd/yyyy) or just use the help of a DATE function.



By doing that, our calculation formula will produce its correct result.

The next cause we will discuss is the wrong input type in a function. This function can be any function that Excel has and the wrong input type can be anything that shouldn’t be the data type of the input for that function. For example, let’s see how we use the DATE function in Excel here.



Can you guess what is the problem here? Yes, it is in the input of our month to the DATE function.

DATE expects its year, month, and day inputs to be in numbers. Nevertheless, in the screenshot example, we input our month as a text. This causes DATE to produce a #VALUE! error as it wants a number for the month, not a text.

Solution? Just make sure that all of the inputs in the function you use in Excel are in the correct data type that the function requires. That should make you remove your #VALUE! error and get the correct result from your function.



The last cause of a #VALUE! error we will discuss here is the space characters that might be hidden in the cell which coordinate we input into our formula. To see this cause in action, take a look at the screenshot below.



Excel usually ignores the blank cells we input into its calculation formulas. However, in the screenshot above, it doesn’t ignore the blank and, instead, produces a #VALUE! error.

Now, why that happens? The reason here is that our blank cell there isn’t completely blank. There is a space in there that makes Excel doesn’t ignore the cell content and includes the space in our calculation formula. This makes us produce a #VALUE! error (because space is a text data type in Excel and our calculation formula only wants numbers).

If you get the same kind of error, you need to make sure that your blank cells are really blank. Just press the Delete button on your keyboard on those blank cells. That should delete all of the contents in those cells, if there are any, and make your calculation formulas produce the right results.

How to Find All #VALUE! Error in an Excel Worksheet?

You might think that there is some #VALUE! errors in your worksheet and you want to fix them. However, you still don’t know their exact locations. How can you find those #VALUE! errors?

You can do it by using the Find and Replace dialog box in Excel. To use the box, first, go to the Home tab, click the Find & Select dropdown button there, and choose Find…. You can also just press Ctrl + F (Command + F on Mac) buttons on your keyboard.



Then, in the Find and Replace dialog box that shows up, click the Options button.



Next, click the Look in dropdown that shows up and choose Values.



Then, in the Find What textbox, type #VALUE! as the reference for finding the #VALUE! errors.



After that, click the Find All button.



The dialog box should now list all of the #VALUE! error instances in your worksheet. When you click one of the instances in the list, your cell cursor will highlight the #VALUE! error instance location in your worksheet.

How to Hide #VALUE! Error in Excel 1: Conditional Formatting

Besides the way to fix the #VALUE! error, you might also want to know the way to hide it so it doesn’t show up in your worksheet. There are two methods that we will discuss for that. The first one is by using the conditional formatting feature in Excel. Note that this method will hide all of your errors, not just #VALUE! errors.

To use this method, first, highlight all the cells where you want to hide the #VALUE! errors. Then, go to the Home tab, click on the Conditional Formatting dropdown button there, and choose New Rule….



In the dialog box that shows up, choose Format only cells that contain in the Select a Rule Type box.



Then, click the dropdown on the bottom left and choose Errors.



Next, click the Format… button



In another dialog box that shows up, click the Color dropdown and choose the white color (or any color that is the same as your cell color).



Then, click OK and OK again on both dialog boxes. Your #VALUE! (an all other) errors have now been hidden in the cells that you highlight!



However, there are still some marks in the cells that contain the #VALUE! errors. What should we do if we want to hide them too?

For that, click the File button on the top left of your Excel workbook.



Then, choose More… and then Options.



In the dialog box that shows up, click Formulas on the left box and then uncheck the Enable background error checking checkbox by clicking on it if it is checked. Then, click OK.



Done! Your #VALUE! errors should be hidden completely now without the error marks.

How to Hide #VALUE! Error in Excel 2: IFERROR or IF ISERROR

Another way to hide #VALUE! error in Excel is by covering your formula with the IFERROR or IF and ISERROR functions. However, as with the first method, this method will also hide other types of errors.

Here is the writing syntax of the IFERROR with your formula to hide its #VALUE! error if it ever produces one.



And here is the writing syntax if you use IF and ISERROR instead.



Here is an implementation example of these two formulas.



As you can see here, we can hide the #VALUE! error we get from our calculation formula by using IFERROR or IF and ISERROR. Just cover our formulas with them by using the writing syntaxes we have discussed earlier and we should be good to go.

In the screenshot, we want 0 to replace our formula result if the error happens. If the error doesn’t happen, it will give the formula result instead.

Exercise

After we have learned about how to remove #VALUE! errors in Excel, let’s do an exercise. This is so we can understand more about the tutorial lessons.

Download the exercise file below and do the instruction. Download the answer key file too if you have done the exercise and want to check your answers.

Link to the exercise file:
Download here

Instruction

Remove all the #VALUE! errors in the worksheet by fixing the inputs of the formulas!

Link to the answer key file:
Download here

Additional Note

Calculation functions in Excel can bypass some of the common causes of #VALUE! error from number inputs. So, if you want to be safer, you can use relevant calculation functions on your numbers rather than calculation formulas you write yourself to get the calculation results you want.

For example, take a look at the screenshot below. We sum apple, orange, and mango quantities here by using the calculation formula we write ourselves and the SUM function.



As you can see, the calculation formula we write ourselves produces #VALUE! error because a number we input into the formula is a text and there is a blank cell with hidden spaces. Meanwhile, our SUM function ignores those wrong inputs and still produces a sum result.

However, you should always try to fix the input errors you have that cause the #VALUE! error in the first place. Doing that will make sure you can get the correct data processing results for your Excel work.



Excel articles/tutorials you might want to read too: