What does #VALUE! mean in Excel? It’s undoubtedly the most common question you might have in your mind when it comes to Excel error messages.

Excel shows many error messages when you don’t define its functions accurately as they should be. These errors tell you where the fault is so you can fix that effortlessly. One such error message is the #Value error. If you wish to master Microsoft Excel, you must learn its errors inside out. You can start with the #Value error easily by reading this Excel guide until the end. Let’s get started!

What Does #VALUE! Mean in Excel?

The #VALUE! error in Excel means there is an issue with the formula or data. It occurs when Excel encounters incorrect data types. For example, it appears if you add text to numbers. It also shows up if a cell reference includes a blank or non-numeric value.

This error can occur in functions that require numbers but receive text instead. Excel cannot process such mismatched inputs. Hence, it triggers the #VALUE! so you can take a look at your formula. To fix it, check all formula inputs for proper data types. Ensure your ranges, references, and arguments contain compatible values.

When Does Excel Show the #VALUE! Error?

I’ve done extensive research on Microsoft Excel’s error messages and have found that the #VALUE! error occurs in 13 different Excel functions. In most of these cases, you’ll find that you might have replaced a number argument with a text argument, which generally creates the #VALUE! error message.

Look at the sample dataset’s screenshot shown above. I’ve used the following formula based on the IF function to derive the sales commission for an agent against sales of different products.

=IF(A2>100, A2*10%, A2*5%)

It worked as expected because I entered all arguments as numbers, as defined in the IF functions’s syntax.

However, if I change one of the number input references, A2 to a text value, the IF function can no longer process the data and generates the #VALUE! error.

How to Troubleshoot #VALUE! Error for Excel Functions

Now that you know the reason behind the #VALUE! error in Excel, let me show you the most commonly used Excel functions where you can see this error message:

AVERAGE/SUM Function

The AVERAGE and SUM function will show the #VALUE! error if one of the referred cells in the formula has any Excel formula errors, including the #VALUE! error.

You can either carefully look into the referred cells in the formula and ensure the source cells have no error messages.

However, if you just want to ignore the errors in the source cell and continue with the calculation, that’s also possible. You can combine the AVERAGE function with ISERROR and IF to determine errors and show appropriate responses.

In the sample dataset shown above, there’s a #VALUE! error in B2 and hence, the final average calculation also produces the same error.

To avoid the error, you can use the following formula:

=AVERAGE(IF(ISERROR(B2:B5),"",B2:B5))

Don’t forget to replace the cell range reference B2:B5 according to your own worksheet.

COUNTIF/COUNTIFS Function

The COUNTIF and COUNTIFS function generate the #VALUE! error when the referred range in the formula is located in a workbook that’s now closed.

You can reopen the closed workbook and press F9 on the keyboard to refresh the active worksheet. Now, the error message should go away.

DATEVALUE Function

The DATEVALUE function’s argument date_text only accepts a date in the text format. So, you’ll get an error message if the referred cell has any other number formatting except the text format.

As you can see in the dataset shown above, the input value in A2 isn’t in the text format so the DATEVALUE function is generating an error message.

What you can do is, select the input cell, and go to the Number block in the Home tab.

Click on the existing number formatting drop-down list and choose Text at the bottom of the drop-down menu.

Now, press the F9 key on the keyboard to refresh the Excel worksheet.

The DATEVALUE function will immediately calculate the cell.

This function closely follows the system’s data formatting pattern. If your system shows a date in MM/DD/YYYY format and the input date value in the formula is in DD/MM/YYYY format, you’ll see the Excel #VALUE! error.

So, check what’s the date formatting you’re using on your PC. Now, look up your own database and convert the date format for all the products accordingly.

DAYS Function

The DAYS function might generate the #VALUE! error if the input date is in the text format. It’s just the opposite of the DATEVALUE function explained earlier.

The two arguments of the DAYS function, namely end_date and start_date should be a Short Date or Long Date so that the function can interpret the input and generate the required outputs.

In the example shown above, you might be seeing the #VALUE! error in the cell containing the =DAYS(B2,A2) formula.

Because both the input data are in B2 and A2 are in the text format.

To fix this, you can select the input cell ranges, and click on the Text drop-down menu in the Number block of the Home tab.

Select the Short Date option from the drop-down menu.

Now, press F9 on the keyboard to refresh and recalculate the worksheet.

FIND/FINDB and SEARCH/SEARCHB Functions

The reason for the #VALUE! error in the functions FIND/FINDB and SEARCH/SEARCHB are similar.

When the value indicated in the find_text isn’t available in the within_text string, Excel throws the error message.

For instance, in the dataset shown above, the FIND function produces the error message because I’m trying to find the position of the text string black whereas this text is nowhere to be found in the referred text string.

When I corrected the find_text argument to brown, the formula worked just fine.

Another way for the FIND function to generate the error message is any misconfiguration in the start_num argument.

If the start_num argument is set to 0 or any value more than the character count of the within_text argument, Excel shows the #VALUE! error. So, check that the start_num argument is 1 or not mentioned at all so that the FIND function can work seamlessly.

INDEX and MATCH Functions

When you’re using the INDEX and MATCH functions together in a formula to retrieve a value from the dataset, you must enter the entire formula as an array formula.

If you’re using the Excel for Microsoft 365 desktop app, simply pressing the Enter key will do. However, for earlier Excel editions, you must press Ctrl + Shift + Enter to convert a standard formula into an array formula.

VLOOKUP Function

Excel must have introduced you to the #VALUE! error for the first time when you started exploring the VLOOKUP function.

There are mostly two scenarios when the function shows the #VALUE! error and these are as outlined below:

Firstly, if the lookup_value argument you enter is more than 255 characters, you’ll get an error message.

Secondly, the col_index_num argument is configured incorrectly. The number of columns to the right side of the search column is the Column Index. It tells you which column to return if a match is found.

This error may happen due to a typo in the col_index_num argument. It can also occur if a number less than 1 is accidentally specified as the index value. This often happens if another Excel function inside the VLOOKUP function returns a number like 0 for the col_index_num.

The least value for the col_index_num argument is 1. The number 1 represents the search column. The number 2 refers to the first column to the right of the search column, and so on. For example, if you’re searching in column A, then 1 refers to column A, 2 refers to column B, 3 refers to column C, and so on.

Find above that the VLOOKUP function shows the error message since I’ve configured the Column Index to start searching from 0 instead of 1 or 2.

However, when I changed that to 2, the Item column where the item I’m looking for exists, the formula worked perfectly.

#VALUE! Error With Spaces

If there are spaces or hidden spaces in any cell involved in a formula, Excel will show the #VALUE! error.

To locate all spaces in the worksheet, select the dataset area and press Ctrl + F to launch the Find and Replace tool.

Type a space in the Find what field.

Go to the Replace tab and type nothing in the Replace with field.

Hit Find All to retrieve all cells containing visible or hidden spaces.

Click on the Replace All button to get rid of the unwanted spaces.

Conclusions

Now you know what #VALUE! mean in Excel and how to fix it for popularly used Excel functions.

If you see this error again when working on your dataset, you should be able to resolve it quickly and efficiently.

If the Excel tutorial helped you to learn a new skill, share your thoughts in the comment box.