Maruf Islam
Excel is a powerful tool for data analysis and manipulation, but it can be frustrating when errors occur in your formulas or functions. One of the most common errors is the VALUE error in Excel, which occurs when a formula or function references a cell with an invalid data type.
This error can prevent your spreadsheet from producing accurate results and may require troubleshooting to fix.
Understanding the causes of the #VALUE! error and how to resolve it can help you avoid data errors and improve the accuracy of your Excel workbooks.
In this article, we’ll show 8 reasons and solutions for the VALUE error in Excel with clear images.
Download Practice Workbook
8 Reasons with Solutions to Solve VALUE Error in Excel
To explore the reasons and solutions, we’ll use the following dataset that represents some fruit products’ order dates, quantities, and prices.
Reason 1: If Text Is Used in Arithmetic Operations During Calculation
Here, we used the basic arithmetic operation to calculate the total price for each order. But for some unit prices, it is returning a VALUE error.
Have a look at the highlighted cells, the cells contain text values instead of numbers, arithmetic operations can’t deal with text values so it’s returning a VALUE error.
Solution: Use PRODUCT Function to Solve VALUE Error
To avoid this situation, use functions instead of direct arithmetic operations.
Here, we used the PRODUCT function to calculate the total price and it’s ignoring the text values. The formula is-
=PRODUCT(D5,E5)
Also, you can use SUM, SUMIF, or any other functions that replace the particular kind of Arithmetic Operations.
Reason 2: Blank Cell Contains Hidden Spaces Work as Text
While performing an arithmetic operation, if we multiply a value by an empty cell then it returns zero because a blank cell means zero. But in the following calculation, we got the VALUE error instead. This happens when spaces remain in the cells, and without selecting the cell it’s not possible to identify it.
Look, there is a space character in Cell E5.
Solution: Use Functions Instead of Arithmetic Formula
Using functions can also rescue us from this kind of situation. Space character works like text values and we already described before functions can ignore text values.
So to multiply the quantity and unit price we used the PRODUCT function again-
=PRODUCT(D5,E5)
Reason 3: Cell Contains Leading Space Before Number or Dates
Here, we subtracted the first order date from the last order date to calculate the total working days of taking orders. But unfortunately, the formula returned a VALUE error in Excel although the format of the dates is correct.
The problem is for the space character again. See, the first date contains a leading space that’s why the formula considers it as a text value.
You will face the same issue while using the other date-related functions like EDATE, and NETWORKDAYS functions.
Solution: Remove Spaces Manually or Use TRIM Function
When you doubt facing this kind of situation, use the TRIM function before every cell reference. It will remove all leading and trailing spaces from the reference cells.
We used the following formula and then it returned the desired result-
=TRIM(B18)-TRIM(B5)
Reason 4: Not Applying Proper Keys While Performing an Array Formula
Before Microsoft Excel 365, we needed to use the CTRL + SHIFT + ENTER keys instead of pressing the ENTER key to perform any array operations in the earlier versions of Excel.
In an earlier version of Excel, we typed the following array formula with the SUM function and pressed only ENTER key-
=SUM(D5:D18*E5:E18)And look, the formula returned a VALUE error in Excel.
Solution: Press CTRL+SHIFT+ENTER Altogether to Perform Array Formula
Never forget to press the CTRL + SHIFT + ENTER keys altogether while using an array formula. After applying it, we got the desired result.
Similar Readings
- How to Fix “Fixed Objects Will Move” in Excel (4 Solutions)
- Excel VBA: Turn Off the “On Error Resume Next”
- How to Fix #REF! Error in Excel (6 Solutions)
- [Fixed] Excel Print Error Not Enough Memory
- [Fixed] Excel Found a Problem with One or More Formula References in This Worksheet
Reason 5: Incorrect Date Format Creating VALUE Error in Excel
If we use any date format that Excel doesn’t support then it will return a VALUE error.
We used the following arithmetic formula to get the total working days, but as the first date is not valid for Excel so it returned the VALUE error.
Solution: Choose the Correct Date Format
The solution is simple, just insert the date using the correct date format.
Look, after using the right format, we got the output without facing any errors.
Reason 6: Inappropriate Range in Arguments of a Function
Some logical functions in Excel need to match the ranges by maintaining the same size, otherwise, they will return a VALUE error.
Here, we applied the SUMIFS formula to return the total price for the carrot but got a VALUE error.
=SUMIFS(F5:F15,C5:C18,D20)
Because the criteria range and the sum range are not the same sizes. This issue may happen and return a VALUE error in Excel while using some other functions like COUNTIFS, SUMPRODUCT, and XLOOKUP functions.
Solution: Match Ranges According to Arguments
Always maintain the ranges in the arguments appropriately. After changing the wrong range we got the correct output.
Read More: Errors in Excel and Their Meaning (15 Different Errors)
Reason 7: Incorrect Column Index Number in Lookup Functions
When we use some lookup functions in Excel like the VLOOKUP, and INDEX-MATCH functions to return output for a lookup value, we need to specify the column index number. According to that index number, the functions return corresponding output from the array.
If you mistakenly insert the wrong or invalid index number then you will get the VALUE error.
Look at the below image, we used the two following formulas to get the total price for the fruit Bran and they returned a VALUE error-
=VLOOKUP(D20,C5:F18,0)=INDEX(C5:F18,MATCH(D20,C5:C18,0),-1)The reason is- the column index number must have been greater than 0, as we inserted 0 and -1 so the VALUE error occurred.
Solution: Insert the Proper Index Number According to Array
Insert the correct index number relative to the array.
As we wanted to get the total price so our index number is 4 according to the selected array, after modifying we got the correct result.
Reason 8: Formula Referring a Cell with VALUE Error Returns VALUE Error
Suppose you have a large dataset or referring to a cell from another sheet, if that cell contains a VALUE error then you will get the VALUE error again if you use that cell in a formula.
We had some VALUE errors in column F and we applied an arithmetic formula to get 5% VAT and got VALUE errors again.
Solution: Eliminate VALUE Errors from the Reference Cells
Remove the VALUE errors by following the described other solutions that match your scenario.
How to Find All Cells with VALUE Error in Excel
To avoid unwanted situations while using a formula or to fix errors, we need to find them first where they are remaining, especially for a large sheet. The Find and Replace tool is very useful for it because it can find out the errors too. Let’s find the errors from the following dataset using this tool.
- Go to the Home ribbon, then click as follows: Editing > Find & Select > Find…
- After appearing in the Find and Replace tool dialog box, click on Options >> It will open up some other find options.
- Type VALUE! in the Find what box.
- Select the Sheet option from the Within box.
- Choose Values from the Look in the dropdown.
- Finally, click on the Find All tab.
- Soon after, the dialog box will show you the result like the following image. It will provide you with the Excel file name, sheet name, cell name, and formula too.
What Is the Ultimate Solution for All Errors? (Using IFERROR Function)
Suppose you already know that there are some errors in your sheet or may make errors after applying the formula but you want to hide them or detect them by a specific text then you can easily handle it by applying the IFERROR function.
Here, we used the following formula to return the text ‘Çheck the Value’ instead of the VALUE errors-
=IFERROR(D5*E5,"Check the Value!")
Note: there is a drawback of using the IFERROR function, it can’t detect the other error- #N/A or #DIV/0, #VALUE, #REF, etc independently. It considers all the errors in the same way.
Frequently Asked Questions
- Why does Excel say #VALUE! error for no reason?
Actually, if the VALUE error occurs then there must be a reason. The #VALUE! error in Excel typically occurs when a formula or function in a cell references an invalid data type, such as trying to perform a mathematical operation on text or a non-numeric value. This error can also occur if a function expects a certain type of input, such as a range of cells, but is instead provided with a single cell or a value that cannot be interpreted.
- Can we face #VALUE! error while summing, multiplication, or dividing in Excel?
Yes, if you use a direct arithmetic formula for summing, multiplication, or dividing in Excel instead of using functions then the VALUE error can occur.
- Can #VALUE! error happens in Excel IF function?
Yes, it is possible to get a #VALUE! error when using the IF function in Excel. This can happen if the formula within the IF statement returns a non-numeric or invalid value, or if one or more of the arguments provided to the IF function are not of the expected data type.
- Can number stored as text return VALUE error in Excel?
Yes, some earlier versions of Excel can return a VALUE error if you insert a number in a formula that is stored as text.
Conclusion
In conclusion, the VALUE error in Excel is a common issue that can occur when a formula or function references an invalid data type or value. This error can be frustrating and can cause inaccurate calculations and analysis.
However, by carefully checking formulas and ensuring that all inputs are correct and in the expected format, users can avoid this error and improve the accuracy of their Excel spreadsheets.
Additionally, using error handling functions and other Excel tools can help to catch and correct any errors that may occur, making it easier to work with large datasets and complex formulas. Leave a comment, if you have further queries or have something to add.
