The #VALUE! error
The formula expected a number or a date and got text. Often, that text looks just like a number: "1,234.50 €", "12 ", a date pasted from another program.
What it means
#VALUE! means an argument does not have the expected type. The + - * / operators require it strictly; SUM, on the other hand, silently ignores text, which gives a wrong total rather than an error.
Where it comes from
- A number stored as text containing more than digits: a non-breaking space, a "€", a trailing minus sign (1,234.56-).
- A date stored as text: "03/04/2024" imported from a CSV, or a day-month date in a month-day Excel.
- A cell containing a space that looks empty.
- SEARCH or FIND do not find the text: they return #VALUE!, not #N/A.
- DATEVALUE or VALUE get a text they cannot read.
- Ranges of different sizes in an array calculation.
Fixing it
- Spot the text:
=ISTEXT(A2)returns TRUE for a fake number. - Convert the whole column: Convert text to numbers (spaces, symbols, trailing signs), Fix dates for dates.
- For SEARCH: wrap it in ISNUMBER,
=ISNUMBER(SEARCH("urgent",A2)), which returns TRUE or FALSE without an error.
Tools that help
- Convert text to numbers and Fix dates: the most frequent cause, fixed across the whole file.
- Clean a software export: accounting and ERP exports, amounts and dates included.
- Explain a formula: what each function expects.
Frequently asked questions
- Why does =A1+B1 give #VALUE! when SUM(A1:B1) works?
- Addition requires two numbers; SUM ignores text. If A1 or B1 is a number stored as text, the addition fails and SUM returns a total that leaves it out.