If your Excel sheet is not calculating automatically, the most likely cause is that the workbook’s calculation mode is set to Manual. To fix it, go to Formulas > Calculation Options and select Automatic. If that doesn’t work, check for cells formatted as text, circular references, or a single cell stuck in edit mode.
Why Excel Stops Calculating on Its Own
Excel’s default behavior is to recalculate every formula whenever a referenced value changes. However, several settings and conditions can interrupt this automatic flow. The most common is the calculation mode, which can be switched to Manual either by a user or by a macro. Other culprits include:
- Text-formatted cells – If a cell containing a formula is formatted as Text, Excel treats the formula as a literal string and never evaluates it.
- Circular references – A formula that refers to its own cell (directly or indirectly) triggers a warning and may halt recalculation.
- Stuck in edit mode – If you click into a cell and press Enter but the cell remains in edit mode (showing the formula in the cell and the name box), Excel may not recalculate until you press Enter again or Esc.
- Volatile functions – Some functions like
RAND()andNOW()recalculate every time, but if calculation is manual, they won’t update until you force a recalculation.
How to Switch Calculation Mode Back to Automatic
The quickest fix is to change the calculation mode. Here’s how:
- Open your workbook in Excel.
- Go to the Formulas tab on the ribbon.
- In the Calculation group, click Calculation Options.
- Select Automatic from the dropdown.
On a Mac, the path is the same: Formulas > Calculation Options > Automatic. In Excel for the web, calculation is always automatic and you cannot change it, so if you’re using the web version, the issue is likely something else.
If you want to recalculate manually without changing the mode, press F9 (Windows) or Cmd + = (Mac) to recalculate all open workbooks. To recalculate just the active sheet, press Shift + F9 (Windows) or Shift + Cmd + = (Mac).
Check for Text-Formatted Cells
When a cell is formatted as Text before you enter a formula, Excel does not evaluate it. You’ll see the formula as plain text, like =A1+B1 instead of a number. To fix this:
- Select the cell or range of cells that aren’t calculating.
- On the Home tab, in the Number group, change the format from Text to General.
- Click into the formula bar and press Enter to re-enter the formula.
If you have many such cells, you can use the Find & Select > Replace feature to replace = with = (yes, same character) to force Excel to re-evaluate, but the safest is to re-enter each formula.
Circular References and Other Hidden Culprits
A circular reference occurs when a formula refers to its own cell. For example, putting =A1+1 in cell A1. Excel usually shows a warning when you enter it, but if you ignored it, the calculation might stop. To find circular references:
- Go to Formulas > Error Checking (the arrow next to it).
- Select Circular References from the dropdown. Excel will list any cells involved.
Another hidden issue: if you have a cell in edit mode (the formula bar shows the formula and the status bar says “Enter”), Excel won’t recalculate. Press Esc or Enter to exit edit mode.
How to Force a Full Recalculation
If you’ve switched to Automatic and still see stale values, you can force Excel to recalculate everything:
- Press Ctrl + Alt + F9 (Windows) to recalculate all open workbooks completely.
- On Mac, press Cmd + Option + F9.
- If you have a large workbook, this may take a moment.
This is also useful when you’ve changed a setting or a macro has altered calculation.
Preventing Future Calculation Issues
To avoid this problem in the future, consider these tips:
- Keep calculation mode on Automatic unless you have a specific reason to use Manual (e.g., a huge workbook that slows down).
- Before entering a formula, ensure the cell format is General or Number, not Text.
- If you use macros, make sure they don’t set
Application.Calculation = xlManualwithout resetting it. - Periodically check for circular references via Formulas > Error Checking.
Frequently Asked Questions
Why is my Excel not calculating even after I press F9?
If F9 doesn’t update values, the workbook might have calculation set to Manual and the Recalculate before saving option is off. Also, check if the cells are formatted as Text – F9 won’t fix those. Finally, ensure no cell is in edit mode; press Esc first.
How do I make Excel calculate automatically for all new workbooks?
Go to File > Options > Formulas and under Calculation options, select Automatic. This sets the default for all new workbooks. For existing workbooks, you need to change each one individually.
What does ‘Calculate now’ vs ‘Calculate sheet’ do?
‘Calculate Now’ (F9) recalculates all open workbooks. ‘Calculate Sheet’ (Shift+F9) recalculates only the active sheet. Both are useful when calculation is set to Manual.
Can a formula with a #REF! error stop other calculations?
No, a #REF! error only affects the cell containing it. Other formulas will still calculate normally. However, if a referenced cell is deleted, you may see #REF! in multiple places.
Why does my Excel say ‘Ready’ but formulas don’t update?
This usually means calculation mode is Manual. Check Formulas > Calculation Options. Also, if you see ‘Calculate’ in the status bar, it means Excel is waiting for you to recalculate.
Does Excel for Mac have the same calculation options?
Yes, Excel for Mac has the same Calculation Options under the Formulas tab. Keyboard shortcuts differ: F9 is replaced by Cmd + =, and Shift+F9 by Shift + Cmd + =.
Related
- Excel Power Query to Merge Data: How-To
- Excel Power Query Subtract Dates: How-To
- Excel Power Query Speed Up Refresh? Proven Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel #REF! Error: How to Fix and Prevent Reference ErrorsTo correct a #REF! error in Excel, inspect the formula that shows the error, identify the missing cell,…
- Excel Freeze Columns Not Working? Fix the ViewIf Excel freeze columns are not working, select the cell immediately to the right of the columns you…
- Excel Arrow Keys Not Working? Common Causes and FixesIf the arrow keys scroll the worksheet instead of moving the active cell, turn off Scroll Lock: press…
- Excel #VALUE! Error: How to Test for It in a CellTo test whether cell A2 specifically contains Excel’s #VALUE! error, use =IFERROR(ERROR.TYPE(A2)=3,FALSE). It returns TRUE for #VALUE!, FALSE…
- Excel Formula Showing as Text? Fix It FastIf an Excel formula is showing as text instead of returning a result, first check whether Show Formulas…
- Why Is AVERAGE Not Working in Excel?Excel’s AVERAGE function usually appears not to work because the selected range contains numbers stored as text, an…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.