Fixes / Google Sheets · Checked September 30, 2026

Google Sheets #VALUE! "expects number values": how to fix it

Short answer

In Google Sheets, this #VALUE! error means a formula doing math (usually +, -, * or /) got text where it needed a number. Hover over the cell to see which part of the formula failed and the exact bad value in quotes. The usual culprit is a number stored as text or a cell that holds only a space. Clean that cell, or convert it with VALUE(), for example =VALUE(A2)-B2.

What this message means

Google Sheets does not add up text. When a formula does math, each input has to be a real number, date or time. If one input is text that Sheets cannot turn into a number, the cell shows #VALUE!. Hover over the cell and the full message appears, for example: "Function MINUS parameter 1 expects number values. But ' ' is a text and cannot be coerced to a number." The function name tells you which operation failed. The + sign shows as ADD, - as MINUS, * as MULTIPLY and / as DIVIDE. The parameter number tells you which input is the problem (1 is the value left of the operator, 2 is the value on the right). The text in quotes is the exact value Sheets rejected.

Common causes

Cause	How to tell
A number is stored as text, often from a paste, a CSV import, a form, or a cell set to Plain text format	The value sits on the left of the cell instead of the right, and =ISNUMBER(A2) returns FALSE. The quoted value in the error looks like a normal number, such as '120'.
A cell that looks empty actually contains a space or a non-breaking space	The error quotes a blank value, such as ' '. Click the cell and the formula bar shows nothing visible, but =LEN(A2) returns 1 or more.
The number contains currency symbols, units or thousands separators that Sheets does not recognise, such as "$1,200 USD", "5 kg" or "22 166.19"	The quoted value in the error includes extra characters or spaces between the digits.
The spreadsheet's locale does not match the number or date format of the data (for example, 1.234,56 in a US-locale sheet, or 31/12/2025 in a US-locale sheet)	Data from another country or system fails while numbers you typed yourself work. Dates stay left-aligned and date math fails, often as a MINUS error.
The formula range includes a header row or a text label, often in ARRAYFORMULA or whole-column references like A:A-B:B	The quoted value is a column heading or label, such as 'Price' or 'Total'.

How to fix it

1. Read the error tooltip to find the exact bad value

Hover over the #VALUE! cell, or click it, to show the red error box.
Note the function name (ADD, MINUS, MULTIPLY, DIVIDE, or a named function like YEAR) and the parameter number.
Parameter 1 is the first input (the value left of the operator), parameter 2 is the next one, and so on.
Go to that cell and compare what you see with the text in quotes in the error. That quoted value is what needs fixing.

2. Remove stray spaces and blank-looking text

Select the column that feeds the formula.
Click Data > Data cleanup > Trim whitespace to remove leading, trailing and repeated spaces.
If a cell should be empty but still contains a space, select it and press Delete.
Trim does not remove non-breaking spaces (common in data pasted from web pages). For those, use a helper column with =VALUE(SUBSTITUTE(A2,CHAR(160),"")).

3. Convert numbers stored as text into real numbers

In an empty column, enter =VALUE(A2) (use your cell reference) and fill it down, or use =ARRAYFORMULA(VALUE(A2:A100)).
Check that the results are now right-aligned.
Copy the new column, then paste it over the original with Edit > Paste special > Values only (Ctrl+Shift+V on Windows and ChromeOS, Cmd+Shift+V on Mac).
Select the column and choose Format > Number > Number so new entries are not stored as Plain text.
Delete the helper column.

4. Strip currency symbols, units and separators

Press Ctrl+H (Windows and ChromeOS) or Cmd+Shift+H (Mac) to open Find and replace.
In Find, type the unwanted characters (for example $, USD or kg). Leave Replace with empty.
Set Search to the correct range, then click Replace all.
For mixed junk, tick Search using regular expressions, find [^0-9.\-] and replace with nothing. This keeps only digits, the decimal point and the minus sign, so only use it on US-style numbers.
Apply Format > Number > Currency if you want the symbol shown again as formatting.

5. Make the formula skip text and headers

Start the range below the header row, for example A2:A instead of A:A.
To treat text or spaces as zero, wrap the inputs in N(): =N(A2)-N(B2). N returns 0 for text.
To show a blank instead of a result when an input isn't a number: =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2-B2,"").
Use SUM instead of + for totals, for example =SUM(A2:A10). SUM ignores text cells, while + returns an error on them.

6. Match the spreadsheet locale to your data

Click File > Settings.
On the General tab, under Locale, choose the region your data comes from (for example Germany for 1.234,56 or United Kingdom for day/month/year dates).
Click Save settings. Everyone who opens the file will see the new number and date formats.
Re-enter or re-import the data that failed. Values that are already stored as text may need the VALUE step above to convert them.
If you can't change the locale, convert each value with a formula instead, for example =VALUE(SUBSTITUTE(SUBSTITUTE(A2,".",""),",",".")) for 1.234,56 in a US-locale sheet.

Next time, skip the search

Crade is an AI assistant for Mac and Windows that sees your screen. When a message like this pops up, ask “what does this mean?” and it explains the exact error in front of you, step by step.

Download Crade

Still not fixed?

Type =ISNUMBER(cell) and =LEN(cell) next to every cell the formula uses. Any FALSE result, or a length longer than the visible characters, points to the input that needs cleaning. If the data comes from IMPORTRANGE, IMPORTDATA, Google Forms or an add-on (for example a budgeting template), fix the value at its source or restore the template, because the import will bring the text back. If the sheet belongs to your organisation, ask whoever maintains it or your Google Workspace admin. Otherwise, post the formula and the full tooltip text in the Google Docs Editors Community.

Questions people ask

What does "cannot be coerced to a number" mean?

To coerce means to convert automatically. Sheets tried to read the text as a number and couldn't, because it contains letters, symbols, spaces or a format that doesn't match the spreadsheet locale.

Why does SUM work but the + formula shows #VALUE!?

SUM skips text cells in a range, while +, -, * and / need every input to be a number. =SUM(A1:A3) works, but =A1+A2+A3 fails if any of those cells holds text.

The cell looks empty, so why does the error mention ' '?

The cell contains a space, or a non-breaking space from pasted web content. Select it and press Delete, or run Data > Data cleanup > Trim whitespace on the column.

I changed the format to Number but the error is still there. Why?

Changing the format only affects values entered from then on. Values already stored as text stay text until you re-enter them or convert them with =VALUE().

How do I hide the error instead of fixing it?

Wrap the formula in IFERROR, for example =IFERROR(A2-B2,""). This hides every error, including real ones, so fix the data first whenever you can.

Sources

Related errors