#VALUE error in Excel: causes and fixes

Check if data type is valid

To avoid the #VALUE error in Excel, verify that the data type in the referred cell is correct. If a formula or function requires numeric data, make sure the cell contains a number and not text.

A typical example is math operations such as addition and multiplication. When one of the values that you add up or multiply is non-numeric, a #VALUE error occurs:

To fix the error, you can use one of these options:

• Enter the missing numeric values.
• Use Excel functions that automatically ignore text values.
• Write an IF statement that corresponds to your business logic.

In this example, you can use the PRODUCT function:

=PRODUCT(B3, C3)

If one of the referenced cells contains text, logical value, or is empty, that cell is ignored. The result is as if you multiply the other value by 1.

Alternatively, you can build an IF statement like this:

=IF(AND(ISNUMBER(B3), ISNUMBER(C3)), B3*C3, 0)

This formula multiplies two cells only if both values are numeric and returns zero if either cell contains a non-numeric value. For this particular case, it makes perfect sense.

Remove spaces and hidden characters

In some formulas, a cell with errant spaces or invisible characters may also cause a #VALUE! error, as shown in the screenshot below:

Visually, cells such as D3, B7 and C14 may appear to be completely empty. However, they do contain one or more spaces or non-printing characters. In Excel, a space character is considered text, and it can potentially trigger the #VALUE! error. In fact, it is just another case of the previous example, so it can be fixed in a similar manner:

• Ensure that the problematic cells are really empty. To do this, select the cell and press the Delete key to remove any hidden character in it.
• Use an Excel function that ignores text values, such as the SUM function instead of the arithmetic operation of addition.

Verify that referred ranges are compatible

Many Excel functions that accept multiple ranges in their arguments require those ranges to be of the same size and shape. If not, a formula raises the #VALUE error.

For example, the dynamic array FILTER function results in a #VALUE error when the include and array arguments have incompatible dimensions. For example:

=FILTER(A3:B20, A3:A22="Apple")

Once the range references are changed accordingly, the error is gone:

=FILTER(A3:B20, A3:A20="Apple")

Make sure dates are not stored as text

In Excel, dates are typically stored as numeric values. However, some dates in your worksheet may be stored as text strings instead. When this happens, Excel will return the #VALUE! error if you try to perform calculations or operations on these dates, as text values cannot be added, subtracted or somehow else calculated.

To resolve this issue, you need to convert text-formatted dates to valid Excel dates.

Check formula syntax

Another possible cause of a #VALUE error in Excel could be a syntax error in your formula. Excel's Formula Auditing tools can help you identify and correct such issues.

1. Select the cell with the formula that is producing the #VALUE error.
2. On the Formulas tab, in the Formula Auditing group, click on Evaluate Formula or Error Checking.

Excel will step through the formula one section at a time, showing the result of each step. If there is a syntax error, Excel will highlight the specific part of the formula is causing the error. Once you have detected the syntax error, correct it and re-evaluate the formula to ensure that it is now working as expected.

For example, consider the following formula in the dataset below:

=SORT(CHOOSECOLS(A3:B20, 3))

The #VALUE error occurs because the col_num argument (3) of the CHOOSECOLS function is greater than the total number of columns in the referred array (2).

Setting the last argument to 2 resolves the issue and returns the desired result – the Total column sorted from smallest to largest: