Fixes / Google Sheets · Checked

Google Sheets Formula parse error: what it means and how to fix it

Short answer

"Formula parse error" means Google Sheets cannot read the formula as written, so the cell shows #ERROR!. The usual cause is a typing problem: a missing or extra bracket, a missing comma, unclosed quotes, or the wrong separator for your region. If the formula came from a guide written for another country, try replacing commas between arguments with semicolons (;).

What this message means

Before Google Sheets calculates a formula, it reads (parses) the text to work out the functions, arguments and operators. If the text breaks the grammar Sheets expects, it stops and puts #ERROR! in the cell. Hover over the cell to see the note "Formula parse error." This is a syntax problem, not a data problem. Your numbers and references might be fine. The formula just isn't written in a form Sheets can read. This separates it from #VALUE!, #REF!, #N/A and #NAME?, where Sheets reads the formula without trouble but can't produce a result.

Common causes

CauseHow to tell
Wrong argument separator for the spreadsheet's locale (comma vs semicolon)The formula came from a tutorial, AI answer or colleague in another country. Every comma between arguments is flagged, or the formula works in one file but not in another that has a different locale under File > Settings.
Unbalanced parentheses or bracketsWhile you edit the cell, Sheets colours matching bracket pairs. One bracket has no partner, or the formula ends before the last function is closed.
Unclosed quotation marks or curly (smart) quotes around textThe formula was pasted from Word, Google Docs, email or a web page, and the text looks like “Yes” instead of "Yes". Or a text value is missing its closing quote.
Missing operator between values, such as no & when joining textTwo pieces sit next to each other with nothing between them, for example =A1" "B1 or ="Total: "SUM(B2:B10).
Array literal written with the wrong separatorsThe formula has curly braces like {1,2;3,4} and fails only in a spreadsheet whose locale uses a comma as the decimal mark (for example 1,50 €).
Text that starts with =, + or - is treated as a formulaYou typed a label, phone number or note such as "=== Notes ===", "+44 20..." or "-- done" and got #ERROR! even though you didn't mean to write a formula.

How to fix it

1. Match the separators to your spreadsheet's locale

  1. Open File > Settings and look at Locale on the General tab.
  2. If the locale uses a comma as the decimal mark (Germany, France, Spain, Italy, Brazil and many others), separate function arguments with semicolons: =IF(A1>10;"High";"Low") instead of =IF(A1>10,"High","Low").
  3. In those locales, write decimals with a comma inside formulas, for example 0,5 instead of 0.5.
  4. Or, if you prefer comma-separated formulas, change Locale to a region such as United States or United Kingdom and click Save settings. This also changes the default date, number and currency formats in that file.

2. Check brackets, commas and quotes piece by piece

  1. Double-click the cell (or select it and press F2 on Windows, or Enter on Mac) to open the formula for editing.
  2. Move the cursor next to each bracket. Sheets highlights its matching partner. Add any missing closing ) at the correct spot.
  3. Make sure every argument is separated by one comma (or semicolon, depending on locale), with no doubled or trailing separators such as ,, or ,).
  4. Make sure every piece of text is inside a pair of straight double quotes, for example "Paid".
  5. Press Enter. If #ERROR! is still there, delete the outer function and test the inner part alone, then add the layers back one at a time.

3. Replace curly quotes after pasting a formula

  1. Open the cell and look for “ ” or ‘ ’ characters around text.
  2. Delete each one and retype it as a straight quote (") with the keyboard in Sheets.
  3. For many cells, use Edit > Find and replace (Ctrl+H on Windows, Cmd+Shift+H on Mac), tick "Also search within formulas", and replace “ and ” with ".
  4. In the future, paste formulas as plain text (Ctrl+Shift+V on Windows, Cmd+Shift+V on Mac) or retype the quotes.

4. Add & when joining text and values

  1. Find places where a cell reference, text string or function sits next to another with nothing in between.
  2. Put & between every piece you want to join: =A1&" "&B1.
  3. For a label with a calculation, use ="Total: "&SUM(B2:B10).
  4. Or use CONCATENATE or TEXTJOIN, which take ordinary comma-separated arguments.

5. Fix array literal separators

  1. In most locales, commas separate columns and semicolons separate rows: ={1,2;3,4}.
  2. If your locale uses a comma as the decimal mark, use a backslash between columns instead: ={1\2;3\4}.
  3. Apply the same rule to combined ranges, for example ={A1:A5\B1:B5} to put two columns side by side in those locales.
  4. Make sure each row of the array has the same number of columns.

6. Stop plain text from being read as a formula

  1. Select the cell showing #ERROR!.
  2. Retype the entry with an apostrophe in front, for example '=== Notes === or '+44 20 7946 0000.
  3. Press Enter. The apostrophe is hidden in the cell and the entry is stored as text.
  4. For whole columns of such entries, select them and choose Format > Number > Plain text before typing.

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?

Select the cell and look at the formula bar. Sheets shows a red highlight or an error tooltip near the part it can't read. Choose View > Show > Formulas (Ctrl+` on Windows, Cmd+` on Mac) to see every formula in the sheet at once and compare a working one with a broken one. If the formula came from Excel, check for Excel-only syntax. Rebuild the formula in a blank cell, one function at a time, and let the formula suggestions guide the argument order (turn them on under Tools > Autocomplete). If the file is shared or generated by an add-on or script, ask its owner or the add-on developer. Parse errors in generated formulas usually come from the tool writing the wrong separators for your locale. You can also post the exact formula and your locale in the Google Docs Editors Help Community.

Questions people ask

What is the difference between #ERROR! and Formula parse error?

They are the same problem. #ERROR! is what the cell shows, and "Formula parse error" is the explanation you see when you hover over or click the cell.

Why does my formula work in Excel but give a parse error in Google Sheets?

The most common reason is the separator. Your Excel may use commas while the Sheets file's locale expects semicolons, or the other way round. Some Excel-only syntax also doesn't parse in Sheets, so check the formula against Sheets' own function help.

Should I use commas or semicolons in Google Sheets formulas?

It depends on the spreadsheet's locale under File > Settings. Locales that use a period as the decimal mark (US, UK) use commas between arguments. Locales that use a comma as the decimal mark (most of Europe, Brazil) use semicolons.

Can IFERROR hide a formula parse error?

No. IFERROR only catches errors that happen during calculation, such as #N/A or #DIV/0!. A parse error means Sheets can't read the formula at all, so you have to fix the syntax.

Why do I get a parse error when typing a phone number or a note?

Anything that starts with =, + or - is treated as a formula. Put an apostrophe in front (for example '+1 555 0100) or format the cells as Plain text first.

Sources

Related errors