Excel Sum Formula Not Updating? Fixes That Work

Formulas & functions · Updated November 22, 2026

If your Excel SUM formula is not updating, the most common cause is that calculation is set to Manual, so press F9 (or Cmd+Ctrl+F9 on Mac) to recalculate, or change it back to Automatic via Formulas > Calculation Options. Other frequent culprits include cells formatted as text, the SUM range missing new rows, or a corrupted workbook. Below we explain each cause and the exact steps to fix it.

Why Excel Stops Recalculating SUM Formulas

Excel normally recalculates formulas whenever a referenced cell changes. But several settings and data types can break that automatic behavior:

  • Manual calculation mode – The workbook is set to recalculate only when you press F9 or save.
  • Text-formatted numbers – Cells that contain numbers stored as text are ignored by SUM.
  • Missing rows in the range – You added new data below the original range, but the SUM formula doesn’t include it.
  • Circular references – A formula that refers to its own cell can stop updates.
  • Corrupted workbook or add-ins – Rare, but can freeze calculation.

Quick Fix: Force a Recalculation

Before changing any settings, try forcing Excel to recalculate:

  1. Press F9 (Windows) or Cmd+Ctrl+F9 (Mac) to recalculate all open workbooks.
  2. If that doesn’t work, press Shift+F9 to recalculate only the active sheet.
  3. Check if the SUM result updates. If yes, the issue is likely manual calculation mode.

To set calculation back to automatic: go to Formulas > Calculation Options and select Automatic. On Mac, the path is Formulas > Calculation Options > Automatic as well.

Check for Text-Formatted Numbers

If a cell looks like a number but is actually text, SUM ignores it. This often happens when data is imported from another system or when a cell was formatted as Text before typing.

To check, select the cells in your SUM range and look at the Number Format box on the Home tab. If it says “Text”, that’s the problem. To fix:

  1. Select the range and press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells.
  2. Choose Number and click OK.
  3. Now re-enter the numbers: select the range, press F2, then Enter (or use Text to Columns).

Alternatively, use the =VALUE() function to convert text to numbers, or use =SUM(VALUE(A1:A10)) as an array formula (press Ctrl+Shift+Enter in older Excel). A simpler approach is to multiply by 1: =SUMPRODUCT((A1:A10)*1).

Expand the SUM Range to Include New Rows

If you inserted rows below your data, the SUM formula doesn’t automatically include them. For example, if you have =SUM(A1:A10) and you add a value in A11, the formula still sums only A1:A10.

To fix, edit the formula to include the new row, or better, use a full-column reference: =SUM(A:A). However, be careful: =SUM(A:A) sums the entire column, which is fine if there’s no stray text above or below. For a dynamic range that automatically expands, consider using an Excel Table (Ctrl+T) – then SUM will automatically include new rows.

Manual Calculation Mode: How to Change It

Excel’s calculation mode can be set to Manual, which is common in large workbooks to improve performance. To change it:

  1. Go to Formulas tab.
  2. In the Calculation group, click Calculation Options.
  3. Select Automatic.

On Mac, the path is the same: Formulas > Calculation Options > Automatic. You can also press F9 to manually recalculate without changing the mode.

Other Causes: Circular References and Corrupted Workbooks

A circular reference occurs when a formula refers to its own cell, either directly or indirectly. Excel usually shows a warning, but if ignored, it can prevent updates. To find it, go to Formulas > Error Checking > Circular References and select the cell listed.

If the workbook is corrupted, try these steps:

  • Copy the data to a new workbook (select all, Ctrl+C, open new file, Ctrl+V).
  • Open the file in Excel’s Safe Mode (hold Ctrl while opening).
  • Use File > Open > Open and Repair.

Frequently Asked Questions

Why is my SUM formula showing 0 even though there are numbers?

Most likely the numbers are stored as text. Use the methods above to convert them to real numbers. Also check that the cells aren’t formatted as Text, and that the range doesn’t include any blank cells that are actually spaces.

How do I make SUM update automatically when I add new rows?

Convert your data range to an Excel Table (select the data, press Ctrl+T). Then use structured references like =SUM(Table1[Column1]). When you add new rows, the table expands and the SUM updates automatically.

Why does my SUM formula not update when I change a value in a referenced cell?

Check if calculation is set to Manual. Press F9 to recalculate. If it still doesn’t update, the cell might be formatted as text, or the formula might have been pasted as a value (check the formula bar).

Can I use F9 to recalculate only one formula?

Yes, select the cell with the formula, press F2 to enter edit mode, then press Enter – this recalculates that formula only. Alternatively, you can press Shift+F9 to recalculate the entire sheet.

What is the shortcut to force Excel to recalculate all formulas?

Press F9 on Windows, or Cmd+Ctrl+F9 on Mac, to recalculate all open workbooks. This is the quickest way to update a SUM that isn’t refreshing.

Why does my SUM formula not include cells I just typed into?

If you typed numbers into cells that are outside the formula’s range, they won’t be included. Check the range in the formula and expand it, or use a full-column reference like =SUM(A:A).

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.