How to Use the Excel Calculation Options - 6 Examples

Below is an overview:

Download Practice Workbook

Download the Excel file.

Use of Excel Calculation Options.xlsm

This is the sample dataset.

Example 1 – Changing the Formula Calculation Options to Automatic or Manual

1.1 To Automatic

Go to Formulas > Calculation Options > Automatic.

When you open an Excel workbook, the Calculation Option is Automatic by default.

If you change the values in D5 and D6, the values in E5, E6 and E12 will automatically update.

Note:

The Automatic Except for Data Tables option is available in Calculation Options feature. Only data tables with a What-If Analysis are affected by this option; standard Excel tables continue to be automatically calculated.

1.2 To Manual

Go to Formulas > Calculation Options > Manual.

If you change the values in D5 and D6, the values in E5, E6 and E12 won’t automatically update.

To change the values manually, click Calculate Sheet in Formulas.

This is the output.

Example 2 – Using the Calculate Sheet option in the Formulas Tab

2.1 Using the Calculate Now Option

Change values in your dataset. Here, the values in D5 and D6.
To recalculate all the sheets in the workbook, click Calculate Now in the Formulas tab.

You can also press F9 to recalculate all sheets in the workbook.

2.2 Using the Calculate Sheet Option

Change values in your dataset. Here, the values in D5 and D6.
To recalculate this sheet only, click Calculate Sheet in the Formulas tab.

You can also press Shift + F9 to recalculate this sheet only.

Example 3 – Adding Manual and Automatic Calculation Commands to the Toolbar

Click Customize Quick Access Toolbar in Toolbar.
Select More Commands.

In Excel Options, choose All Commands in Choose commands from:.
Select Automatic Calculation and click Add to add it to the Toolbar.

Select Manual and click Add to add it to the Toolbar.
Click OK.

The Automatic and Manual commands are added to the Quick Access Toolbar. You can switch between automatic and manual.

Example 4 – Using a VBA Macro to Change the Formula Calculation Options

Go to Developer > Visual Basic.

You can also press Alt + F11 to open the Visual Basic window.

Select Module in Insert.

Enter the following code in the Module and click Run or press F5 to run the code.

Sub Changing_Calculation_Option()
With Application
If .Calculation = xlAutomatic Then
.Calculation = xlManual
MsgBox ("The Calculation Option is set to Manual")
Else
.Calculation = xlAutomatic
MsgBox ("The Calculation Option is set to Automatic")
End If
End With
End Sub

Before running the code, the Calculation Options were set to Automatic.

After running the code you’ll see the following message: “The Calculation Option is set to Manual”.
Click OK.

The Calculation Options are in Manual mode.

If you run the code again, the Calculation Options will change to Automatic.

Example 5 – Enabling Iterative Calculations in the File Tab

Enter the following formula using the IF and NOW functions in D5 and drag down the Fill Handle to see the delivery time of the products whose delivered status is yes.

=IF(C5="yes", IF(D5="",NOW(), D5), "")

No results are displayed, as the formula has a circular reference.

To enable the Iterative Calculation, go to the File tab.

Select Options in the Home tab.

In the Excel Options window, select Formulas.
Check Enable iterative calculation.
Click OK.

The formula is working:

Note:

You can also set the frequency of recalculations by changing values in Maximum Iterations (the higher the number, the more slowly the recalculation), and in Maximum Change (the accuracy of the result and the length of recalculation increase with decreasing numbers).
Maximum Iterations and Maximum Change are set to 100 and 0.001 by default.