Ah, the dreaded #VALUE error! It is like Excel’s version of saying, “I can’t even with this!”
If you have ever been working on a formula, only to be met with that frustrating error message, you know the struggle. But do not worry—Excel is not broken, it is just a little confused. In this post, we will dive into the most common reasons for the #VALUE error and show you how to get things running smoothly again. Let’s turn those errors into answers!
Contents
Contents
When does a #VALUE error occur?
There are more than a few reasons you could get the #VALUE error in excel when trying to get your formula to work. Generally when you get a #VALUE pop up in your cell it means that Excel cannot understand what you want it to do.
Here are some of the most common cases when you could get that error:
- Incorrect formula syntax – To fix this error you will want to check that your function’s arguments are all there, correct, and in the right order.
- Unexpected data type – Some Excel formulas require a certain data type which can be a number or a piece of text. If the cell you want to use the formula for contains a different data type this may be causing your #VALUE error.
- Incompatible range dimensions – If your function is referencing a few ranges of different size or shape, this might confuse Excel in calculating the formula and it could give you an error.
- Spacing errors – If you want your function to reference a cell inside your spreadsheet that contains a space character you will get a #VALUE error. Visually these cells look empty.
- Dates formatted as text – If you have typed in a few dates formatted in a way that Excel does not understand, they will be interpreted as text strings. This will give you a #VALUE error.
- Invisible characters – In some cases you might have hidden characters in a cell which could stop your formula from working.
There are plenty of cases when you can get a #VALUE error when working with Excel. You now have a good starting point in finding a fix. While Excel is one of the best spreadsheet programs out there, if you are a beginner in the spreadsheet world you might want to start off with a simpler spreadsheet software. Why not give OfficeSuite Sheets a try?
Next up we will see how you could fix the errors and some specific formulas where you could get them.
How to fix the #VALUE error?
Check for any unneeded special characters
Make sure your data has no non-numbers values, text, or special characters. These could be spaces or symbols. Unfortunately Excel does not let you easily distinguish blank cells and cells with space characters. Luckily, though, you can use the ISTEXT function to recognize them. If a value has been introduced to the cell (this could be a space) it will return a TRUE value.
To fix the error you should clear out the cells that contain space characters and then run your formula again.
Use built-in Excel functions instead of operators
An alternative approach could be using the built-in functions in excel instead of operators when creating formulas. They can be used for disregarding non-textual values.
Take into consideration that cell E4 does not include a value but only a dash (-) which means that it can’t be calculated but is also not empty. This is why you get a #VALUE error.
You can fix this by using the PRODUCT function. In this case it successfully returns a value of 45 without any errors.
Make sure you are using the correct data formatting
In the image below you will see the WEEKDAY function which will give you the day of the week for the specific date you have chosen. When using this specific formula you may get a #VALUE error when the date you have introduced in the desired cell is in the general format instead of the date format.
To fix this you need to head over to Home->Number-> and select “Date“
Change the #VALUE error
When you can’t find a solution, sweep your problems under the rug, right? Well, not exactly.
If you know in advance that some of your cells will get a VALUE error you can tell Excel to show a different message instead. In this case you can use the IFERROR formula. It will replace the error message with whatever value you want to.
Assess the formula
If you’ve tried all other options and still can’t find the cause of an error, use Excel to evaluate the formula. This should be your final troubleshooting step.
- Select a formula that results in a #VALUE! error.
- Click on the “Formulas” tab and choose Evaluate Formula.
- Excel will break down each step of the formula, helping you locate the error.
- For example, if adding 10 and 25 shows the correct result of 35, but the next step involves text, the error will occur there.
- After identifying the issue, rerun the formula to confirm it works.
How to fix #VALUE error in specific formulas?
HLOOKUP
Common Scenarios:
- Incorrect Lookup Value Type: The lookup_value is not found in the first row of the table_array, or its type doesn’t match the type of the values in the lookup row (e.g., text vs. number).
- Inconsistent Table Array Dimensions: The table_array is not a valid range or does not have enough columns.
How to fix:
- Ensure that the lookup_value matches the data type and format of the values in the first row of table_array.
- Verify that the table_array range is correctly specified and has the right number of rows and columns.
VLOOKUP
Common Scenarios:
- Lookup Value Not Found: The lookup_value is not found in the first column of the table_array, or the data type does not match.
- Incorrect Column Index Number: The col_index_num is greater than the number of columns in the table_array.
How to fix:
- Confirm that the lookup_value is present and matches the type of the values in the first column of the table_array.
- Check that the col_index_num is within the valid range (i.e., between 1 and the number of columns in the table_array).
SUM
Common Scenarios:
- Non-Numeric Data: One or more of the cells within the range contains non-numeric data that cannot be converted to a number.
- Array Formulas or Improper Range References: The range contains arrays or references that Excel cannot interpret as numbers.
How to fix:
- Ensure all cells within the range contain numeric values or values that can be interpreted as numbers.
- Verify that the range references are correct and do not include text or errors.
AVERAGE
Common Scenarios:
- Non-Numeric Values: The range includes text or other non-numeric data.
- Array Formulas or Improper Range References: Similar to SUM, if the range includes non-numeric data or invalid references.
How to fix:
- Make sure the cells within the range contain numeric values.
- Correct any range references or ensure there are no text or error values in the specified range.
COUNTIF
Common Scenarios:
- Incorrect Criteria Format: The criteria argument is not in the correct format or is improperly referenced.
- Non-Numeric Data: The range contains data types that COUNTIF cannot process correctly.
How to fix:
- Ensure that the criteria is specified correctly (e.g., use quotes for text criteria).
- Verify that the range contains values that are compatible with the criteria being applied.
IF
Common Scenarios:
- Incorrect Argument Types: One or more arguments are not in the expected format or type (e.g., using text where numbers are expected).
- Complex Formulas in Arguments: If the value_if_true or value_if_false contains complex formulas that result in errors.
How to fix:
- Make sure the logical_test and the value_if_true and value_if_false arguments are correctly formatted and appropriate for the expected data types.
- Simplify or debug the formulas used in value_if_true or value_if_false to ensure they don’t produce errors.
- For all these formulas, you can use the ERROR.TYPE function to help identify specific errors, but understanding the common reasons for #VALUE! will often guide you to the root cause more quickly.
Other errors you may get
#DIV/0!
You could get a #DIV/0! error when trying to divide by 0. This might be a typo where you put the 0 in the denominator of a division operation.
To fix this you simply need to make sure the denominator of your division operation is different from 0. A smart way to approach this error is to use the IF formula to handle cases where the denominator is 0. For example: =IF(B1=0, “Error”, A1/B1)
#REF!
A #REF! error refers to a cell reference that is not valid. This can happen when you delete cells referenced in the formula you are trying to use. Another case may be if you have set an incorrect cell reference when introducing the formula.
Good news, though. In both these cases there is an easy fix. You should start by updating the cell references in the formula to valid locations. If that does not work you might want to try restoring any deleted cells if possible.
#NAME?
A #NAME? error serves to tell you that Excel does not recognize the name or function used in the formula. This is usually caused by misspelled function names (or lacking functions if you are using alternatives to Excel). Additionally you might want to check for unrecognized named ranges or use of text values without quotes.
#NUM!
A common error to get when working with numeric values. This is Excel’s way of telling you that your formula (or function) contains invalid numeric values. This could be caused by using numbers too large or too small for Excel to process. Another common cause is using functions like IRR or PMT.
To fix this error you can verify all numeric values within acceptable ranges or that your function arguments are correct.
#NULL!
A #NULL! error indicates that a formula contains an incorrect range operator. This may happen if you are using a space instead of a comma or colon between range references. To fix it you will want to make sure you are using the correct range operator. This would be a colon for ranges or a comma for separate arguments.
Conclusion
The #VALUE error may seem like an obstacle at first, but with a little troubleshooting, it is entirely fixable. Whether this is cleaning up invisible characters, adjusting data formats, or simply tweaking your formula, you now have the tools to banish those pesky error messages for good.
And remember, if Excel’s quirks have you feeling stuck, there is always OfficeSuite Sheets as an easy alternative to get your work done with fewer bumps along the way.
