Written by Allen Wyatt (last updated January 18, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021
Vernon has been using Excel for quite some time. He noticed that one of his often-used workbooks recently stopped recalculating. When he checks if calculation has been set to manual (Excel Options), it has been. Vernon hasn't purposely done this, however, so he wonders if there is some shortcut key he's hitting, by mistake, that sets calculation to manual.
It is doubtful that a shortcut key is being hit by mistake because there is no simple shortcut key to control calculation. There is a way to do it with the keyboard by pressing Alt+M+X+M, but that sequence is obscure enough to not be pressed by mistake.
It is more likely that the calculation mode is being set by the sequence in which workbooks are being opened on Vernon's system. Believe it or not, Excel's calculation mode is controlled by the workbook that is first opened in your session with Excel. This is described in this ExcelTip:
https://tips.net/T9310
Basically, if Vernon starts Excel by opening a workbook that was last saved with calculation mode set to manual, then every workbook Vernon opens during the rest of that session will automatically have the calculation mode set to manual. Save those workbooks, and now you'll have them set to open in manual calculation if they are the first opened in your next session of Excel. This behavior is described in this page by Microsoft:
https://learn.microsoft.com/en-us/office/troubleshoot/excel/current-mode-of-calculation
To complicate matters even more, if your Personal workbook (assuming you have one on your system) is saved with the calculation mode set to manual, then every time you start Excel directly—without opening a workbook by double-clicking on it—then your entire session in Excel will be in manual recalculation mode.
The solution is to (1) make sure that your Personal workbook is set to automatic recalculation and saved, and (2) make sure that any workbooks you might open directly by double-clicking on them are set to automatic recalculation and saved.
This becomes particularly important if you work with workbooks that others in your organization send to you via e-mail or that you access on a shared network drive. If the other person saved that workbook in manual calculation mode, and you start Excel by double-clicking on that workbook, then you will be using manual calculation mode for the rest of your Excel session.
There is, of course, also the possibility that a macro you are using is setting the calculation mode to manual. If that is the case, then the macro needs to be modified to not change the setting.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5916) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
The Formula Bar is a regularly used feature in the Excel interface. You can, however, modify whether Excel displays the ...
Discover MoreWhen you make changes in a worksheet, Excel automatically recalculates everything that may be affected by that change. If ...
Discover MoreThe Developer tab of the ribbon is the gateway to many advanced features in Excel, including those features related to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-01-20 15:55:18
J. Woolley
@Simon Freeman
Yes. Press Alt+F11 to open the Visual Basic Editor (VBE), then copy/paste the following code to the ThisWorkbook module of PERSONAL.XLSB:
Private WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
App_WorkbookOpen Wb
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim msg As String
If Wb.Name = ThisWorkbook.Name Then Exit Sub
If Application.Calculation = xlCalculationManual Then
msg = "The calculation mode is Manual." & vbLf _
& "Do you want to make it Automatic?"
If MsgBox(msg, (vbQuestion + vbYesNo)) = vbYes Then
Application.Calculation = xlCalculationAutomatic
msg = "The calculation mode is now Automatic."
MsgBox msg, vbInformation
End If
End If
End Sub
For more about PERSONAL.XLSB, see:
https://www.myonlinetraininghub.com/create-a-personal-macro-workbook-personal-xlsb
https://www.ablebits.com/office-addins-blog/excel-personal-macro-workbook/
2025-01-18 05:06:27
Simon Freeman
Would it be possible to create a macro that runs whenever you open a workbook and if the workbook is set to manual calculation, warns you and invites you to change it to auto. It shouldn't change it automatically as you might want manual calculation.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments