How to Fix the #VALUE! Error in Google Sheets
#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:
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
| Cause | Fix |
|---|---|
| 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 space | Select 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 recognize | Retype 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.