#VALUE! Error in Excel – How to Fix!

No matter how good you’re with Excel and formulas, sometimes you will end up getting a few error here and there.

And the #VALUE error is one of the common errors you’re likely to come across.

Excel returns the #VALUE! Error when there is something wrong with the cells you are referencing in a formula or the way you typed the formula.

The #VALUE! error is very general, so it may take some work and digging around to identify its cause.

This tutorial describes possible causes of the #VALUE! error in Excel and explains some techniques for fixing the error.

Understanding #VALUE Error in Excel

If you have worked with Excel formulas, I am sure you have been greeted by the #VALUE error already.

In most cases, this happens when the formula expects you to give it a specific type of data, but your input doesn’t match that expectation.

Don’t worry!

Let’s break down why the #VALUE error occurs in your worksheet and what you can do to fix it.

There are a few common reasons for the #VALUE error:

  • Incorrect Data Type: If you use the incorrect data type in a formula, you’ll likely encounter this error. For example, when trying to sum a text value instead of a number.
  • Missing Values: If a referenced cell is empty or contains an unexpected value, Excel might not know how to process it, causing the error.
  • Special Characters: Some formulas are sensitive to special characters. If special characters are present in a cell, they can trigger the #VALUE error.

Imagine you’re trying to calculate a total price by multiplying the number of items with the unit price, but the unit price cell contains a mixture of numbers and text.

Your formula would return a #VALUE error because Excel doesn’t know how to multiply numbers with text.

Fixing the error can often be as simple as correcting the input data or changing the formula itself.

For example, by removing any text or special characters in the cell containing the unit price, your formula can work as intended.

Also read: #NUM! Error in Excel – How to Fix it?

Causes of #VALUE! Error and How to Fix It

Now that you have a good understanding of what is a VALUE error in Excel, let me show you what causes these value errors (using some examples) and how you can easily fix them.

Cause #1: Using Text Strings in Formulas that Expect Number Arguments 

Some formulas in Excel, for example, the addition arithmetic formula, are designed to work with number values; therefore, if you supply them with text values, the formulas return #VALUE! error instead of a total.  

Let’s consider the following frequency table with a text value in cell B3 instead of a number value.

An absentminded data entry clerk may have entered the text value.

incorrect data entry with numbers and text

We want to determine the total frequency using an addition arithmetic formula. 

We use the steps below:

  1. Select cell B5 and type in the formula below:
add the three cells that have number as well as text
  1. Press Enter.
value error as a result of the arithmetic operation

The formula returns a #VALUE! error instead of a total because one of the operands in cell B3 is a text string and not an expected number value.

The arithmetic formula only works with numeric values.

How to Fix

We can use the following techniques to fix the #VALUE! error caused by text string arguments in a formula where number values are expected:

  1. Delete the text string from the target cell range so that only numeric values that can be added remain, as shown in the example below.
delete the text value from the cell
  1. Replace the text value with the correct number value, as shown in the example below, where we replaced “fifteen” in cell B3 with “15.”
replace the text value with the numeric value
  1. Use the Excel built-in functions instead of arithmetic operators. Generally, the built-in functions are designed to disregard data types unsupported by the function.

In this example, we use the SUM function to add all the numbers in the target cell range instead of the arithmetic formula:

use the sum function that ignores the text value and only adds the numbers

Notice that the SUM function has disregarded the text value in cell B3 and added only the numeric values in the cell range B2:B4.

Some Important Things to Know

  • A space character in the target cell range can also trigger the #VALUE! error when you use the arithmetic formula, as seen in the below example:
sell with space or apostrophe also leads to value error

The cell with the space character appears blank, but when you select it and check in the formula bar, you find it has a space character represented by an apostrophe

The space character causes the #VALUE! error because Excel considers it a text string. You can fix this error by deleting all space characters in the target cell range.

  • Sometimes, you may accidentally enter a space between numbers, which can trigger a #VALUE! error as shown in the example below:
space in between numbers can also lead to value error

Notice a space between 9 and 5 in the value in cell A2. The space makes the value a text string, not a number causing the error.

You can fix the error by removing the space between 9 and 5 in cell A2:

correct the space between numbers to get rid of the value error
  • Sometimes, you may accidentally enter a mix of digits and letters in a cell, and this causes the #VALUE! error as seen in the example below:
mix of number and digits can also lead to value error

Notice that in cell A2, instead of entering a nine followed by a zero, someone entered a nine followed by the letter “O.”

The value looks like a numeric value, ninety, but it is not; it is a text string causing the #VALUE! error. You can fix the error by replacing the letter “O” with a zero (0).

correct alphanumeric cell content to only get the numbers to avoid the value error
Also read: #NAME? Error in Excel – How to Fix!

Cause #2: Entering a Formula Incorrectly

If you mistype an arithmetic formula, Excel will return the #VALUE! Error as shown in the below example:

incorrect use of arithmetic operator can also lead to value error

You notice that the arithmetic formula in cell C2 is intended to compute the difference between the values in cells A2 and B2 and has a comma instead of a minus symbol between the operands.

You can fix the error by typing the formula correctly with a minus symbol between the values:

replace incorrect character with the right arithmetic operator

How to Get Help in Finding the Correct Syntax of a Formula

If you do not know the correct syntax of a formula in Excel, you can consult Excel’s built-in help feature:

Use Excel’s Built-in Help Feature
  1. Type an equal sign symbol in a cell to start a formula.
  2. Begin typing the name of the function you want to use, for example, AVERAGE. When the full name of the function appears in the IntelliSense drop-down as below, press the tab key to select the function:
use intellisense to make sure that you're using the right formula without any mispread words
  1. Press Ctrl + A to open the Function Arguments dialog box.
use the function arguments dialog box to insert functions

The Function Arguments dialog box displays the syntax of the function you want to use and explains each argument.

You can use this information to enter the formula correctly.

When dealing with complex formulas, it can be challenging to identify the cause of the value error. In cases like that, it is recommended that you select a segment of the formula and press the F9 key to calculate and return the output of that segment. This process allows you to troubleshoot the formula in parts.

Also read: Excel Showing Formula Instead Of Result

Cause #3: Unsupported Date Formats

Excel supports many date formats, but sometimes you work with datasets with dates in formats not recognized by Excel.

In that case, Excel treats the dates in unsupported formats as text strings. Therefore, if you use such dates in date functions, Excel returns the #VALUE! error.

Let’s consider the dataset below with dates in cells A4, A5, and A7 that are in formats not recognized by Excel:

incorrect date formats can also leads to value error

When we apply the YEAR function in column B to return the years of the dates in column A, we get the #VALUE! error in cells B4, B5, and B7:

year function gives value error where the date is not recognized by Excel

The only way to fix this error caused by unsupported date formats is to ensure the dates are in formats recognized by Excel. 

Note: Dates in Excel are serial numbers where each day is assigned a unique number starting from January 1, 1900, which is assigned the value 1. 

Therefore one way to test whether a date is in a format recognized by Excel is to select an empty cell and enter a formula that adds the value 1 to the date, as shown below.

add one to cell to check whether the date is in the right format or not

If the formula returns a date, then the date is in a format supported by Excel.

Otherwise, the date is in a format not recognized by Excel, and you need need to reenter the date in a format supported by Excel.

Also read: Add Days to Date in Excel?

Suppressing the #VALUE! Error (using IFERROR or ISERROR)

Another way of fixing the #VALUE! error is using the IF and ISERROR functions to suppress the error and display an informative message instead.

Let’s consider the following dataset.

dataset with days that needs to be added to dates

We want to use a formula to add the days in column B to the dates in column A and show the result in column C. The formula suppresses the #VALUE! error if it occurs and displays an informative message instead.

We use the following steps:

  1. Select cell C2 and enter the below formula:
=IF(ISERROR(A2+B2),"Correct the date",A2+B2)
IF and ISERROR formula to handle value errors
  1. Drag or double-click the fill handle feature to copy the formula down the column.
result shows the text correct the date when the date is in incorrect format

Explanation of the formula

=IF(ISERROR(A2+B2),”Correct the date”,A2+B2)

The formula checks whether the result of the calculation of adding days in column B to dates in column A is a #VALUE! error or not.

Suppose the output is a #VALUE! error, the formula returns the second argument, “Correct the date.” Otherwise, it returns the result of the calculation. 

Note: If you are using a newer version of Excel, you can use the IFERROR function instead of combining IF and ISERROR functions. However, remember that the IFERROR function catches many other errors besides the #VALUE! error, for example, the #REF! errors and #DIV/0! errors.

Some Tips for Preventing #VALUE Errors

Below are some useful tips you can use in your day-to-day work to avoid the value error in Excel

1. Check for text or special characters: When using formulas that expect numeric values, ensure that only numeric values are used (as text or special characters can cause this error). If you’re using a cell reference, make sure it contains a numerical value and not text.

2. Use appropriate functions: Instead of using mathematical operations, use functions like SUM(), COUNT(), or AVERAGE() to handle your calculations. For example, if you have a cell containing text and a cell containing a number, instead of using =A1+A2, use SUM(A1:A2), which will ignore any non-numeric values.

3. Check formula syntax: Double-check your formula for any missing or extra characters which could be causing the error. Verify that you’ve constructed the syntax correctly. For example, ensure your nested IF functions are properly formatted.

4. Use Excel Table format: Consider converting your data to an Excel Table format, making it easier to work with and reducing the chance of errors. To convert data into an Excel Table, press Ctrl + T or use the Format as Table button on the Home tab.

With these tips, you can prevent Excel’s #VALUE errors and make your work more efficient and error-free.

How to Filter Cells With #VALUE! Error

If auditing a workbook, you should filter the cells with the #VALUE! errors so that you can quickly find and fix them.

Suppose we have the dataset below with the #VALUE! errors in cell C4, C5, and C7.

data set that has value errors in cells

We want to filter the cells with the #VALUE! errors.

This can be done using the following steps:

  1. Select the cell range C1:C7 with the errors.
select the range that has the value errors
  1. On the Data tab, click the Filter button on the Sort & Filter group.
click the filter icon in the ribbon to apply filters to the column headers
  1. Click the Filter button in cell C1 and deselect all options in the bottom area of the drop-down except the #VALUE! option and click OK.
Uncheck all the other options and only check the value Error option to get only those cells that have the value error

All the cells with the #VALUE! errors are filtered, and you can fix the errors one by one.

only the cells with value errors are filtered and shown

This tutorial described various causes of the #VALUE! error in Excel and gave possible solutions. We hope you found the tutorial helpful. 

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment