Spreadsheet error
How to fix the #VALUE! error
TL;DR
#VALUE! means a formula got text when it expected a number, or the wrong data type somewhere. Fix: find the cell with the text and convert it, or use VALUE()/NUMBERVALUE().
What it means
The #VALUE! error means Excel or Google Sheets cannot make sense of an argument passed to the formula — usually because text was supplied where a number or date was expected. Less commonly, it appears when array formulas receive mismatched array sizes.
Common causes
- •A cell that looks like a number is actually stored as text (left-aligned, with apostrophe prefix).
- •Concatenating a formula result with an operator that expects a number.
- •Date arithmetic on a cell that contains a text date like "3/5/2024" instead of a real date value.
- •Array formula with mismatched range sizes.
- •Passing a range to a function that expects a single cell.
How to fix it
1.Convert text to number
- Use =VALUE(A2) to coerce text into a real number.
- Or =NUMBERVALUE(A2) if the text uses a different decimal separator.
- For dates: =DATEVALUE(A2).
2.Find the culprit cell
- Select the range used by the formula.
- Use Home > Find & Select > Go To Special > Errors to locate the problem cells.
- Or apply IFERROR on individual arguments to narrow it down.
3.Fix at the source
- Select the suspect column.
- Data > Text to Columns > Finish — converts text-numbers to real numbers.
- Or multiply by 1: =A2*1 to coerce.
Example
Broken
=A2+B2Fixed=VALUE(A2)+VALUE(B2)A2 was stored as text (e.g., "10" with apostrophe). VALUE() coerces it to a real number so addition works.
Prevention
- Enforce column data types via Data > Data Validation.
- When importing CSV, use Text to Columns to set explicit types.
- Avoid string concatenation in math expressions — use explicit conversion.
Related errors
Let FormulaPad debug it for you
Paste your broken formula and the error message — get a diff and an explanation in seconds.
Debug a formula