Excel formula not calculating or updating: how to fix it

Fix 1: Calculation is set to Manual

This is the cause when every formula in the file seems frozen: you change a quantity and the total stays the same, but double-clicking the formula cell and pressing Enter suddenly gives the right answer. Look at the status bar at the bottom of the window. If it says Calculate, Excel knows results are out of date and is waiting for you.

The setting catches people because it travels. Calculation mode is stored in the workbook, and Excel takes the mode from the first workbook you open in a session. Open a file a colleague saved in Manual mode first, and every workbook you open after it in that session also stops recalculating.

1. Go to Formulas > Calculation Options and pick Automatic.
2. If it was already Automatic but a big data table is stale, check it is not set to Automatic Except for Data Tables, which skips What-If data tables.
3. Save the file, so the next person who opens it first does not inherit Manual mode.

Fix 2: Recalculate now with F9, Shift+F9 or Ctrl+Alt+F9

Some people keep huge models in Manual mode on purpose, because recalculating takes seconds every time a cell changes. Then you trigger calculation yourself. F9 recalculates the formulas that need it in all open workbooks. Shift+F9 does only the active sheet, which is faster on a large file.

Ctrl+Alt+F9 forces a full calculation of every formula in every open workbook, whether Excel thinks it changed or not. Use it when F9 seems to do nothing, for example after you pasted values in from another program. If you work in Manual mode, also check File > Options > Formulas > Recalculate workbook before saving, so a saved file never contains stale numbers.

Fix 3: The total ignores numbers stored as text

Sometimes the formula does recalculate, but the answer is still wrong. In this order sheet the pens and sticky-note quantities were typed into cells formatted as Text, so they hold the text "6" and "10". SUM skips text, so the Total qty in F2 says 9 when you ordered 25 items. Typing a new number into B4 changes nothing, which looks exactly like a formula that is not updating.

The confusing part: the line totals in D4 and D5 are correct, because multiplication turns text that looks like a number into a number. So some formulas work and others don't. Count how many quantities are real numbers with COUNT, which only counts numbers, against COUNTA, which counts anything.

Find the text cells with ISTEXT

ISTEXT returns TRUE for a cell that holds text. Inside FILTER it lists the items whose quantity is stored as text, so you know exactly which cells to fix. In versions without FILTER, put =ISTEXT(B2) in a spare column and fill it down. Text-formatted numbers also sit on the left of the cell and usually show a small green triangle.

To fix them, select the cells, set Home > Number Format to General, then click the warning icon and choose Convert to Number. For a whole column, Data > Text to Columns > Finish converts every cell at once. Our guide on numbers stored as text covers more ways.

Fix 4: Show Formulas is on, or the cell is formatted as Text

If cells show the formula itself, such as =SUM(B2:B6), instead of a result, the formula is not being calculated at all. When it happens on the whole sheet, Show Formulas is on: press Ctrl+` (the key under Esc) or click Formulas > Show Formulas to turn it off. It is set per sheet, so other tabs can look normal.

When only one cell or one column shows its formula, that cell was formatted as Text before you typed the formula. Change the format to General and re-enter the formula with F2 then Enter; changing the format alone is not enough. Our guide on formulas showing as text covers this cause in detail.

Fix 5: A circular reference, or iterative calculation hiding one

A circular reference is a formula that depends on its own cell, for example a total in G2 written as =SUM(D2:G2). Excel warns you when you create one, then usually shows 0 and stops calculating that chain. The status bar shows Circular References with the cell address, and Formulas > Error Checking > Circular References lists the cells so you can jump to each one and fix the range.

If the warning never appears and numbers drift or stop part way, check File > Options > Formulas > Enable iterative calculation. With it on, Excel accepts circular references and recalculates them up to Maximum Iterations (100 by default) or until results change by less than Maximum Change (0.001). Like the calculation mode, this setting can come in with a workbook someone else saved. Unless you built the loop on purpose, turn it off and fix the reference.