Everything You Need To Know About #VALUE! Error In Excel

Have you ever encountered the #VALUE? Error in Excel?

The # VALUE error may appear when entering data into your spreadsheet or using formulas. When you see this error, it means something is wrong with your formula or data types.

This blog post will talk about why Excel generates a #VALUE! Error. Moreover, we'll show you how to fix it quickly so you can get back to working on your spreadsheet.

• What Does the #VALUE! Error Mean in Excel?

• Why Does the #VALUE! Error Occur?

• How to Correct the #VALUE! Error in Excel

• Final Thoughts on #VALUE! Error in Excel

• Frequently Asked Questions on #VALUE! Error in Excel

What Does the #VALUE! Error Mean in Excel?

Error prompts are normal and essential parts of Excel formulas and functions. However, it is difficult for beginners to locate and fix errors to make the function work correctly.

Every error appears only because of a user's mistakes. Moreover, understanding the reasons behind them is crucial. 

Why Does the #VALUE! Error Occur?

The first step is determining what causes the #VALUE! Error.

Excel indicates an error with either the formula itself or the cell reference by displaying the #VALUE error message.

The reasons you may encounter a #VALUE error in your formula can vary depending on the formula used. However, some common reasons for this error include:

Incorrect data type.

If you enter the wrong data type for a formula, you will probably get a #VALUE error.

Example:

The Excel SUM function is not able to add text, only numbers. Since the list contains "three," the formula to add the cells results in a #VALUE! Error.

Incorrect cell references.

Correct the formula to an entire list by using the drag-and-drop method. See an example below:

The first formula accurately subtracts two cells.

If you try to drag and drop the same formula to all the cells, you will receive a #VALUE! Error message.

The reason is that one of the cell values used in the second formula needs to be corrected.

The dash (-) in cell B2 causes an error (#VALUE) when Excel tries to subtract it from 10. Excel cannot perform subtraction with a dash symbol.

Wrong function argument.

Suppose you encounter a #VALUE! Error, review your function for correctness. 

The FIND function of Excel enables users to locate the position of a specific character within a text string.

The formula FIND ("B," "green") gives the result 2. Because the substring "b" comes in the second position in the word "green."

Excel displays a #VALUE! Error because the word "blue" does not have the character "j" and its respective position.

How to Correct the #Value! Error in Excel

Let's explore and resolve the #VALUE error. Here are some solutions to fix the #VALUE error:

Check if there are any special characters.

Review your data for non-numbers values, text, or special characters such as spaces or symbols.

In Excel, it is impossible to distinguish between blank cells and cells with space characters. However, you can use the ISTEXT function to recognize them.

Cell A1 appears to be blank, but it contains a space character. When using the ISTEXT function, it returns TRUE, which means that the cell contains a value.

Clear out the cells that contain space characters before running your formulas to avoid the #VALUE error in your spreadsheet.

Replace operators with built-in Excel functions.

Instead of using operators to create formulas, use the built-in functions in Excel. These functions are for disregarding non-textual values.

Remember that cell B16 has a dash (-), which means it is not empty.

Excel has a #VALUE error because it is impossible to multiply 16 with a dash (-).

Now, use the PRODUCT function:

The PRODUCT function successfully returns a value of 16 without any errors.

Verify your data formatting.

In the image below, you'll notice that we used the WEEKDAY function to get the day of the week for a specific date.

The "#VALUE! Error" occurs in the function because cell A16 has a date in the general format. And it is not a valid date for the WEEKDAY function.

Alter the format to date value and try using the WEEKDAY function again.

Change the #VALUE error.

Sometimes, the #VALUE! An error has nothing to do with Excel. 

A workaround is using the IFERROR function, like in the example below.

The IFERROR formula replaces the error message #VALUE with a hyphen (-) by default, but you can enter any value you prefer.

Assess the formula.

If you have tried all other options and still cannot find the reason for the error, use Excel to evaluate the formula and identify the issue. Assessing the formula should be your final solution.

1. Choose a formula that results in a #VALUE error and click on the "Formulas" tab. Then, select "Evaluate Formula."

2. Excel can help identify the source of an error by evaluating the formula.

In our example, adding the cells shows that 20 plus 30 equals 50. But, in the next step, the text gives rise to the error here.

To see if the formula works, try rerunning it after addressing the issue identified by Excel.

Final Thoughts on #VALUE! Error In Excel

Resolving an #VALUE! Error in Excel is an issue many of us have run into at some point. The root cause is often simple to diagnose and fix, whether caused by incorrect cell referencing or formatting errors.

Visit Simple Sheets for more easy-to-follow guides, and remember to visit the related articles section of this blog post.

For the most straightforward Excel video tutorials, subscribe to Simple Sheets on Youtube!

Frequently Asked Questions on #VALUE! Error In Excel

How can I fix a #VALUE? Error in Excel?

IF statements can be complicated, and it is easy to make mistakes. To avoid the #VALUE! Add special functions like ISERROR, ISERR, or IFERROR to your formula.

How can I change from #VALUE! to zero?

1. Select the cells with the #VALUE! Errors.

2. Press Ctrl+H to open the Find and Replace dialog box.

3. Type "#VALUE!" (no quotes) in "Find what."

4. Type "0" (no quotes) in "Replace with."

5. Click "Replace All" to replace all #VALUE!

After following the steps, Excel will show zero instead of #VALUE! in your spreadsheet.

How can I retain the formula in Excel while removing the #VALUE Error?

1. Go to the Home tab and click Find & Select.

2. Click Go To special dialog.

3. Click Constants and uncheck the Logical, Text, and Errors boxes.

After following the steps, Excel will not remove the formulas while removing an #VALUE Error in your spreadsheet.

Related Articles:

The Ultimate Guide: How To Change Date Format In Excel

The Easiest Way to Connect a Slicer to Multiple Pivot Tables in Excel

The Essential Guide On How To Insert A Check Mark In Excel