Value-Error-In-Excel-Someka-Blog-Featured-Image

How to fix #VALUE! Error in Excel? Reasons, Troubleshoot Tips & Solutions

Excel Tips
Updated on May 13, 2024

Are you annoyed with #Value! Errors in your Excel worksheet? Here’s a complete guide on why you’re getting Value Error in Excel, how to fix them and where to find these errors on your workbook? Troubleshooting tips included!

Table Of Content

1. What’s #VALUE! Error in Excel?
2. What are the reasons of #VALUE! Error in Excel?
3. How to fix #VALUE! Error in Excel?
4. How to find all #VALUE Errors in Worksheet?
5. Conclusion

Let’s start!

1. What’s VALUE Error in Excel?

If you get the #VALUE! error in Excel, it means that there’s something wrong with your formula/functions or your reference cells.

How to fix Excel Errors

So, this error usually shows up when Excel runs into something it can’t understand, like a text string that shouldn’t be in a formula that only needs numbers. Therefore, this error message appears when Excel is expecting a different type of input.

So with this error, Excel tries to say:

I can’t understand this data!

Now, we’ll look deeper into the reasons of why Excel cannot understand your data.

2. What are the reasons of #VALUE! Error in Excel?

The #VALUE! error in Excel can be attributed to a variety of reasons, often stemming from incorrect data types or improper data format. So, here are some common causes:

Text in Formulas Requiring Numbers:

Firstly, one of the most frequent causes of the “#VALUE!” error is the inclusion of text in a cell that a formula expects to be numeric.

For example, if you try to add ‘5’ (as text) to 10, Excel will not understand how to handle this operation.

Date and Time Issues:

Additionally, Excel handles dates and times as serial numbers, and misunderstanding this can lead to the VALUE error in Excel.

For instance, attempting to perform arithmetic operations with a text string that looks like a date (e.g., “March 15”) instead of using Excel’s date value might result in an error.

Wrong Use of Functions:

Also, using a function incorrectly can lead to errors.

For example, supplying an incorrect argument type to a function that expects a different format or more/less data than provided can trigger this error.

Cell References Issues:

Furthermore, incorrect cell references, such as referring to an empty cell in a calculation or using a cell with non-numeric data, can also cause this error.

Mismatched Array Lengths in Array Formulas:

Also, when using array formulas, if the arrays do not match in size or length, Excel might display the “#VALUE!” error because it cannot perform operations on arrays that differ in size.

These are the common reasons behind the #Value! Error. Besides those, you can also have,

Unsupported Characters in Formulas
And/or, using incompatible Excel functions together
Or, other unprecise functions in your formula, which Excel cannot understand.

3. How to fix VALUE Error in Excel?

So, if you understand why you’re getting an error, you can easily fix it.

Let’s have some suggestions to troubleshoot value errors in your spreadsheet:

Troubleshoot 1: Correct Data Types

Firstly, you should ensure that all data used in your formulas are of the correct type. You can convert text that should be numbers using functions like VALUE() or NUMBERVALUE() in Excel.

For example, if a cell contains the text ’12 3′ with an unnecessary space between 2 and 3. But, you can convert it to a number by using NUMBERVALUE() function.

Additionally, if your formula involves dates that are formatted as text, convert them using DATEVALUE() or similar functions to ensure they are recognized as dates.

Tip: Sometimes cells might contain invisible characters like spaces that can cause the “#VALUE!” error. So, you can get rid of these hidden/invisible characters with VALUE(), NUMBERVALUE(), or TRIM() functions.

Troubleshoot 2: Check Array Formula Consistency

If you’re working with array formulas, make sure all arrays involved have the same length and are properly aligned to prevent mismatches.

Troubleshoot 3: Correct Use of Functions

Also, you should review the functions you’re using to ensure they’re being used correctly. So, you can check each argument in the function to make sure it matches the expected data type and format.

Troubleshoot 4: Adjust Cell References

Additionally, you should verify that all cell references are correct. So, you should make sure there are no accidental text entries or errors in referenced cells.

Troubleshoot 5: Replace operators with built-in functions

So, if you use Excel’s built-in functions instead of operators like “+” or “*”, then you’ll not get this value error.

In this case, Excel will ignore the undefined values in your range. So, if you have worries about this issue, we do not recommend you to use that solution.

ALL-IN-ONE Solution: IFERROR

If you know that your data may create errors and do not want to see that warnings on your worksheet, you can use IFERROR () function. Also, this function will give you the possibility to manipulate error values with your preferred values.

Now, we have tried to explained possible solutions for you. So, we’ll continue with how to find the value errors in your spreadsheet.

4. How to find all VALUE Errors in Excel Worksheet?

Suppose that you’re working with a bid data set, and have many #Value! errors. But do not know how to find them?

Here’s the small tricks:

Go To Special Feature:

Firstly, Excel’s “Go To Special” feature is a powerful tool for finding errors.

Under the Home tab, go to Find & Select > Go to Special.

Then, check the box for errors.

Shortcut: You can use F5 shortcut key to open Go To dialog box. For more short cuts, visit our Shortcuts Cheat Sheet.

Now Excel will select all the error cells in your worksheet.

Using Excel Formulas:

Also, you can create a helper column next to your data that checks for errors.

For example, the formula =IF(ISERROR(A1), “Error in this cell”, “”) can be dragged down alongside your data to display a message wherever an error is found.

Additionally, you can combine this helper with a Conditional Formatting rule to highlight the error values. So, you can set up a rule to format cells with the formula =ISERROR(A1) where A1 is the first cell in the range you’re checking. This approach visually flags all errors, making them easy to identify and correct.

Please check out our Conditional Formatting Guide to learn expert tips on this visualization tool.

BONUS TIPS to fix VALUE Errors in Excel

Also, here are two additional tips for advanced Excel users:

BONUS 1: Error Checking Tool in Excel

Yes, Excel has a built-in Error Checking tool.

Go to Formulas > Formula Auditing > Error Checking

Here you can either trace your errors and monitor your error cells with blue arrows.

Or, you can click on the Error Checking which will let you:

take help on the error
show calculation steps
ignore the error
edit the formula

BONUS 2: Evaluate Feature in Excel

This is one of the shining tools of Excel most people do not even know that it exists.

Go to Formula > Formula Auditing > Evaluate Formula

Once you open the Evaluate Formula dialog box, you can track all the steps of your calculations. Excellent tool to find the root causes of your errors.

The main idea to find and fix errors in Excel is to use helper formulas, conditional formatting, built-in formula auditing tools, or format/function/reference fixes.

*ALTERNATIVELY: For those comfortable with VBA, writing a simple macro to search for and report all error cells can automate the process. The macro can loop through all cells in a worksheet and create a report of cells that contain errors, making it easier to address multiple issues simultaneously.

That’s all from the solutions.

5. Conclusion on VALUE Error in Excel

As a regular Excel user, you’re probably getting #VALUE! errors quite often. If you understand the reasons behind this error, you can easily fix it.

We have tried to give you alternative solutions that you can use the most suitable one for your case. Sometimes you can fix this #VALUE! Error in Excel with simply changing the data type, but sometimes you should go through evaluate formula to understand the real reason.

If you want to master your Excel skills, try our unique tool to practice: