Excel Formula Not Updating? Check These Causes

If your Excel formula is not updating when you change a number, the most likely cause is that calculation is set to Manual. Other causes look the same from the outside: a formula built from typed-in numbers, a range that misses new rows, or inputs stored as text. This guide takes you through each cause in the order to check it, with the fix for each.

Why is my Excel formula not updating?#

Excel normally recalculates every formula the moment one of its inputs changes. When a result stays the same, either Excel isn't recalculating, or the formula doesn't depend on the cell you changed.

Work through this checklist from the top:

Cause 1: Calculation is set to Manual#

In Manual mode, Excel only recalculates when you tell it to. Everything looks normal, but results go out of date as soon as you change an input. The word Calculate may appear in the status bar at the bottom of the window as a reminder.

To switch back to Automatic:

1. Go to the Formulas tab.
2. Click Calculation Options in the Calculation group.
3. Choose Automatic.

On a Mac, the same Calculation Options button is on the Formulas tab. You can also set it in File > Options > Formulas on Windows, or Excel > Settings (called Preferences in older versions) > Calculation on a Mac.

If you need to stay in Manual mode, for example because a very large file is slow, recalculate when you are ready:

• F9 recalculates all open workbooks.
• Shift+F9 recalculates the active sheet only.
• Ctrl+Alt+F9 recalculates every formula, whether or not Excel thinks it changed.

On a Mac laptop you may need to hold fn to use the F keys.

Why Excel turns Manual on by itself#

The calculation mode belongs to the Excel session, not to each file. Excel takes it from the first workbook you open. If a colleague saved a file in Manual mode and you open it first, every other workbook you open in that session is also in Manual mode. Setting it back to Automatic fixes it for the session.

There is also a third option, Automatic except for data tables. It only affects What-If Analysis data tables, so you can usually ignore it.

Cause 2: The formula uses typed-in numbers#

A formula only updates when it refers to cells. If someone typed the values into the formula, like this:

=1200+950+1430+880

the result is fixed. Changing the sales in B2 doesn't matter, because the formula never looks at B2. Replace the numbers with references:

=SUM(B2:B6)

Now any change in B2:B6 updates the total. In the first formulas lesson you see this in action: a formula that points at cells updates the moment you change an input. Our guide to writing your first Excel formula explains why cell references are the habit to build.

Cause 3: The range doesn't include new rows#

A formula like =SUM(B2:B6) only adds rows 2 to 6. If you add a new rep in row 7, the total doesn't change, so it looks like the formula is not updating. It is updating; it just doesn't include the new row.

Fixes:

• Extend the range: =SUM(B2:B7).
• Insert new rows inside the range (above the last row), so Excel stretches the range for you.
• Convert the data to an Excel table with Ctrl+T (Mac: ⌘T). Formulas that refer to a table column grow automatically.

The Debugging SUM lesson has you fix exactly this bug on an expense list.

Cause 4: Some inputs are numbers stored as text#

If you type a new value into a cell that is formatted as Text, Excel stores it as text. SUM skips text, so the total doesn't move. The cell sits on the left side and often shows a small green triangle.

Change the cell to General, then type the number again. For a whole column, see how to find and fix numbers stored as text.

Cause 5: The cell shows the formula, not a result#

If the cell shows =SUM(B2:B6) as text, the formula never runs at all. Either Show Formulas is on (press Ctrl+` to switch it off) or the cell is formatted as Text. Our guide to why Excel shows the formula instead of the result covers the fix step by step.

Other reasons a result looks stuck#

A few less common causes:

• Circular reference. A formula that includes its own cell can't calculate properly and often shows 0. Excel shows a warning and lists the cell under Formulas > Error Checking > Circular References.
• Rounded display. The value changed, but the number format shows too few decimals to see it. Click the cell and check the formula bar.
• Links to other workbooks. A formula that refers to a closed file may show the last saved value until you update the links.
• Errors in an input. If one input shows an error such as #VALUE! or #REF!, the formula returns that error instead of a new result.
• Unexpected order of operations. The formula updates but gives a different number than you expect. Check the brackets; see Excel order of operations.

Being able to work through a checklist like this calmly is a big part of being job-ready, as our guide on how long it takes to learn Excel for a job explains.

Practise it#

Fix the stuck total in the exercise on this page, then work through the Debugging SUM lesson to find a short range, a text number and a circular reference in a live sheet. For the full reference on adding ranges, see the SUM function guide.