How to Fix the #VALUE Error in Excel?

If you’ve been working on formulas in Excel, you’ll probably agree that one of the most dreadful things to see is the #VALUE error. I mean, what does #VALUE exactly mean, right?

In this article, I’ll show you the possible causes of #VALUE error and provide you with some solutions that you can do to fix it.

Please note that the #VALUE error can be very generic and could mean many things, so you might need to test out one or more of the solutions below to fix it.

What causes the #VALUE error in Excel?

The #VALUE error provides a bit of a clue on what is causing the problem – the value.

The problem, however, is that this error can mean different things in different Excel functions.

• The #VALUE error in the AVERAGE calculated, SUM, CONCATENATE, and IF functions may mean that one of the cells referred to in the formula contains an error (#VALUE, #DIV/0, etc.).

• The #VALUE error in the VLOOKUP (especially if you have multiple IF statements), COUNTIF, and MATCH functions may mean that the lookup value exceeds the 255-character limit.

• The #VALUE error in the SUMIFS and SUMPRODUCT functions may mean that the rows in the range arguments do not match.

• The #VALUE error in the FIND and SEARCH functions may mean that the find_text does not exist in the within_text. For the SEARCH function, it may also mean that the value set for start_num is invalid.

• For the other functions, the #VALUE error may mean that one of the values specified in the formula has the wrong data type.

Fix the #VALUE Error

If you are working on a complex formula with more than one Excel function, break it down into small chunks first. Copy part of the formula in one cell and see if that results in an error.

But before doing that, ensure that your formula contains absolute cell references (one with ‘$’ before the range addresses) so that it still points to the same cells.

These will narrow things down and make it easier for you to identify where the error started.

1. AVERAGE, SUM, CONCATENATE, and IF functions

If you’re working on AVERAGE, SUM, CONCATENATE, and IF functions, the #VALUE error may occur if one of the cells referred to in your formula contains an error (e.g., #VALUE, #DIV/0).

To fix this, you can do either of the following options:

1. Go to the range of cells specified in your formula and fix the errors that appear. If there are inevitable errors, update the cells’ formula to make it display something else when these errors occur.

Make sure that the replacement you select for the error is a valid value for your formula. You could, for example, use zero to replace the error.

=IFERROR([insert your formula], 0)

2. If you’re unable to update the “source cells” (the ones described above), update your formula so that it displays (or does) something else when an error occurs.

In the screenshot above, the text “Update Price List” appears whenever there’s an error with the total. 

2. VLOOKUP, COUNTIF, and MATCH functions

If you’re working on VLOOKUP, COUNTIF, and MATCH functions, the #VALUE error may occur if your lookup_value contains more than 255 characters. 

To fix this, you have to shorten the lookup_value.

If you’re open to having only the first part of the text used as the basis for the data matching, you could do the following:

1. Adjust the lookup value so that it only contains the first 255 characters. You could do this using this formula: =LEFT(A2, 255)

2. Go to the reference table. Insert a new column. This column will store the first 255 characters of the original text. Add the same formula as above.

3. Go back to your VLOOKUP, COUNTIF, or MATCH formula. Update it so that it points to the new column inserted.

Your lookup value and reference table should now be able to find a match.

3. SUMIFS and SUMPRODUCT functions

If you’re working on SUMIFS and SUMPRODUCT functions, the #VALUE error can occur if the rows in your range arguments do not match.

Adjust the range in the formula so that the total rows match. It should then look something like this:

4. FIND and SEARCH functions

If you’re working on FIND and SEARCH functions, you could encounter the #VALUE error if:

• The start_num is invalid.
  • start_num = 0
  • start_num > length of within_text

• The text searched for could not be found.

To fix this, check your formula to see if start_num is not invalid. It should not be equal to zero and should not be greater than the length of within_text.

If you still have the #VALUE error, there’s a chance that the text you’re trying to search for is not within the other text. If this is the case, you can update the formula to make it display something else whenever this happens.  

5. Other functions

If the above solutions do not apply to your function, check the values in your formula and see if they are in the correct data type.

Excel expects a particular data type for every value entered in the formula (whether it should be a number, a text, or a date). If one of the values is not in the expected format, you will get the #VALUE error.

To fix this, figure out the data type expected for each argument in your formula. See if it requires a number, a date, or a text.

If you have fixed values in your formula, check if their data type is correct.

• Texts and Dates should be inside double quotes (“).
• Numbers should not have any special characters (like leading apostrophes).  

If you have ranges inserted in your formula, check ALL of the cells within that range and see if they have the correct data type.

The tricky part about this one is that even though a cell may look like, for example, a date, it doesn’t necessarily mean that Excel sees it as a date. Excel may see it as a text.

This disparity happens when the cell contains special characters or spaces that are not so obvious to the human eyes.

Use the following formulas to check the data types of the cells.

If the cells are not in the expected format, select the cells within the specified range and update the Number Format (Press CTRL + 1).

If the cells are still not in the correct data type even after changing the Number Format, go over each cell and check for special characters. Check also for extra spaces in between. You may need to use formulas or macros to clean up the data.

To convert a cell into text, add a single quote (‘) at the start of the cell.

For the dates, ensure that they follow the date format on your computer.

Conclusion

The #VALUE error can mean a lot of things. So, the first thing that you need to do is figure out the most probable cause for the error in your formula.

Once you have that figured out, apply the appropriate solution. If the suggested solution doesn’t work, try the others. Just be patient. You’ll figure it out.