Excel Formula Not Calculating? Six Causes and How to Fix Each

Excel shows the formula instead of the result, or the result never updates. Test the six causes in order, from Show Formulas to Manual calculation.

Founder, Glow Reports. , 4 min read
  • Excel
  • Formulas
  • Data Quality
A spreadsheet grid with one raised orange cell holding a crank handle, a mint green switch turned on and a refresh arrow above
On this page
  1. Why is my Excel formula not calculating?
  2. Why does Excel show the formula instead of the result?
  3. Why are my Excel formulas not updating?
  4. Why does my SUM formula return 0?
  5. What if Excel warns about a circular reference?
  6. Does Google Sheets have the same problems?
  7. Where does Glow Reports fit?
  8. Common questions

You type a formula, press Enter, and Excel shows the formula itself instead of the result. Or the result sits there and never changes when you edit the numbers it depends on. Both come down to six causes, and five of them take seconds to fix: Show Formulas turned on, a cell formatted as Text, a missing equal sign, calculation set to Manual, numbers stored as text, and a circular reference. Test them in the order below. Each check rules out a cause before you touch the next one.

Why is my Excel formula not calculating?

Start with how it fails. If the cell shows the formula text, the sheet is in Show Formulas mode, the cell was formatted as Text, or the entry is missing its equal sign. If the cell shows a result that doesn't update, calculation is set to Manual. A result of 0 points to text numbers or a circular reference.

SymptomLikely causeFix
Every formula on the sheet shows as textShow Formulas is onTurn off FormulasShow Formulas
One cell or one column shows the formulaThe cells were formatted as TextSet the format to General, then F2 and Enter
The formula shows, the format is GeneralNo equal sign, or an apostrophe before itRetype it starting with
Results don't change when the inputs changeCalculation is set to ManualFormulasCalculation OptionsAutomatic
A total returns 0 or skips rowsNumbers stored as textConvert them to numbers
0 or an old value, and a warning on openingCircular referenceFormulasError CheckingCircular References

On a forum thread about a large workbook that stopped updating, the replies went through these same checks one by one: Show Formulas, text-formatted cells, Manual calculation, circular references Microsoft Tech Community.

Why does Excel show the formula instead of the result?

Because the sheet is set to display formulas, or because Excel stored your entry as text. Show Formulas affects every formula on the sheet at once. Text format affects only the cells that carried it before you typed.

Show Formulas. Microsoft's checklist for broken formulas starts here: the FormulasShow Formulas button in the Formula Auditing group switches the whole sheet between results and formulas, and so does Ctrl with the backtick key Microsoft Support. The backtick key sits under Esc on most keyboards, so it is easy to press by accident. A sheet in this mode also widens its columns. That is the quickest tell.

Text format. A cell formatted as Text keeps whatever you type as text, formulas included. Microsoft's example is typed in a Text cell: Excel can't calculate it Microsoft Support. Changing the format alone doesn't fix it. Set the cell to General with HomeNumber FormatGeneral, then press F2 and Enter so Excel reads the entry again. For a whole column, select it, change the format, then retype the first formula and fill it down.

This happens most with exported data. A column imported as Text from a CSV keeps that format, and every formula you add beside it in that column inherits it. If your exports open badly in the first place, fixing a CSV that opens in one column covers the import settings.

No equal sign. An entry that doesn't start with isn't a formula Microsoft Support. The same goes for a space or an apostrophe typed before the equal sign. The apostrophe is invisible in the cell. Click the cell and look at the formula bar.

Why are my Excel formulas not updating?

Calculation is set to Manual. In that mode Excel recalculates only when you ask, so results stay frozen at their last values. Go to FormulasCalculation Options and pick Automatic, which recalculates every dependent formula each time you change a value, a formula or a name.

Microsoft lists three options: Automatic, the default, Automatic Except for Data Tables, and Manual. In the desktop apps, changing the option affects all open workbooks. In Excel for the web, it affects the current workbook only Microsoft Support. So on a desktop a workbook can land on Manual without anyone opening its settings. The option changed while another file was open beside it.

When you need to stay on Manual for a slow workbook, recalculate by hand:

  • F9 recalculates the formulas that changed, and their dependents, in all open workbooks.
  • Shift+F9 does the same for the active sheet only.
  • Ctrl+Alt+F9 recalculates every formula in every open workbook, changed or not.

Ctrl+Alt+F9 is the one to try when Automatic is on and a value still looks stale. One reply on the forum thread above reports it fixing a workbook that stopped updating after an Excel update.

Why does my SUM formula return 0?

The numbers it adds are stored as text. They look like numbers, but SUM and other math functions skip them, so a column of text numbers totals 0 and a mixed column gives a total that's too low. A green triangle in the corner of the cells is the usual sign.

Select the cells, open the warning icon next to them and choose Convert to Number. The green triangles disappear. In a helper column, returns the number held in the text Microsoft Support. Then paste the results back as values.

Text numbers come from exports more than from typing: thousands separators from another locale, a currency sign, a trailing space. The cases and their fixes, including the nonbreaking space that TRIM misses, are in how to clean data in Excel. To check a column before you trust a total, compare with . COUNT counts only true numbers, so a gap between the two is the number of text cells.

What if Excel warns about a circular reference?

A formula refers to its own cell, directly or through other cells, so Excel can't settle on a result. The cell shows 0 or its last calculated value. Find the cell with FormulasError CheckingCircular References, then change the formula so it no longer includes itself.

The status bar at the bottom of the window also shows "Circular References" with one cell address. If the loop sits on another sheet, the label appears without an address Microsoft Support. The classic case is a total that includes its own row, such as typed in B20. Change it to .

Microsoft's page explains iterative calculation, which lets a loop run a set number of times. Leave it off unless you built the loop on purpose for a financial model. With it on, a real mistake stops showing a warning.

Does Google Sheets have the same problems?

Mostly. Google Sheets has a calculation setting too: under FileSettingsCalculation, Automatic mode applies changes as soon as you edit, and Manual mode calculates only when you select Calculate at the top of the screen Google Docs Editors Help.

The other causes carry over. A formula without its equal sign stays text. A number stored as text is skipped by SUM. To show or hide every formula, Google lists Ctrl with the tilde key Google Docs Editors Help. On most keyboards it is the same key as in Excel.

Where does Glow Reports fit?

Every fix above works in your own copy of Excel or Google Sheets, and once the cause is clear it takes a minute. The trouble is that exports bring the same problems back every month: the column imported as text, the totals that come out as 0, the workbook someone left on Manual.

With Glow Reports, you upload the CSV or Excel export and ask for the figures in plain language. A CSV upload detects its separator, comma, semicolon or tab. The AI writes the SQL, builds the charts and drafts the text, and the SQL sits under each figure, so you see exactly what was summed and can edit the query. Reports export to PDF and stay in the workspace. The free plan gives 300 credits once, and a report costs 100 credits.

Common questions

How do I make Excel calculate formulas automatically?

Go to FormulasCalculation Options and select Automatic. In the desktop app this applies to every open workbook. Press F9 once afterwards if some results still look old.

Why does my Excel formula show as text?

The cell was formatted as Text before you typed the formula, or the formula starts with a space or an apostrophe. Set the cell to General, press F2, then Enter. If every formula on the sheet shows as text, press Ctrl with the backtick key, under Esc, to turn off Show Formulas.

What is the shortcut to recalculate in Excel?

F9 recalculates what changed in all open workbooks. Shift+F9 does the active sheet only. Ctrl+Alt+F9 recalculates every formula, changed or not, and is the one to try when results look stuck.

Why does my formula work in one cell and not in the one next to it?

The second cell has a different format, usually Text, or its formula has a hidden apostrophe. Click both cells and compare the number format on the Home tab and the formula bar.

Why does Excel show 0 instead of the result?

The values are numbers stored as text, which SUM skips, or the formula is part of a circular reference. Check for green triangles in the source cells first, then look at the status bar for "Circular References".

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your export to Glow Reports and ask for the figures, with the SQL behind each one.

Keep reading