"There's a Problem With This Formula": Fixes

Excel shows "There's a problem with this formula" when what you typed starts like a formula but breaks Excel's formula rules, so it can't be entered at all. The cause is almost always a small typing slip: a missing bracket or quote, the wrong separator, an operator Excel doesn't know, or ordinary text that happens to start with = or -. This guide lists each cause with a broken and a fixed example, so you can match your formula and fix it fast.

What the message means#

The message is Excel refusing to accept the formula. It is different from an error result like #VALUE!, #REF! or #NAME?. Those appear in the cell after Excel has accepted a formula and tried to calculate it. Here, Excel can't even work out what the formula says, so it stops you before you press Enter successfully.

When you click OK, Excel puts you back in the cell and often highlights the part it couldn't read. Fix that part and press Enter again. If you want to give up and keep the old content, press Esc.

Here is a quick guide to the most common causes:

Cause 1: Missing or extra brackets#

Every opening bracket needs a closing one. In a nested formula, it is easy to lose one:

=ROUND(AVERAGE(B2:B6,0)

Here the bracket after B2:B6 is missing. Excel reads 0 as a second number for AVERAGE, and ROUND is left without its closing bracket. Sometimes Excel offers to add a missing bracket at the end for you; check its suggestion before you accept, because it may not put the bracket where you meant. The correct formula is:

=ROUND(AVERAGE(B2:B6),0)

Excel colours each pair of brackets as you type, and briefly bolds the matching bracket when you type a closing one. Use that to check pairs. Building a nested formula from the inside out also helps: write =AVERAGE(B2:B6) first, check it, then wrap ROUND around it.

Cause 2: Missing quote marks around text#

Text inside a formula must sit between two double quotes. Lose one, and Excel treats the rest of the formula as text:

=IF(A2="Yes,1,0)

Excel sees the text Yes,1,0) that never ends. Close the quote straight after the word: =IF(A2="Yes",1,0). Curly "smart" quotes pasted from a document or email cause the same problem. Retype them in Excel as straight quotes.

If you leave out both quotes, Excel usually accepts the formula but shows #NAME?, because it reads the word as a name it doesn't know.

Cause 3: Commas vs semicolons#

Excel separates function arguments with a list separator that depends on your computer's regional settings. Most English settings use a comma. Many European settings use a semicolon, because the comma is their decimal mark. Type the wrong one and you get the message:

=SUM(B2;B6)

If formulas copied from websites keep failing, this is the likely reason. Look at a formula that already works in your file, or start typing a function and read the tooltip under the cell: it shows the separator your Excel expects. All formulas on this site use commas.

Cause 4: Operators Excel doesn't know#

Excel has a short, fixed list of operators. Habits from maths or programming often slip in:

• Use >= and <=, not => or =<.
• Use <> for not equal, not != or ≠.
• Use * to multiply, not x. Use / to divide, not ÷.
• Don't put two operators side by side, such as =B2*/C2.
• Don't type thousands separators or currency symbols in a number: write 1000, not 1,000 or £1,000.

The Microsoft list of calculation operators shows every valid one.

Cause 5: Sheet names with spaces#

When you refer to another sheet whose name contains a space or a symbol, the name must be wrapped in single quotes:

='Sales Data'!B2

Without the quotes, =Sales Data!B2 breaks at the space. The easiest way to avoid this is to click the cell on the other sheet while typing your formula. Excel then writes the reference, quotes and all, for you.

Cause 6: Text that starts with =, + or -#

Excel treats anything that starts with =, + or - as a formula. Notes like -- call back, headings like === Totals ===, or phone numbers like +44 20 7946 0000 are not valid formulas, so you get the message.

To type them as text, put an apostrophe first: '+44 20 7946 0000. The apostrophe doesn't show in the cell. For a whole column of phone numbers, format the cells as Text before you type. If your problem is the opposite, a formula that shows as text instead of calculating, see why Excel shows the formula instead of the result.

How to find the mistake in a long formula#

If the cause isn't obvious, work through the formula in parts:

1. Copy the formula text from the formula bar to a spare cell, with an apostrophe in front, so you don't lose it.
2. Rebuild it one function at a time, pressing Enter after each step to check it is accepted.
3. Turn on Show Formulas (Windows: Ctrl+; Mac: Control+) to compare it with similar formulas on the sheet.

Once a formula is accepted, other problems can still appear. A formula that refers to its own cell causes a circular reference warning, and a formula that won't recalculate is covered in Excel formula not updating. Small green triangles in a corner are warnings rather than errors; the green triangle guide explains them.

Practise it#

The fastest way to stop these slips is to type lots of correct formulas. Practise your first formulas in a live spreadsheet: you type each one and it is checked instantly. Then work through debugging a SUM formula to practise finding and fixing broken formulas, and try the ROUND exercise on this page.