Excel Calculation Options and Macros

How to change Excel calculation options with command buttons or macros. Tricks to see which calculation mode is active, and prevent calculation problems.

Excel Calculation Automatic or Manual

You can end up with serious problems, if an Excel workbook opens up in Manual Calculation mode, and you don't notice. That can happen if someone sends you a file that was saved in Manual mode, and it's the first workbook that you open during an Excel session.

To see how that can happen, watch this short video. There are written steps below the video.

Why Use Manual Calculation?

The default setting for Excel calculation mode is Automatic. When that setting is selected:

• all open workbooks automatically recalculate every time there is a change
• a workbook recalculates when you open it
  • You'll notice that if you just saved another workbook and its title bar says "Saved"
  • After opening the next workbook, the "Saved" disappears, because the file was recalculated

However, when calculation mode is set to Manual:

• all open workbooks only recalculate if you press F9, or Ctrl+Alt+F9, or you save a workbook
• if there are uncalculated formulas, the status bar will show "CALCULATE"

Less Waiting with Manual Calculation

So, if your Excel workbooks have formulas that take more than a few seconds to calculate, you can save time by setting switching to Manual calculation.

That way, you're not wasting time, waiting for one or more workbooks to recalculate, after every change that you make.

But, in most cases, it's best to switch back to Automatic calucation mode, before you save and close the workbook. That will help prevent calculation problems, if you, or someone else, opens that workbook at the start of another Excel session

First Workbook Opened

The calculation mode is an application-level setting.

• All open workbooks have the same calculation mode
• When you start a new Excel session, Excel takes its calculation setting from the first workbook you open.
• Excel ignores the calculation setting in subsequent workbooks, when opening them

So, if the first workbook opened has a Manual calculation setting, any other workbooks that you open will be in Manual calculation mode too.

Don't Cause Manual Problems

You don't want Excel workbooks in Manual calculation mode, if you aren't aware of it.

• Most people always work in Automatic calculation, and won't think to check that setting, when starting an Excel session.
• And, if you assume calculations are updating automatically, you could end up printing or copying data that isn't up to date. That could cause serious and costly problems for your business.

So, if you switch to Manual calculations sometimes, help protect yourself, and your co-workers, by switching back to Automatic before closing and saving the workbook.

Calculation Settings - QAT or Ribbon

The built-in Excel calculation settings are on the Excel Ribbon's Formula tab, at the far right.

To see which calculation option is active:

• Click on Calculation Options
• The active setting has a check mark

Add Automatic and Manual

To help avoid calculation setting problems, you can add the Automatic and Manual commands to the Quick Access Toolbar (QAT) in Excel, or to a custom tab in the Ribbon.

• NOTE: There are instructions on the Quick Access Toolbar page, and look for the Automatic and Manual commands in the "All Commands" list.
• 

Here are 2 benefits from adding the Automatic and Manual commands to the Ribbon or QAT:

• you can quickly see the current calculation mode
• you can easily change the current calculation mode

In the screen shot above, the manual calculation mode is active.

• Manual setting shows a check mark in the QAT
• Manual setting is highlighted in the Excel Ribbon, where it's on a custom tab (Deb)

To switch to Automatic calculation:

• Click Automatic on the QAT, to add a check mark
• OR, click Automatic on the Excel Ribbon, to highlight that command

Application Settings Add-in

If you need to fix the Calculation settings frequently, get Andrew Engwirda's free Application Settings add-in.

• NOTE: The download link is at the very end of Andrew's article.

With the Application Settings add-in, you can quickly see and change the Excel calculation settings

• All non-default settings are in red font, like the Display Status bar turned off in the screen shot below
• There's also an option to let the add-in fix the application settings when you open or save files.

Calculation Setting Macro

Here's another way to keep track of which calculation mode is active. Thanks to UniMord, who recommends this trick that he uses, to make it obvious when calculation mode is set to Manual:

• Use a macro to toggle between Automatic and Manual calculation modes
  • Change gridlines to red, when Manual calculation mode is turned on
  • Change gridlines back to grey, when Automatic calculation modeis turned on

In this screen shot, the gridlines are red, so calculation mode was switched to Manual. The numbers in column B were changed, but the total in B7 did NOT recalculate automatically

Two Calculation Toggle Macros

UniMord didn't send his macros, so I created the 2 calculation toggle macros below.

• Both macros switch the calculation mode from its current setting, to the opposite setting.
• GridCalcToggle01 changes gridline colour for active sheet only
• GridCalcToggleALL changes gridline colour for all sheets in active workbook

Copy one or both macros to a regular code module in your workbook. There are instructions on the Copy macros to workbook page. Both macros are in the sample workbook too, that you can download below.

Then, to run a macro:

• Go to the View tab on the Excel Ribbon, and at the far right, click Macros
• Click on the name of the macro that you want to run
• Click the Run button

Or, make the macros available in every open workbook:

• Save the macros in your Personal Workbook, or another workbook that's always open.
• Then, add the macros to the Quick Access Toolbar, so it's easy to run them.

Gridline Colour - Active Sheet Only

This macro switches calculation mode from its current setting, and changes gridline colour on active sheet only.

Sub GridCalcToggle01()
'toggle calculation mode
'red gridlines if manual
'--active sheet only
'inspired by UniMord

With Application
  If .Calculation = xlAutomatic Then
    .Calculation = xlManual
    ActiveWindow _
      .GridlineColorIndex = 3 'red
  Else
    .Calculation = xlAutomatic
    ActiveWindow _
      .GridlineColorIndex = -4105 'automatic
  End If
End With

End Sub

Gridline Colour - All Sheets in Active Workbook

This macro switches calculation mode from its current setting, and changes gridline colour on all sheets in the active workbook.

Sub GridCalcToggleALL()
'toggle calculation mode
'red gridlines if manual
'--on all sheets
'inspired by UniMord
Dim wbA As Workbook
Dim wsA As Worksheet
Dim ws As Worksheet
Dim GridCI As Long
Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

With Application
  If .Calculation = xlAutomatic Then
    .Calculation = xlManual
    GridCI = 3 'red
  Else
    .Calculation = xlAutomatic
    GridCI = -4105 'automatic
  End If
End With

For Each ws In wbA.Worksheets
  ws.Activate
  ActiveWindow _
    .GridlineColorIndex = GridCI
Next ws

wsA.Activate

End Sub

More Calculation Tips

For more tips and research on Excel calculation, visit Charles Williams' site - Decision Models. There are several pages of calculation secrets that can help you improve your workbook.

Charles is an reknowned Excel calculation expert, and his FastExcel products (affiliate link) can help you speed up your workbooks. Here is a screen shot of the commands on the SpeedTools tab.

Get Sample File

To see the calculation setting macro from this page, get the Calculation Setting Macro workbook.

The zipped file is in xlsm format, and it contains the macros shown on this page.

More Tutorials

Sum Functions

Count Functions

Count Criteria in Other Column

Functions List