Skip to content 
How to Fix #VALUE Error in MS Excel?
When you’re working formulas in Excel, you will often encounter the #VALUE error.
While the error is rather generic and doesn’t tell you what the problem is, in most cases, this happens when you give a formula a data type that is not desired (explained with examples below).
For example, if you have a few numbers that you’re adding in Excel and try adding a text value in the same formula, Excel will give you an error. MS Excel expects only digits to be used in addition or subtraction.
Value Error Becuase of Text String in Formula
Some formulas are developed to only work with numbers.
And when there are no_numbers (such as text strings) operated in the formula, you may see a value error.
The image below is a straightforward example where I need to add a student’s scores in different subjects.
While I have the numbers for all the subjects, I don’t have them for Math (there is NA written instead of the score value).
If I use a simple arithmetic equation to get the sum of all the scores, I will bring a value as shown below.
This is because the formula expects only to add numbers, and I’m referencing a cell with a text string instead.
How to tackle this?
In such simple addition subtraction formulas, one way to eliminate the error would be to remove the text string so that only numbers are left. MS Excel VBA can add that.
If I remove the text ‘NA’ from the cell, the formula will work fine in our example.
Another way you can tackle this VALUE error is by utilising in-built functions instead of using the arithmetic equation.
Many of the commonly used Excel formulas are built to ignore any unexpected data type that is not supported by the formula.
For example, in this case, you can utilise the SUM formula, which would ignore all the text strings and only add the numbers.
Value #Error Because of Incorrect Argument Type in Formulas
If you’re using a formula that gives you a value error, there’s a good chance you have operated an argument that is not the expected data type in the formulas.
Let me give you an examples and explain.
Below I have the year, month, and day values in three separate cells.
If I use this data in the DATE formula to get the serial number of this date, it will give me a value error.
This is because the date formula expects only numeric values as arguments, and in this formula, I retain the month name, which is a text string.
How to tackle this?
Check and double-check the formulas to ensure that the argument type are correct.
You may be operating formulas that you’re not extremely well versed with.
Value Errors Due to Incorrect Date Format
Excel has multiple in-built date formats that it can determine.
But in some cases, you may contain a date in a format that Excel does not identify as a date.
In such a strategy, Excel would treat those dates as text strings.
If you try and use these dates in formulas built to handle date values, you will get a value error.
Let me explain with an example.
Below I have a date in cell A1, but this is not in the format that MS Excel recognizes as a proper date format.
Suppose I try and use this date in the EOMONTH formula (which requires a date as one of the arguments). It will give me the value error.
Removing #VALUE Error Using IF or IFERROR Functions in Excel
Excel has some built-in error handling formulas that can help you attack the value error.
This can be done utilising the IFERROR formula (or a combination of IF and ISERROR).
Below I have a data set where I have the dates in column A and the number of days that I like to add to these dates in column B.
As you can see, some of the results show the value error as the dates are not in the proper format (i.e., these are in a format thatdoesn’t recognize as a date; hence these are regarded as text strings)
You can use the below formula to get a more meaningful text instead of the values error.
The above IFERROR formula would return the result of the first argument, and in case that results is an error, it would return the second argument.
Find All Cells with the #VALUE Error
If you’re auditing someone else’s work, it may be helpful to know how to quickly find out all the cells that have the value error in them.
You can be done this using a simple Find operation in Excel.
Below are the steps to quickly find out all the cells that possess the value error in a worksheet:
- Click the Home tab
- In the Editing group, click on Find and Select
- In the options from the drop-down, click on Find. You will open the Find and Replace dialogue box
- Click on the Options button. You will make available some additional options.
- In the Find and Replace dialogue box, make the following changes:
- In the ‘Find what’ field, enter VALUE!
- From the ‘Within’ drop-down, select Workbook (or keep in Worksheet if you only want to find the error in the active Worksheet)
- In the ‘Look in’ drop-down, choice Values
- Click on Find All
You should see the list of cells as shown below.
