#VALUE! Error in Google Sheets: 8 Causes and How to Fix Them

#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.

Related guides