Correct a #VALUE! error

Microsoft Excel may display the #VALUE! error if your formula includes cells that contain different data types. If error checking is enabled and you position the mouse pointer over the error indicator, 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.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. 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.

For example, use the formula =SUM(A2,A3,A4) instead of =A2+A3+A4 or =SUM(A2+A3+A4).

  • Ensure that none of the arguments in a math function, such as SUM, PRODUCT, or QUOTIENT, contain text as an argument directly in the function. If your formula uses a function, and that function refers to a cell that contains text, that cell is ignored, and no error is displayed.
  • 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.
 
 
Applies to:
Excel 2010