#VALUE! Error in Google Sheets
What it means: The formula received the wrong type of data — for example text where a number was expected.
Quick fix
=VALUE(A2)
Convert a text-formatted number back to a real number so math works again.
Why you see #VALUE! — and how to fix each cause
1. Numbers stored as text
A cell that looks like 123 may actually be text (often left-aligned, or with a leading apostrophe). Math on text throws #VALUE!.
Fix: Wrap the cell in VALUE(), or select the range and use Format → Number.
=SUM(VALUE(A2), VALUE(A3))
2. Doing math on a cell that contains text
If A2 holds the word "none" and you compute =A2*2, Sheets can't multiply text.
Fix: Filter out or clean the non-numeric cells first, or use functions that ignore text.
=SUMIF(A2:A100, ">0")
3. A date in an unrecognized text format
Date math on a string like "31/13/2026" fails because it isn't a valid date.
Fix: Enter dates in a format Sheets recognizes, or convert with DATEVALUE().
Before and after
| Broken | Working |
|---|---|
=A2*2 (A2 is the text "12") |
=VALUE(A2)*2 |
VALUE() turns text "12" into the number 12 so multiplication works.
How to stop it happening again
Import numeric columns as numbers, avoid leading apostrophes, and use Format → Number on any column you plan to do math with. Left-aligned numbers are a warning sign they are stored as text.
FAQ
Why does my number look fine but still error?
It is probably stored as text. Numbers stored as text are left-aligned by default and won't do math until converted with VALUE() or reformatted.
Is #VALUE! the same as #NUM!?
No. #VALUE! is a wrong-type problem; #NUM! is a valid type but an impossible number (like the square root of a negative).
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas