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.
We want to determine the total frequency using an addition arithmetic formula.
We use the steps below:
- Select cell B5 and type in the formula below:
=B2+B3+B4
- Press Enter.
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:
- 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.
- Replace the text value with the correct number value, as shown in the example below, where we replaced “fifteen” in cell B3 with “15.”
- 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:
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:
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:
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:
- 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:
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).
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:
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:
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
- Type an equal sign symbol in a cell to start a formula.
- 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:
- Press Ctrl + A to open the Function Arguments dialog box.
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:
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:
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.
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.
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:
- Select cell C2 and enter the below formula:
=IF(ISERROR(A2+B2),"Correct the date",A2+B2)
- Drag or double-click the fill handle feature to copy the formula down the column.
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.
We want to filter the cells with the #VALUE! errors.
This can be done using the following steps:
- Select the cell range C1:C7 with the errors.
- On the Data tab, click the Filter button on the Sort & Filter group.
- 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.
All the cells with the #VALUE! errors are filtered, and you can fix the errors one by one.
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:
