Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the #VALUE! error
Excel & Google SheetsWhat #VALUE! means
#VALUE! means a formula got the wrong kind of input — most often text where it expected a number. Spreadsheets cannot add a word to a number, so they flag the cell.
Common causes
- A cell in a calculation contains text (even a single space looks blank but is text).
- Numbers are stored as text, so math operators reject them.
- A date is stored as text rather than a real date value.
- A function received a range where it expected a single cell, or vice versa.
Example fix
Broken
=A2+B2+C2Fixed
=SUM(A2:C2)SUM ignores any text cell in the range, while the + operator errors on it.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Look for any cell in the formula that holds text instead of a number — a stray label, unit, or space.
- 2Convert text-numbers to real numbers with VALUE(), or by multiplying by 1.
- 3Use SUM instead of + when adding a range, because SUM ignores text cells while + does not.
- 4Clean invisible characters with TRIM and CLEAN if values were pasted from the web.
- 5Check that any date used in math is a real date (right-aligned by default), not text (left-aligned).
Got the same error in multiple cells? Upload your whole sheet and the Auditor will flag every #VALUE! and broken formula at once — free. Pro plans (₹199/$4.99/mo) can apply the verified fixes and download the corrected file in one click.
Frequently asked
Why does + give #VALUE! but SUM does not?
The + operator fails if any operand is text. SUM is built to skip text and blank cells, so it tolerates a stray label in the range.
How do I tell if a number is stored as text?
Text-numbers align to the left and often show a small green triangle in Excel. Real numbers align to the right. Multiply by 1 or use VALUE() to convert.