How to Fix #VALUE! Error in Microsoft Excel
A vital skill in spreadsheet wizardry
If you’re often working withformulas in Microsoft Excel, you’ve probably encountered the #VALUE error. This error can be really annoying because it’s very generic. For example, adding a text value to the numbers formula might trigger this error. That’s because when you add or subtract, Excel expects you to use only numbers.
The easiest way to deal with the #VALUE error would be to always make sure there are no typos in your formulas, and that you’re always using the correct data. But this might not always be possible so in this article we will help you learn several methods that you can use to deal with the #VALUE error in Microsoft Excel.
Also, check out our favoriteExcel tips and tricks to boost your productivitywith fewer headaches.
What’s Causing the #VALUE Error
There are several reasons why the #VALUE error might happen when you’re using a formula in Excel. Here are some:
When you find what is causing the #VALUE error you’ll be able to decide how to fix it. Now let’s take a look at each specific case and learn how to get rid of the #VALUE error.
Fix #VALUE Error Caused by Invalid Data Type
Some Microsoft Excel formulas are designed to work only with a certain type of data. If you suspect this is what’s causing the #VALUE error in your case, you’ll have to make sure none of the referenced cells use an incorrect data type.
For example, you’re using a formula that calculates numbers. If there is a text string in one of the referenced cells, the formula won’t work. Instead of the result, you’ll see the #VALUE error in the selected blank cell.
The perfect example is when you’re trying to perform a simple mathematical calculation such as addition or multiplication, and one of the values is not numeric.
There are several ways to fix this error:
In the example above we can use the PRODUCT function: =PRODUCT(B2,C2).
This function will ignore the cells with empty spaces, incorrect data types, or logical values. It’ll give you a result as if the referenced value was multiplied by 1.
You can also build an IF statement that’ll multiply two cells if they are both containing numeric values. If not, the return will be zero. Use the following:
=IF(AND(ISNUMBER(B2),ISNUMBER(C2)),B2*C2,0)
Fix #VALUE Error Caused by Spaces and Hidden Characters
Some formulas can’t work if some of the cells are filled with hidden or invisible characters or spaces. Even though visually these cells look empty, they may contain a space or even a non-printing character. Excel considers spaces as text characters, and as in the case of different data types, this might cause the #VALUE Excel error.
In the example above, the C2, B7, and B10 cells seem empty, but they contain several spaces which are causing the #VALUE error when we try to multiply them.
To tackle the #VALUE error you’ll have to make sure the cells are empty. Select the cell and press theDELETEkey on your keyboard to remove any invisible characters or spaces.
You can also use an Excel function that ignores text values. One such is the SUM function:
=SUM(B2:C2)
Fix #VALUE Error Caused by Incompatible Ranges
If you’re using functions that accept multiple ranges in their arguments, won’t work if those ranges are not of the same size and shape. If that’s the case, your formula will result in the #VALUE error. Once you change the range of cell references the error should disappear.
For example, you’re using the FILTER function and you’re trying to filter the range of cells A2:B12 and A3:A10. If you use the =FILTER(A2:B12,A2:A10=”Milk”) formula, you’ll get the #VALUE error.
You’ll need to change the range to A3:B12 and A3:A12. Now that the range is of the same size and shape, your FILTER function won’t have a problem calculating.
Fix #VALUE Error Caused by Incorrect Date Formats
Microsoft Excel can recognize different date formats. But you might be using a format that Excel cannot recognize as a date value. In such a case, it’ll treat it as a text string. If you try using these dates in formulas, they’ll return the #VALUE error.
The only way to deal with this issue is to convert the incorrect date formats to the correct ones.
Fix #VALUE Error Caused by Incorrect Formula Syntax
If you’re using the wrong formula syntax while trying to make your calculations, the result would be the #VALUE error. Luckily, Microsoft Excel has Auditing Tools that’ll help you with the formulas. You’ll find them in the Formula Auditing group in the ribbon. Here’s how to use them:
Excel will analyze the formula you used in that particular cell, and if it finds a syntax error it’ll get highlighted. The detected syntax error can be easily corrected.
For example, if you’re using =FILTER(A2:B12,A2:A10=”Milk”) you’ll receive the #VALUE error because the range values are incorrect. To find where the problem is in the formula, click the Error Checking and read the results from the dialog box.
Correct the formula syntax to read =FILTER(A2:B12,A2:A12=”Milk”) and you’ll fix the #VALUE error.
Fix #VALUE Error in Excel XLOOKUP and VLOOKUP Functions
If you need to search and retrieve data from your Excel worksheet or the workbook, you’ll commonly use the XLOOKUP function, or its modern successorthe VLOOKUP function. These functions can also return the #VALUE error in some cases.
The most common cause of the #VALUE error in XLOOKUP is the incomparable dimensions of the return arrays. It can also happen when the LOOKUP array is bigger or smaller than the return array.
For example, if you’re using the formula: =XLOOKUP(D2,A2:A12,B2:B13), the return will be the #VALUE error because lookup and return arrays contain a different number of rows.
Adjust the formula to read: =XLOOKUP(D2,A2:A12,B2:B12).
Use IFERROR or IF Function to Resolve the #VALUE Error
There are formulas you can use in order to handle the errors. When it comes to #VALUE errors, you can use the IFERROR function or the combination of IF and ISERROR functions.
For example, you can use the IFERROR function to replace the #VALUE error with a more meaningful text message. Let’s say you want to calculate the arrival date in the example below, and you want to replace the #VALUE error caused by the incorrect date format, with the “Check the date” message.
You’ll use the following formula: =IFERROR(B2+C2,”Check the date”).
In case there is no error, the above formula will return the result of the first argument.
The same can be achieved if you use the combination of the IF and ISERROR formula:
=IF(ISERROR(B2+C2),”Check the date”,B2+C2).
This formula will first check if the return result is an error or not. If it is an error it’ll result in the first argument (Check the date), and if not, it’ll result in the second argument (B2+C2).
The only drawback of the IFERROR function is that it’ll catch all types of errors, not just the #VALUE one. It won’t make a difference between errors such asthe #N/A error, #DIV/0, #VALUE, or #REF.
Excel, with its wealth of functions and features, offers endless possibilities for managing and analyzing data. Understanding and conquering the #VALUE! error in Microsoft Excel is a vital skill in the world of spreadsheet wizardry. These little hiccups can be frustrating, but armed with the knowledge and techniques from this article, you’re well-prepared to troubleshoot and resolve them.
Nicolae is a Jack of all trades technology writer with a focus on hardware, programming languages, and AI image-processing software. Over the last five years, he has ghostwritten numerous tech how-to guides and books on a variety of topics ranging from Linux to C# programming and game development. Nicolae loves everything that has to do with technology and his goal is to share his knowledge and experience with others.Read Nicolae’s Full Bio
Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.
HomeAbout UsEditorial StandardsContact UsTerms of Use
Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved
