[Fixed!] VALUE Error in Excel (8 Reasons with Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.

1-Overview of VALUE error in Excel


[Fixed!] VALUE Error in Excel: 8 Reasons with Solutions

To explore the reasons and solutions, we’ll use the following dataset that represents some fruit products’ order dates, quantities, and prices.

2-Dataset for VALUE error in Excel


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.

3-Arithmetic operation returning 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 ignored the text values. The formula is-

=PRODUCT(D5,E5)

4-Using PRODUCT function to ignore VALUE error

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.

5-Arithmetic operation returning VALUE error

Look, there is a space character in cell E5.

6-Hidden spaces in blank cells


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)

7-Using PRODUCT function to avoid VALUE error


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. Unfortunately, the formula returned a #VALUE! error in Excel although the format of the dates is correct.

8-Leading space of a date returning VALUE error

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.

9-Leading space in the date

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)

10-Using TRIM function to remove leading space


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 the ENTER key-

=SUM(D5:D18*E5:E18)

And look, the formula returned a #VALUE! error in Excel.

11-Array formula returning VALUE error


Solution: Press CTRL+SHIFT+ENTER Altogether to Perform Array Formula

Never forget to press the CTRL + SHIFT + ENTER keys together while using an array formula. After applying it, we got the desired result.

12-Pressing the CTRL+SHIFT+ENTER keys altogether to perform array operation


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 it returned the #VALUE! error.

13-Incorrect date format creating 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.

14-Inserting Excel-supported date format


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)

15-Inappropriate ranges creating VALUE error

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.

16-Inappropriate ranges in arguments


Solution: Match Ranges According to Arguments

Always maintain the ranges in the arguments appropriately. After changing the wrong range we got the correct output.

17-Matching ranges in the arguments


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 and they returned a #VALUE! error.

=VLOOKUP(D20,C5:F18,0)
=INDEX(C5:F18,MATCH(D20,C5:C18,0),-1)

The reason is- that the column index number must have been greater than 0, as we inserted 0 and -1 so the #VALUE! error occurred.

18-Incorrect column index number returning VALUE error


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 our index number is 4 according to the selected array, after modifying we got the correct result.

19-Inserting correct column index number


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.

20-Arithmetic formula returning VALUE error


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…

21-Opening  Find and Replace tool

  • After appearing in the Find and Replace tool dialog box, click on Options >> It will open up some other find options.

22-Opening options in Find and Replace tool

  • 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.

23-Setting criteria in Find and Replace tool to find VALUE errors

  • 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.

24-Showing all VALUE errors from the entire sheet


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!")

25-Using IFERROR functions to ignore errors

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.


Download Practice Workbook


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.


Related Articles


<< Go Back To Excel Formula Errors | Errors in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo