Your Excel formulas are not updating when you change cell values. This happens because the workbook is set to manual calculation mode. This article explains why this mode exists and provides the steps to turn automatic calculation back on.
Key Takeaways: Restoring Automatic Formula Calculation
- Formulas > Calculation Options > Automatic: This is the primary setting to force all formulas in the workbook to recalculate instantly.
- Keyboard shortcut F9: Manually triggers a full calculation of all formulas in all open workbooks when in manual mode.
- Shift + F9: Recalculates formulas only in the active worksheet, which is faster than a full workbook calculation.
Why Excel Formulas Stop Updating Automatically
Excel has two primary calculation modes: Automatic and Manual. In Automatic mode, Excel recalculates every formula in the workbook whenever a value that affects it changes. This is the default setting for most new workbooks. Manual calculation mode suspends this automatic behavior. Formulas only recalculate when you specifically command Excel to do so, by pressing F9 or a similar key.
The manual mode is a performance feature. It is useful for very large or complex workbooks where constant recalculation would slow down data entry. If you open a workbook created by someone else, or if you accidentally change the setting, your workbook can become stuck in manual mode. The status bar at the bottom of the Excel window will display “Calculate” when manual mode is active and formulas need updating.
Steps to Re-enable Automatic Calculation
Follow these steps to switch your workbook back to automatic calculation and restore normal formula behavior.
- Open the Calculation Options menu
Go to the Formulas tab on the Excel ribbon. In the Calculation group, click the Calculation Options button. - Select Automatic
From the dropdown menu, click Automatic. The moment you select it, Excel will perform a full recalculation of all formulas in your workbook if needed. - Verify the change
Check the status bar at the bottom of the Excel window. The word “Calculate” should no longer be displayed. Change a value in a cell referenced by a formula to confirm it updates immediately.
Using Manual Calculation Mode Intentionally
If you want to use manual mode to speed up work on a large file, you can control when calculation occurs.
- Set workbook to manual
Go to Formulas > Calculation Options and select Manual. - Enter your data
Type or paste all your new values. The formulas will not update, and “Calculate” will appear on the status bar. - Recalculate when ready
Press F9 to calculate all open workbooks. Press Shift + F9 to calculate only the active worksheet. This gives you control over performance.
Common Mistakes and Limitations
Formulas Still Not Updating After Setting to Automatic
If you set calculation to Automatic but formulas still do not update, the workbook may have the “Iterative Calculation” setting enabled. This is used for circular references. Go to File > Options > Formulas and look under Calculation options. If “Enable iterative calculation” is checked, uncheck it unless you specifically need it for a circular formula.
Excel Reverts to Manual Mode on Reopen
If a workbook keeps opening in manual mode, the setting is saved with the file. The person who last saved it likely had it set to manual. To fix this permanently, open the file, set calculation to Automatic, and then save the workbook. The new setting will be stored.
Performance Issues with Large Workbooks
In Automatic mode, a workbook with thousands of complex formulas can become very slow. Instead of using full manual mode, consider setting calculation to “Automatic Except for Data Tables.” Find this under Formulas > Calculation Options. It keeps most formulas automatic but pauses calculation for resource-intensive data tables until you press F9.
Automatic vs Manual Calculation Mode Comparison
| Item | Automatic Calculation | Manual Calculation |
|---|---|---|
| Default Behavior | Yes, for new workbooks | No, must be set manually |
| Formula Recalculation Trigger | Any change to precedent cells | Pressing F9, Shift+F9, or saving |
| Performance Impact | Can be slow for large files | Faster data entry, delayed results |
| Status Bar Indicator | No special text | Shows “Calculate” |
| Best Use Case | Most workbooks, real-time analysis | Very large models, data entry phases |
You can now control when and how your Excel formulas calculate. Use the Automatic setting for everyday work to see instant results. For heavy files, try the manual mode and use the F9 key to update on demand. Remember that the Calculation Options setting on the Formulas tab applies to the entire workbook, not just one sheet.