Excel Automatic Calculation Turns Manual After Opening File: Fix

When you open an Excel file, the formula calculation mode may switch from Automatic to Manual without warning. This causes formulas to stop updating until you press F9 or manually recalculate. The problem often occurs because the file itself stores a specific calculation setting, or because an add-in overrides the default behavior. This article explains why Excel changes the calculation mode and provides step-by-step fixes to restore automatic calculation permanently.

Why Excel Switches to Manual Calculation on File Open

Each Excel workbook can store its own calculation mode setting inside the file. When you open a file that was saved with Manual calculation, Excel respects that saved setting and switches away from Automatic. This behavior is by design to prevent large or complex workbooks from recalculating every time you open them, which could cause long delays. However, the setting can also be changed by macros, add-ins, or external link prompts that force Manual mode for the session.

The calculation mode is stored in the CalculationMode property of the workbook. If a file was created or last saved by someone who manually set it to Manual, that setting persists. Additionally, certain Excel add-ins such as Bloomberg Terminal, Solver, or Analysis ToolPak may temporarily switch the mode to Manual when they load. External links that cannot be updated also trigger a prompt that can lead to Manual mode if you choose not to update them.

How the Calculation Mode Affects Your Work

When Excel is in Manual mode, formulas do not recalculate automatically when you change a cell value, insert a row, or update a source file. You must press F9 to recalculate the entire workbook, or Shift+F9 for the active sheet. This can lead to stale results, incorrect reports, and wasted time troubleshooting why numbers do not match.

Steps to Force Automatic Calculation for a Specific File

1. Open the affected file in Excel
   Double-click the file or use File > Open. Wait for the file to fully load.
2. Go to File > Options > Formulas
   In the Excel Options dialog, click the Formulas category on the left sidebar.
3. Under Workbook Calculation, select Automatic
   Click the radio button next to Automatic. Ensure the option is not grayed out. If it is grayed out, a macro or add-in is overriding the setting — proceed to the next fix.
4. Click OK to save the setting
   Excel will immediately recalculate all formulas in the workbook.
5. Save the file to preserve the setting
   Press Ctrl+S or click File > Save. The Automatic mode is now stored inside the file. Close and reopen the file to confirm the setting sticks.

Steps to Disable Add-Ins That Override Calculation Mode

If the setting reverts to Manual after you save and reopen, a COM add-in is likely overriding the workbook setting. Follow these steps to disable suspect add-ins.

1. Open Excel in Safe Mode
   Hold the Ctrl key while starting Excel. A dialog asks if you want to open in Safe Mode. Click Yes. Safe Mode disables all add-ins and customizations. If the file opens with Automatic calculation in Safe Mode, an add-in is the cause.
2. Go to File > Options > Add-ins
   At the bottom of the Add-ins page, click the Manage dropdown and select COM Add-ins, then click Go.
3. Uncheck all third-party add-ins
   Uncheck any add-in that is not from Microsoft, such as Bloomberg, Reuters, Solver, or Analysis ToolPak. Leave the Microsoft add-ins checked unless you know they cause issues.
4. Click OK and restart Excel normally
   Open the same file. Check the calculation mode under Formulas tab > Calculation Options. It should now show Automatic.

Steps to Break External Links That Trigger Manual Mode

When a workbook contains external links to other files that are not available, Excel may switch to Manual calculation to avoid repeated failed update attempts. Breaking or updating those links can resolve the issue.

1. Open the file and go to Data > Edit Links
   On the Data tab, click Edit Links. This button is enabled only if the workbook contains external links.
2. Select each link and click Break Link
   For links that point to files you no longer need, select the link and click Break Link. Confirm the action. This replaces the external reference with its current cached value.
3. For links you still need, click Change Source
   If the source file exists but its path changed, click Change Source and browse to the correct file. Update all broken links.
4. Save the file and reopen
   After breaking or updating all links, save the workbook. Close and reopen it. The calculation mode should now stay Automatic.

If Excel Still Has Issues After the Main Fix

Excel Opens Every File in Manual Mode

If all files open in Manual mode, the default calculation setting for the Excel application may have changed. Go to File > Options > Formulas > Workbook Calculation and select Automatic. Then click OK. This sets the default for all new workbooks. However, existing files that were saved with Manual mode will still open in Manual until you change and save them individually.

A Macro or VBA Code Forces Manual Mode

Some workbooks contain VBA macros that run on open and set Application.Calculation = xlCalculationManual. Press Alt+F11 to open the VBA editor. Look in the ThisWorkbook module or standard modules for code that changes the calculation mode. Remove or comment out those lines. Save the file as a macro-enabled workbook (.xlsm) if it contains other macros you need.

Excel Add-In Installed in the AppData Folder

Some add-ins are installed in the user’s AppData folder and do not appear in the COM Add-ins list. Open File Explorer and paste %appdata%\Microsoft\AddIns into the address bar. Look for .xlam files. Move them to a backup folder temporarily. Restart Excel and test. If the problem stops, one of those add-ins was the cause.

Quick Repair vs Online Repair: Key Differences

Item	Quick Repair	Online Repair
Duration	5 to 10 minutes	30 to 60 minutes
Internet required	No	Yes, full download of Office installer
Fixes add-in corruption	Sometimes	More thorough
Resets calculation defaults	Yes	Yes
Removes custom settings	No	No, only repairs system files

If the problem persists after trying all file-level fixes, run a Quick Repair from Programs and Features in Windows. Right-click Microsoft 365 or Office, select Change, then choose Quick Repair. If that does not work, run Online Repair.

You can now prevent Excel from switching to Manual calculation when opening any file. Start by checking the file’s own calculation setting in File > Options > Formulas. If the problem repeats, disable COM add-ins and break stale external links. For persistent cases, run a Quick Repair of Office. As an advanced tip, use the keyboard shortcut Alt+T+O then press F to open the Formulas options directly without navigating through menus.