How to Fix the #VALUE! Error in Google Sheets

By Gerard Fernandez · Updated · 2 min read

#VALUE! means a formula received the wrong type of data: usually text where it expected a number. Sheets tells you exactly which value caused the problem, so this error is quick to fix once you read the message.

What the error looks like

In our test, D3 multiplied two cells with =B3*C3, but B3 holds a product name, "Monitor arm", not a number:

Google Sheets cell D3 showing #VALUE! with the tooltip Function MULTIPLY parameter 1 expects number values. But 'Monitor arm' is a text and cannot be coerced to a number
Real test: the tooltip names the function (MULTIPLY), the argument (parameter 1, the first value) and the text that caused it.

Sheets can convert text that looks like a number on its own: ="2"*3 returned 6 in our test. It can't convert a word, a stray space or a unit. ="5 kg"*2 and =" "*2 both gave #VALUE!, naming '5 kg' and ' ' as the problem. With + the message says Function ADD instead.

Common causes and fixes

CauseFix
A word or note in a number column ("two", "n/a", "TBC")Replace it with a number, or leave the cell empty.
A cell that looks empty but contains a spaceSelect the cell and press Delete, or run Data → Data cleanup → Trim whitespace.
Numbers with units typed in, like "5 kg" or "€12"Keep only the number in the cell and add the unit with Format → Number → Custom number format.
A date typed as text that Sheets doesn't recognizeRetype it in your locale's order, or convert it with DATEVALUE. See how to change the date format.

Make formulas more tolerant

Use SUM instead of +. In our test, =B2+C2 with "Desk lamp" in B2 gave #VALUE!, while =SUM(B2:C2) skipped the text and returned 25. The same goes for PRODUCT versus *.

Convert text numbers with VALUE. If numbers were imported as text, =VALUE(C3) turns "3" into 3.

Hide the error when blanks are expected:

=IFERROR(B3*C3, "")

Use IFERROR sparingly: it also hides errors you'd want to see.

Frequently asked questions

How do I find which cell is text?

Text sits on the left of the cell by default, numbers on the right. You can also test a cell with =ISNUMBER(C3).

Is #VALUE! the same as #N/A?

No. #VALUE! is about the wrong data type; #N/A means a lookup didn't find anything, and #NUM! means the number is the right type but impossible for that function.

Why does my SUM return 0 instead of #VALUE!?

Because SUM ignores text. If every "number" is stored as text, the total is 0. See how to sum a column.