Why is Excel telling me value?

Sometimes Excel comes across a formula that it cannot calculate. When this happens, it displays an error value. Error values occur because of incorrectly written formulas, referencing cells or data that don’t exist, or breaking the fundamental laws of mathematics.

The #### error occurs when the column isn't wide enough to fit the cell data.

1. Double-click the line to the right of the column letter for the column containing the error.
   

The column width automatically resizes to fit the widest string of text in the column, thus fixing the error.

To resize all columns in the sheet at once, click the Select All button in the upper-left of the worksheet before resizing a column’s width.

You'll see the #NAME? error when the text in the formula isn't recognized. Sometimes it's easy to figure out the error, but other times you'll need help to determine what’s happening. For this example, you’ll use Excel’s Error Checking feature to help fix the problem.

1. Select the cell with the #NAME? error.
2. Click the Formulas tab.
3. Click the Error Checking button.
   

   The Error Checking dialog box opens. The left side of the dialog box shows the formula that’s causing the error and gives a description of what’s happening.

4. Select an error checking option at the right and fix the error.
• Help on This Error: Displays information specific to the error type.
• Show Calculation Steps: Demonstrates all steps leading to the error.
• Ignore Error: Allows you to accept the formula as entered, without Excel displaying the Error Checking Options smart tag.
• Edit in Formula Bar: Allows you to edit the formula that is generating the error in the Formula Bar.
5. Close the dialog box.
   

The #NAME? error in the cell is replaced with the corrected formula.

The #VALUE! error tells you there’s something wrong with the cells you’re referencing or with the way the formula is typed. This is a very general error and it can be tricky to pinpoint the cause of it. This example uses the Trace Precedents feature to help fix the error.

1. Select the cell with the #VALUE! error.
2. Click the Trace Precedents button on the Formulas tab.
   

   Trace Precedents shows dots that indicate which cells affect the value of the currently selected cell. This helps to visually locate the error.

3. Locate the cell that’s causing the error.
4. Correct the formula in the formula bar.
5. Click or press Enter.
   

The formula updates to display the correct result and the #VALUE! error disappears.

You will see the #DIV/0! Error any time a number is divided by zero. This includes typing “/0” in a formula or referencing a cell to divide by that contains 0 or is blank.

1. Select the cell with the error.
2. Click in the formula bar and fix the error.
3. Click or press Enter.
   

The cell updates to the correct result and the #DIV/0! Error is fixed.

You will get the #REF! error when a formula references a cell that’s not valid. This often happens when referenced cells get deleted or pasted over.

Error is a common and integral part of Excel formulas and functions, but fixing those errors is what makes you a pro in Excel. As a beginner, finding those errors and restoring them to work correctly is not easy. Every error occurs only because of a user’s mistakes. So, it is essential to know our mistakes and why those errors are happening. In this article, we will show you why we get #VALUE! Errors in Excel and how to fix them.

For example, the simple formula may return as the #VALUE! Error if a space character in any cell is created to clear a cell. To fix the error, the cell which contains a space character needs to be selected and then pressed the Delete key.

Table of contents

• What is #Value! Error in Excel?
  • How to Fix #Value! Error in Excel?
    • Case #1
    • Case #2
      • Example
    • Case #3
      • Example
  • Things to Remember Here
  • Recommended Articles

How to Fix #Value! Error in Excel?

You can download this Error Value Excel Template here – Error Value Excel Template

Case #1

#VALUE!: This excel errorExcel ErrorErrors in excel are common and often occur at times of applying formulas. The list of nine most common excel errors are - #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #####, Circular Reference.read more occurs for multiple reasons depending upon the formula we use. The most common reason for this error is the wrong data type used in the cell references.

Follow the below steps to fix the value error in Excel.

1. Look at the below formula for adding different cell values.

   
   

   
   
   In the above basic Excel formula, we are trying to add numbers from A2 to A6 in cell A7, and we have got the result of #VALUE! Error. In cell A5, we have a value as “Forty,” which is the wrong data type, so it returns #VALUE!

2. To get the correct sum of these numbers, we can use the SUM function in excel.

   
   

   

3. We get the following result.

   
   

   
   
   The SUM function has ignored the wrong data type in cell A5, adding the remaining cell values and giving the total.

4. Otherwise, we can change the text value in cell A5 to get the correct result.

   
   

   
   
   For the earlier formula, we have only changed the A5 cell value to 4,000. So now, our previous function is working properly.

Case #2

Now, we will see the second case of #VALUE! Error in Excel formulas.

Example

• Look at the below formula.

We have divided column B with column A. And we have got three different results.

• Result 1 says B2/A2. In both cells, we have numerical values, and the result is 80%.

• Result 2 says B3/A3. Since there is no value in cell B3, we have got the result of 0%.

• Result 3 says B4/A4. It is also the same case as Result 2.

• We have got an #VALUE! Error, so curious case.

• The main reason for this error is that the empty cell is not truly blank because there could be an errant space character.

In cell B4, we have a space character that is not visible to naked eyes. However, it is the reason why we have got #VALUE! Error.

We can use the LEN Excel FunctionLEN Excel FunctionThe Len function returns the length of a given string. It calculates the number of characters in a given string as input. It is a text function in Excel as well as an inbuilt function that can be accessed by typing =LEN( and entering a string as input.read more or ISBLANK Excel function to deal with these unnoticeable space characters.

• LEN function will give the number of characters in the selected cell. LEN considers a space character as a single character.

Look at the above function. We have got one character resulting in cell B4, which confirms B4 cell is not an empty cell.

• Similarly, the ISBLANK function shows TRUE if the cell is empty. Otherwise, it shows FALSE.

Look at the above result. We have got FALSE as a result for the B4 cell. So, we can conclude that cell B4 is not an empty cell.

Case #3

Another case of resulting #VALUE! Excel error is because the function argument data type is wrongly stored.

Example

Look at the below image.

• In the above formula example, we have used the NETWORKDAYS Excel functionNETWORKDAYS Excel FunctionThe NETWORKDAYS function is a date and time function that determines the number of working days between two given dates  and is widely used in the fields of finance and accounting. When calculating the working days, NETWORKDAYS automatically excludes the weekend (Saturday and Sunday).read more to find the actual working days between two dates.

• The first two cells got the result, but we have an error result of #VALUE!

The end date in the B4 cell has the value of NA, the non-date value, resulting in #VALUE!

• We need to enter the proper date value in cell B3 to correct this error.

There is also another chance of getting the same error even though we have the date value.

• For example, look at the below cell.

Even though we have a date in cell B4, we still have the #VALUE! Error. In cell C4, a date is not a date stored as a text value, so we need to convert this to a proper date format to get the correct result.

Things to Remember Here

• Other error values are explained in separate articles. This article is dedicated to #VALUE! Error.
• The #VALUE! Error occurs for multiple reasons. We have listed above all the possible scenarios of this error.

Recommended Articles

This article has been a guide to Excel #VALUE! Error. Here, we discuss fixing a value error in Excel with examples and a downloadable Excel template. You may learn more about excel from the following articles: –

How do I get rid of ## in Excel?

How do you change #value to 0?