Excel Formula Not Calculating? Fix It in Order, Fastest First

When an Excel formula refuses to calculate and the cell shows the raw text like =A1+B1 instead of a number, the cause is almost always one of three things: Show Formulas is switched on, the cell is formatted as Text, or the workbook calculation mode is set to Manual – and the fastest fix is to press Ctrl+` (the grave accent key above Tab) to toggle Show Formulas off. This article explains what each cause looks like, how to confirm which one you have, and how to fix them in order from safest to most invasive.

What Actually Stops an Excel Formula From Calculating

Excel does not “break” formulas at random. A cell that displays formula text instead of a result is a display or formatting state, and a cell that shows a stale or zero result is a calculation state. Distinguishing the two saves most of the troubleshooting time.

There are five documented causes, and they account for nearly every case:

• Show Formulas is on. This is a worksheet-level view toggle. Every formula on the sheet displays as text, columns widen automatically, and the results come back the moment you toggle it off.
• The cell is formatted as Text. Microsoft’s own documentation gives the example: enter =2+3 in a cell formatted as Text and all you see in the cell is =2+3. Excel treats the entry as a string, not an instruction.
• Calculation mode is set to Manual. Formulas exist and are valid, but results do not refresh when you change inputs. You see old values rather than formula text.
• The entry never became a formula. A missing equals sign, or a leading apostrophe or space before the equals sign, means Excel never parsed it as a formula at all. Microsoft states plainly that if an entry does not start with an equals sign, it is not a formula and will not be calculated.
• A circular reference. A formula that refers to itself, directly or indirectly. Excel shows a warning the first time one is created in a session, and after you close the warning the cell may display 0 or the last calculated value.

A sixth, less common case: mismatched parentheses. Each opening parenthesis in a function needs a closing one, and Excel will refuse the entry or flag it rather than calculate it.

How to Confirm Which Cause You Have in Under a Minute

Run these three checks before changing anything. None of them modify your file.

1. Look at how many cells are affected. If every formula on the sheet shows as text, it is Show Formulas. If only one cell or one column shows as text, it is cell formatting or a stray character.
2. Click the problem cell and read the formula bar versus the cell. If the formula bar shows =A1+B1 and the cell shows a stale number, the formula is intact and the problem is calculation mode. If the cell itself shows =A1+B1 as visible text and is left-aligned, the problem is Text formatting or a missing equals sign.
3. Check the Number Format box. With the cell selected, look at the Number group on the Home tab. If it reads Text, you have found the cause.

Also glance at the status bar at the bottom of the window. If it reads Circular References followed by a cell address, that is your answer and no amount of reformatting will help.

Symptom to Cause to Fix

Menu paths above are for Excel for Microsoft 365 on Windows and Excel 2021 and 2024 as of August 2026. Excel for Mac uses the same Formulas tab and the same Ctrl+` shortcut.

Fix 1: Turn Off Show Formulas (Safest, Instant, Reversible)

Show Formulas is a view setting. Turning it off changes nothing about your data, so start here.

• Keyboard: press Ctrl+`. The grave accent key sits above the Tab key on most US layouts, sharing a key with the tilde. Press it again to switch back.
• Ribbon: go to the Formulas tab and select Show Formulas in the Formula Auditing group.
• Excel for Mac: identical – Formulas tab > Show Formulas, or Ctrl+`.

Column widths change when you toggle this setting, and they resize again when you toggle back. That column-width jump is a reliable signal that Show Formulas was the culprit.

One version note as of August 2026: in Excel for the web the formula bar shows the formula while the cell shows the result, so the classic Show Formulas behaviour is not the usual explanation for a text-looking formula there. Check cell formatting instead.

Fix 2: Change the Cell From Text to General

If only some cells are affected, the format is the problem. Changing a cell’s number format does not delete anything, but re-entering a formula does overwrite whatever is currently in the cell, so on a workbook you cannot easily reproduce, save a copy first.

1. Select the affected cell or range.
2. Right-click and choose Format Cells, then set the category to General. The ribbon route is Home tab > Number group > format dropdown > General.
3. Changing the format alone does not force recalculation. With the cell selected, press F2 to enter edit mode, then press Enter. The formula now evaluates.

For a whole column, select the column, set it to General, then re-enter each formula, or re-enter the top formula and fill down. Filling down overwrites the cells below it. If those cells contain formulas that differ from the top one, back up the file before filling – this step is not undoable once the workbook is saved and closed.

Removing a Leading Apostrophe or Space

A leading apostrophe forces Excel to treat an entry as text and the apostrophe itself does not display in the cell, only in the formula bar. Click the cell, look at the formula bar, and if you see '=A1+B1 or a space before the equals sign, delete that character and press Enter. This is the single most overlooked cause when exactly one cell in an otherwise healthy column misbehaves.

Fix 3: Set Calculation Back to Automatic

When results are stale rather than displayed as text, the workbook is in Manual calculation mode. Excel offers three modes:

• Automatic – recalculates all dependent formulas every time you change a value, formula, or name. This is the default.
• Automatic except for data tables – the same, but data tables are skipped, which is useful in workbooks where large data tables slow every edit.
• Manual – automatic recalculation is off and formulas update only when you recalculate by hand.

To change it, go to the Formulas tab > Calculation group > Calculation Options and choose Automatic. The same setting lives at File > Options > Formulas > Calculation options under Workbook Calculation.

To force a recalculation without changing the mode:

Be aware that in the Excel desktop apps, changing the calculation option affects all open workbooks, not just the one in front of you. If a colleague’s workbook arrives set to Manual and you open it, you may find your own workbooks behaving differently in the same session. Close other workbooks first if that matters.

Fix 4: Convert Numbers Stored as Text

A formula can be perfectly valid and still return 0 or an unexpectedly small total because its source cells hold numbers stored as text. Excel flags these with a small green triangle in the upper-left corner of the cell.

Back up the workbook before bulk-converting a column. Conversion rewrites cell values in place and cannot be undone after the file is saved and closed.

The documented method:

1. Select the cells you want to convert.
2. Select the error indicator that appears in the top-left corner of the selection, or press Alt+Shift+F10 to open it.
3. Choose Convert to Number.

If the error indicator does not appear, use the VALUE function instead:

1. Insert a new column next to the text values.
2. Enter =VALUE(A2), referencing the first text cell, and fill the formula down.
3. Copy the new column, then use Home > Paste > Paste Special > Values (or Ctrl+Shift+V) to paste the results back over the original column.
4. Delete the helper column.

Step 3 overwrites the original column irreversibly once saved. Verify the converted values look correct before deleting the helper column.

Fix 5: Find and Remove a Circular Reference

A circular reference is a formula that refers to itself, directly or through a chain of other cells. Excel displays a warning the first time one appears in a session, and the status bar can show Circular References along with one cell address.

To locate them, go to the Formulas tab > Formula Auditing group > the arrow next to Error Checking > Circular References. The submenu lists offending cells; selecting one navigates to it. Fix that formula, then check the menu again, because the status bar and menu surface one reference at a time and a second may be waiting behind the first.

Editing or deleting a formula to break a circular reference destroys the original logic in that cell. Copy the formula text into a scratch cell or a note before you change it, so you can rebuild the intended calculation.

Excel does offer iterative calculation, which lets circular references resolve by repeated passes, under File > Options > Formulas > Enable iterative calculation. Turn this on only when the circularity is intentional and mathematically convergent, such as certain engineering or accounting models. Enabling it to silence a warning in an ordinary spreadsheet hides genuine errors rather than fixing them.

When the Formula Bar Is Empty: Hidden and Protected Cells

If a cell produces a result but the formula bar stays blank when you select it, the formula has the Hidden protection attribute applied and the sheet is protected. This is a deliberate design choice by whoever built the workbook, not a fault.

To reveal the formula, go to the Review tab and select Unprotect Sheet. If that button is unavailable, the Shared Workbook feature is likely still enabled and has to be turned off first. Unprotecting may require a password you do not have.

Removing sheet protection exposes every formula and removes edit restrictions the workbook author put in place deliberately. On a shared or company file, confirm with the owner before you unprotect – and never remove protection on a file you are not the owner of.

To stop the formula being hidden again the next time the sheet is protected, select the cells, choose Format Cells > Protection tab, and clear the Hidden checkbox.

Exceptions and When to Stop Troubleshooting the Cell

Not every non-calculating formula is a settings problem. Escalate past cell-level fixes in these situations:

• All formulas across multiple workbooks are affected and none of the fixes above apply. Try opening Excel in safe mode to rule out an add-in, then repair the Office installation from Windows Settings > Apps > Installed apps > Microsoft 365 > Modify > Quick Repair. Quick Repair does not remove your files, but run it with all workbooks closed to avoid losing unsaved edits.
• The formula returns a genuine error value such as #REF!, #VALUE!, or #NAME?. These mean the formula is calculating and reporting a problem with its inputs or syntax, which is a different task from the display and mode issues covered here.
• The workbook came from another system and uses a function your Excel version does not have. Functions like XLOOKUP and dynamic array formulas require newer builds; opening such a file in Excel 2016 or 2019 can surface _xlfn. prefixes in the formula. The fix is a version upgrade, not a settings change.
• The file opens read-only or in Protected View. Formulas may not recalculate until you enable editing. Only enable editing on files whose source you trust.

If the same workbook calculates correctly on another machine, the problem is that Excel installation or its settings, not the file. If it fails everywhere, the problem is in the file.

Excel Formula Not Calculating FAQ

Why does my Excel cell show the formula instead of the answer?

Two causes account for nearly all of these. Either Show Formulas is turned on for the sheet, which you clear with Ctrl+` or the Formulas tab > Show Formulas button, or the individual cell is formatted as Text, which you fix by setting the format to General and then pressing F2 followed by Enter to re-enter the formula. Check how many cells are affected: sheet-wide points to Show Formulas, a single cell points to Text formatting.

Why do my Excel formulas not update when I change the numbers?

The workbook calculation mode is set to Manual, so results only refresh when you trigger a recalculation. Go to the Formulas tab > Calculation group > Calculation Options and select Automatic, or press F9 to recalculate once without changing the mode. Note that in the Excel desktop apps this setting applies to all workbooks currently open in the session.

How do I get Excel to add up a column that keeps returning zero?

The numbers are almost certainly stored as text, which SUM ignores. Look for a small green triangle in the upper-left corner of the cells. Select the range, click the error indicator that appears, and choose Convert to Number. Back up the file first, since the conversion rewrites the cell values in place and cannot be undone once the workbook is saved and closed. If no error indicator appears, use =VALUE() in a helper column and paste the results back as values.

Related reading

• Windows 11 Running Slow? Official Fixes in Order
• iPhone Battery Draining Fast: Causes and Real Fixes
• Excel Showing Formula Instead of Result: How to Fix It in Under a Minute

More on this topic: all Tech How-To articles · full article index