Correct a #VALUE! error

Excel shows this error when your formula includes cells that have different data types (text and numeric values).

The #VALUE! error is shown when a formula references one or more cells that have text instead of numbers, and uses the standard math operators (+, -, *, and /) to add, subtract, multiply, or divide the different data types.

For example, if the formula is =A1+B1, and A1 has text (Hello) and B1 a number (3), the formula returns the #VALUE! error.

To fix this, use a mathematical function, such as SUM, PRODUCT, or QUOTIENT to calculate cells that have mixed data types, and avoid using math operators in the formula For example, use the formula =SUM(A2,A3,A4) instead of =A2+A3+A4 or =SUM(A2+A3+A4). The function will ignore text values in the calculations and no error is shown.

The #VALUE! error is also shown when:

  • A formula uses a mathematical function, such as SUM, PRODUCT, or QUOTIENT, and has an argument that’s a text string instead of a numeric value or cell reference.

    For example, the formula =PRODUCT(3,"Hello") returns the #VALUE! error because the PRODUCT function requires numbers or cell references as arguments.

    To fix this, replace any text strings in the argument section of the function with numbers or cell references.

  • Your workbook uses a data connection that isn’t available.

    To fix this, restore the data connection, or consider importing the data if possible.

Tip    If error checking is turned on in Excel, you can click Button Image next to cell that shows the error. Click Show Calculation Steps if it’s available, and pick the resolution that works for your data.

Applies To: Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel Starter



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Change language