If your Excel formulas are not updating, the most common cause is that calculation mode is set to Manual instead of Automatic. Press F9 (or Cmd + = on Mac) to force a recalculation, then switch calculation back to Automatic via Formulas > Calculation Options > Automatic to prevent the issue from recurring.
Why Excel Stops Updating Formulas
Excel normally recalculates a formula whenever its input cells change, but several settings and conditions can interrupt that flow. The most frequent culprit is manual calculation mode, which you might have accidentally enabled with a keyboard shortcut or through the ribbon. Other causes include:
- Cells formatted as Text, which prevents Excel from evaluating the formula.
- The formula being entered with a leading apostrophe (
') or an extra space. - Volatile functions like
RAND()orTODAY()not updating because calculation is manual. - Circular references that halt recalculation.
- Hidden rows or columns that appear to “not update” because the visible result is stale.
How Calculation Modes Work
Excel has three calculation modes: Automatic, Automatic Except for Data Tables, and Manual. In Automatic mode, Excel recalculates whenever a dependent cell changes. In Manual mode, it only recalculates when you explicitly trigger it (F9, Shift+F9 for the active sheet, or Ctrl+Alt+F9 to rebuild the dependency tree). The setting is stored per workbook, so if you open a file created with Manual mode, it stays manual until you change it.
To check the current mode, go to Formulas > Calculation Options. A checkmark appears next to the active mode. You can also see it in the status bar at the bottom of the Excel window: if it shows “Calculate” (or “Ready” with a manual indicator), the mode is manual.
Step-by-Step Fixes to Try
Work through these in order, testing after each step:
- Force a recalculation: Press F9 (Windows) or Cmd + = (Mac). If the values update, the problem is simply manual mode.
- Set calculation to Automatic: Go to Formulas > Calculation Options > Automatic. This is the default and should be your long-term setting.
- Check for text formatting: Select the formula cell, go to the Home tab, and look at the Number Format dropdown. If it says “Text,” change it to “General” and re-enter the formula (press F2, then Enter).
- Look for a leading apostrophe: Click the cell and look in the formula bar. If you see an apostrophe before the equals sign, remove it and press Enter.
- Check for circular references: Go to Formulas > Error Checking > Circular References. If a cell is listed, fix the circular path—a formula that refers to itself, directly or indirectly.
- Clear stale formatting: If a range of cells is formatted as Text, select the range, set format to General, then use Data > Text to Columns > Finish to force recalculation.
Common Misconceptions About Updating
Many users think that scrolling or saving triggers recalculation—it does not. Saving recalculates only if the workbook is set to automatic. Another misconception is that pressing F9 recalculates only the active sheet; actually, it recalculates all open workbooks. To recalculate just the active sheet, use Shift+F9 (Windows) or Cmd + Shift + = (Mac).
Also, a formula that returns the same value after a change might not be “stuck”—it could be that the referenced cell is empty or contains a value that hasn’t changed. For example, if =A1+B1 returns 5 and you change A1 from 2 to 3, the result should become 6. If it stays 5, check that A1 actually changed (maybe it’s text that looks like a number).
Volatile Functions and Manual Mode
Functions like TODAY(), NOW(), RAND(), and OFFSET() are volatile—they recalculate every time Excel recalculates, even if no input changed. In manual mode, they won’t update until you press F9. This is a common gotcha for dashboards that use TODAY() to show the current date. If your workbook relies on volatile functions, keep calculation on Automatic, or remember to press F9 when you open it.
When Formulas Show as Text
If your formula appears as text (e.g., you see =A1+B1 instead of a number), the cell is formatted as Text. This happens when you import data or copy from a web page. To fix, select the cell, set Number Format to General, then click into the formula bar and press Enter. For a whole column, select the column, set General, then use Data > Text to Columns > Delimited > Finish—this forces Excel to re-evaluate each cell.
Frequently Asked Questions
Why won’t my formulas update when I change a cell?
Most likely calculation mode is Manual. Go to Formulas > Calculation Options > Automatic and press F9 once to recalculate. If it’s already Automatic, check that the changed cell is actually referenced by the formula, and that the formula cell isn’t formatted as Text.
How do I force Excel to recalculate all formulas?
Press Ctrl + Alt + F9 (Windows) to rebuild the calculation chain and recalculate everything. On Mac, use Cmd + Option + =. This is more thorough than F9, which only recalculates formulas whose inputs have changed.
What does “Calculate” mean in the status bar?
It means Excel is in Manual calculation mode and there are unsaved changes that require recalculation. Click “Calculate” or press F9 to update all formulas. The message disappears after recalculation.
Can a circular reference stop all formulas from updating?
Yes, Excel stops calculating at the circular reference and may show a warning. Go to Formulas > Error Checking > Circular References to see the cell, then edit the formula to remove the loop (e.g., if B1 contains =B1+C1, change it to =C1).
Why does my formula update only when I double-click it?
That happens when the cell is formatted as Text. Double-clicking puts you in edit mode, and pressing Enter forces Excel to treat it as a formula. To fix permanently, change the number format to General and re-enter the formula.
Does Excel update formulas automatically when I open a file?
Yes, if the workbook is set to Automatic calculation, it recalculates on open. If it’s Manual, it does not, and you’ll see “Calculate” in the status bar. You can change the default in File > Options > Formulas > Calculation options (Windows) or Excel > Preferences > Calculation (Mac).
Related
- Excel and Numbers Compatibility: What Works
- Excel Align Cells Evenly: 4 Quick Ways
- Excel and Access LLC: What’s the Difference?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Between Two Dates: Count, Sum, or CheckIf dates are in A2:A100, the start date is in D1, and the end date is in E1,…
- Excel LEN, FIND, LEFT & RIGHT: Text Extraction FormulasTo split text in Excel, combine LEN and FIND with LEFT or RIGHT: use =LEFT(A2,FIND(" ",A2)-1) to return…
- Excel UNIQUE Function: Return Values Without DuplicatesYes. In Microsoft 365 for Windows, enter =UNIQUE(A2:A100) to return the distinct values in A2:A100, with repeated entries…
- Excel Formula for Days Left in MonthThe simplest Excel formula for days left in the current month is =EOMONTH(TODAY(),0)-TODAY(). This returns the number of…
- Excel LOWER Formula: Convert Text to LowercaseUse Excel’s LOWER function to convert text to lowercase: if the original text is in A2, enter =LOWER(A2)…
- Arduino String Functions: Methods, Examples, and TipsArduino string functions are methods for working with text stored in the Arduino String class: for example, length()…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.