Correct a #VALUE! error - Excel - Microsoft Office Online

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 6.0 or later, Firefox 1.5, or Netscape Navigator 8.0 or later. Learn more about supported browsers.

Email this link

Printer-Friendly Version Bookmark and Share

Correct a #VALUE! error

Applies to: Microsoft Office Excel 2007

Excel can display the #VALUE! error if your formula includes cells that contain different data types. If smart tags are turned on and you position the mouse pointer over the smart tag, the ScreenTip displays "A value used in the formula is of the wrong data type." You can typically fix this problem by making minor changes to your formula.

Symptom

Excel displays the #VALUE! error in one or more cells in a worksheet.

Causes

One or more cells that are included in a formula contain text, and your formula performs math on those cells by using the standard arithmetic operators (+, -, *, and /).
For example, the formula =A1+B1, where A1 contains the string "Hello" and B1 contains the number 3, returns the #VALUE! error.
A formula that uses a math function, such as SUM, PRODUCT, or QUOTIENT, contains an argument that is a text string instead of a number.
For example, the formula PRODUCT(3,"Hello") returns the #VALUE! error because the PRODUCT function requires numbers as arguments.
Your workbook uses a data connection, and that connection is unavailable.

Example

When you copy the example data to a blank worksheet, the formulas in cells A4 and A5 return a #VALUE! error; however, cells A6 and A7 return the correct value of 30.

Show How do I copy an example?

Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In Excel, create a blank workbook or worksheet.
In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1

2
3
4
5
6
7

A
10
Hello
20
=A1+A2+A3
=SUM(A1+A2+A3)
=SUM(A1,A2,A3)
=SUM(A1:A3)

Resolution

Instead of using arithmetic operators, use a function, such as SUM, PRODUCT, or QUOTIENT to perform an arithmetic operation on cells that may contain text, and avoid using arithmetic operators in the function. Instead, separate the arguments by using commas.
Ensure that none of the arguments in a math function, such as SUM, PRODUCT, or QUOTIENT, contain text as an argument.
If your workbook uses a data connection, take the steps that are required to restore the data connection or, if it is possible, consider importing the data.

Button image Become a fan on Facebook