#VALUE! means a function received a value of the wrong type, almost always text where it expected a number. The good news is that Google Sheets tells you exactly which function and which argument failed. Point at the cell with the error and read the message: Function ADD parameter 2 expects number values means the second thing you added is the problem.
Tested in Google Sheets. We produced each #VALUE! on this page on November 11, 2026, copied the message Google Sheets showed and checked that each fix returns the right number. How we test
Match the message to the cause
| Message (shortened) | Cause |
|---|---|
| … expects number values. But ‘Apple’ is a text and cannot be coerced to a number. | 1. Text in a calculation |
| … But ‘5 ‘ is a text … (note the space) | 2. A hidden non-breaking space |
| DATEVALUE parameter ’31/12/2026′ cannot be parsed to date/time. | 3. A date in a format your sheet doesn’t read |
| VALUE parameter ‘1.234,5’ cannot be parsed to number. | 4. A number with the wrong separators |
| Function SUM parameter 1 expects number values. But ‘a’ is a text … | 5. Text typed directly into a function |
| … value is negative. It should be positive or zero. / … value is 0. It should be greater than or equal to 1. | 6. A negative or zero argument |
| The default output of this reference is a single cell in the same row … | 7. A range where one cell is expected |
| … But ‘TRUE’ is a text … | 8. TRUE or FALSE stored as text |
1. Text in a calculation
The operators + - * / need numbers. =1+"a" returned Function ADD parameter 2 expects number values. But ‘a’ is a text and cannot be coerced to a number. The same happens when a cell holds a word: with “Apple” in A2, =A2+1 returned the same message with ‘Apple’.
Google Sheets does convert text that looks like a number: ="5"+1 returned 6, ="5"*"2" returned 10 and ="2026-01-05"+1 returned the date one day later. So #VALUE! means the text could not be read as a number at all.
Fix: check that the formula points to the right cells. If a column mixes numbers and labels, SUM ignores the labels in a range: =SUM(A2:A6) over a column of words returned 0 instead of an error. But read the warning below before relying on that.
Found in testing: SUM’s habit of skipping text can give a wrong total with no error. In a column with 101, 102, 103, 104 and 105, where only 102 was stored as text, =SUM(B2:B6) returned 413. The correct total, 515, only appeared with =ARRAYFORMULA(SUM(B2:B6*1)), which converts each cell first. If a total looks low, check for numbers aligned to the left: that usually means they are text. Our guide to converting text to numbers lists which formats VALUE can read.
2. A hidden non-breaking space next to the number
This one is easy to miss because the cell looks fine. Normal spaces did no harm in our tests: =" 5"+1 and ="5 "+1 both returned 6. The culprit is the non-breaking space (character 160), which data copied from websites and PDFs often contains. =("5"&CHAR(160))+1 returned Function ADD parameter 1 expects number values. But ‘5 ‘ is a text and cannot be coerced to a number. The space inside the quotes in the message is the clue.
TRIM does not remove it: after TRIM our test value still had 2 characters, and VALUE on it failed with VALUE parameter ‘5 ‘ cannot be parsed to number. What worked was replacing the character first:
=VALUE(SUBSTITUTE(A2, CHAR(160), ""))
That returned the number 5. To clean a whole column, put the formula in a helper column, then copy it and paste it as values over the original. Tabs, line breaks and zero-width spaces need other fixes, which we cover in removing extra spaces when TRIM doesn’t work.
3. A date in a format your sheet doesn’t read
Dates typed as text only work if they match your spreadsheet’s locale. In a sheet set to the United States, =DATEVALUE("12/31/2026") worked, but =DATEVALUE("31/12/2026") returned DATEVALUE parameter ’31/12/2026′ cannot be parsed to date/time. The year-month-day format worked without problems in our tests, for example =DAYS("2026-01-10", "2026-01-01") returned 9.
Fix: check the locale in File > Settings, or rebuild the date from its parts with DATE. Our guide to sorting dd/mm/yyyy dates shows a formula that converts a whole column of day-first text dates.
4. A number with the wrong separators
The same applies to decimal and thousands separators. In a United States sheet, =VALUE("1,234.5") returned 1234.5, but the European style =VALUE("1.234,5") returned VALUE parameter ‘1.234,5’ cannot be parsed to number. Change the spreadsheet locale to match your data, or swap the separators with SUBSTITUTE before converting. If the problem is in a formula you copied rather than in the data, our formula checker and separator converter rewrites it for your locale.
5. Text typed directly into a function
SUM skips text inside a range, but not text you type as an argument. =SUM("a", 1) returned Function SUM parameter 1 expects number values. Date functions behave the same way: =WEEKDAY("x") returned Function WEEKDAY parameter 1 expects number values. Check for quotes around something that should be a number or a cell reference: "A2" is text, A2 is a reference.
6. A negative or zero argument
Several functions reject numbers outside their allowed range, and the message names the limit:
=LEFT("abc", -1): Function LEFT parameter 2 value is negative. It should be positive or zero.=REPT("a", -1): the same message for REPT.=VLOOKUP("Apple", A2:C6, 0, FALSE): Function VLOOKUP parameter 3 value is 0. It should be greater than or equal to 1. Column numbers in VLOOKUP start at 1.
Not every function uses #VALUE! for this: =MID("abc", 0, 1) returned #NUM! with a similar message. These numbers often come from another formula, such as LEN(A2)-5 on a text shorter than 5 characters, so check the cell that calculates them.
7. A range where one cell is expected
=A1:A3+1 returned The default output of this reference is a single cell in the same row but a matching value could not be found. To get the values for the entire range use the ARRAYFORMULA function. Without ARRAYFORMULA, a range in a single-cell calculation is reduced to the cell in the same row as the formula. If there is none, you get #VALUE!.
Fix: to calculate every row, use =ARRAYFORMULA(A1:A3+1) with empty cells below for the result. To get a single total, use a function that takes ranges, such as SUM.
8. TRUE or FALSE stored as text
Real TRUE and FALSE values count as 1 and 0: =TRUE+1 returned 2. The text “TRUE” does not: ="TRUE"+1 returned But ‘TRUE’ is a text and cannot be coerced to a number. This happens with data imported from CSV files or other apps. Convert it with =A2="TRUE", which returns a real TRUE or FALSE.
Should you hide #VALUE! with IFERROR?
Only when you know why it happens and the blank result is correct, for example while a form is half filled. Otherwise the error is useful: it points straight at bad data. Hiding it can turn a broken total into a believable wrong number, like the SUM above. If you only want to hide “not found” lookups, use IFNA instead, as we explain in how to replace #N/A with a blank.