Whenever a tool enables you to perform multiple operations, you often have to face errors. When it comes to Excel, an error is a common thing while using Excel formulas and functions. However, the main thing is to fix those errors correctly. Do you have any idea when is the Value error raised in MS Excel?
The #VALUE! error occurs when you are working with formulas in Excel. Though value error is not so rare and does not show the real cause behind it, however in most cases it usually appears when your formula is given as an unexpected data type.
Let’s understand it in detail:
What is a #VALUE Error in Excel?
The presence of unexpected data in a formula may let you experience a #VALUE error. Below are some reasons for this error:
- Your data is stored as text, or
- Your text is used in arithmetic operations, or
- Or your cells may have hidden spaces.
Below are simple explanations you can follow to deal with this error:
When is the VALUE Error Raised in MS Excel?
Typically, the #VALUE error is raised in MS Excel when there are characters, text, or spaces within the formulas where a number is supposed to be there.
Reasons for Experiencing #VALUE Error in MS Excel
Most people ask why a Value error occurs while working with formulas. If a wrong formula is entered or something is wrong with the cells, chances are that you will encounter a value error. Apart from that, we have some other reasons that let you face this error. Let’s have a look at these reasons:
- Your cells might be empty
- The text is used in the arithmetic operations
- The array formula is not closed properly
- Dates in your data are stored as text
- The text value is added where a number is expected.
You cannot find the exact reason behind the occurrence of value error, however, it hardly depends upon the formulas and calculations.
Fix #1: Incorrect Argument Type in Formulas as a Reason of Value Error
Suppose you are using a formula in Excel and a value error appears, it simply shows that there is an unexpected argument data type in the formula. Let’s have an example:
In this example, you can see the three separate cells of the year, month, and day. Assume that you put this data in the DATE formula to find a serial number, you will notice a value error instantly.
A date formula is supposed to have numeric values only whereas you have added another type of data. As a result, it gives you a value error.
Did you get that?
Now, let’s understand how would you settle this issue.
You need to ensure that the data type added as arguments are correct. It might be possible that your formulas are not confirmed with the values. In that case, you need to check the help for formulas and ensure the type of argument is correct.
Fix #2: Replace the Spaces
Another reason behind the occurrence of value errors is the spaces. When your cells have hidden spaces, they look empty and that’s the reason for the error. In this case, you have to remove these hidden spaces, and for this below are the easy-to-follow tricks:
Use of Find & Replace Feature
Open the cells that contain formulas and select them. Remove the space by selecting the entire column. Let’s have a look at the following steps:
- Select all the cells in which you need to replace the space.
- To replace the hidden spaces from all columns, click on a column letter and select entire columns.
- Now, open the Home tab and click on the Find & Select option and then choose Replace.
- Here, you need not replace the spaces with anything.
- Enter the space in the Find What box field by pressing the Spacebar key from the keyboard.
- Now, in the Replace with box, you have to delete everything.
- Click on the Replace All option.
- If you want to replace the space with a single column, you have to:
- Click on the Find Next option.
- And then click on Replace button instead of clicking on Replace All.
That’s it!
How to Fix #VALUE Error with IF or IFERROR Functions
In Excel, you will find a solution to everything. Similarly, some built-in error handling formulas are present in Excel that let you deal with the value error. For this, you can use the IFERROR formula or sometimes a combo of IF and IFERROR.
In the following example, you can see in column A there are dates, and in column B the number of days is needed to be added.
In column C, the following formula is used that gives the date after the particular number of days:
=A2+B2
Some results are having a value error because the dates are not in the proper format. To have the right outcome instead of an error, you need to use the following formula:
=IFERROR(A2+B2,”Check the date”)
Using the above IFERROR formula will give you the result of the first argument and if you find an error it would give you the second argument.
Below another combo of IF and ISERROR is given that can be used:
=IF(ISERROR(A2+B2),Check the Date”,A2”B2)
If the above formula gives an error in the result, it will return the second argument or the third argument.
That’s it!
Summary:
In this post, you have learned what the value error is and how you can fix it. Apart from that you have also found an answer to when is the value error raised in MS Excel or what reasons are there that provoke the #VALUE error to occur. Hopefully, this post has enough for you!
