How to Use Excel Calculation Options (6 Suitable Examples)

In this article, you’ll learn 6 suitable examples of how to use Excel Calculation Options.

The examples include changing the Calculation Options to Automatic or Manual, manually calculating the dataset, adding Automatic and Manual commands in the Quick Access Toolbar, and using VBA Macro to change.

You’ll also learn to enable the Iterative Calculation option for formulas using circular reference and to set calculation precision from stored values to displayed values.

We normally use the Automatic option to change the values of our dataset automatically and switch to the Manual option when our dataset is large to save time.

Below you’ll see an overview image showing the Automatic Calculation Options is enabled and the dataset is updating automatically in Excel.

Download Practice Workbook

You can download the Excel file from here for practice.

6 Suitable Examples to Use Excel Calculation Options

We’ll use the following dataset to show 6 suitable examples to use the Excel Calculation Options. Here we multiply the Price column with the Quantity column to get the Sales of each product and then sum the Sales column using the SUM function to get the Total Sales of all the products.

Example 1: Changing Formula Calculation Options to Automatic or Manual

We want to change the cell values of our dataset having formulas automatically or manually when we change any value.

1.1 To Automatic

• To change the formula Calculation Options to Automatic, go to Formulas > Calculation Options > Automatic.
• Normally when you open an Excel workbook, the Calculation Options is Automatic by default.

• Now, when you change the values in cells D5 and D6, the values in Cells E5, E6 and E12 will automatically update.

1.2 To Manual

• To change the formula Calculation Options to Manual, go to Formulas > Calculation Options > Manual.

• Now, when you change the values in cells D5 and D6, the values in cells E5, E6 and E12 won’t change automatically.

• To change the values manually, click on the Calculate Sheet option from the Formulas tab.
• The values will update now.

Example 2: Manually Calculating Sheet from Formulas Tab

When the Calculation Options in your Excel file are set to Manual, then after each change in your workbook Excel doesn’t recalculate. To recalculate your workbook you have to use the Calculate Now or the Calculate Sheet option.

2.1 Using Calculate Now Option

• Change any values in your dataset, like we change the values in cells D5 and D6.
• To recalculate all the sheets of the workbook, click on the Calculate Now option from the Formulas tab. This option will recalculate all sheets in all open workbooks.
• Or, you can press F9 from the keyboard to recalculate all sheets of the workbook.

2.2 Using Calculate Sheet Option

• Change some values in your dataset, as we did in cells D5 and D6.
• To recalculate only this sheet, click on the Calculate Sheet option from the Formulas tab. This option will recalculate only the current sheets of the workbook.
• Or, you can press Shift + F9 from the keyboard to recalculate only this sheet.

Example 3: Adding Manual and Automatic Calculation Commands to Toolbar

• To add Automatic and Manual commands in the Quick Access Toolbar, click on the Customize Quick Access Toolbar icon from the Toolbar and go to the More Commands option.

• The Quick Access Toolbar tab under the Excel Options window will open.
• Choose All Commands from the Choose commands from: dropdown.
• Find the Automatic Calculation command from the commands list and click on the Add button to add this on the Toolbar.

• Again find the Manual command from the commands list and click on the Add button to add this on the Toolbar.
• Click OK to close this window.

• The Automatic and Manual commands are now added in the Quick Access Toolbar.
• You can check any of them and easily switch between automatic to manual and manual to automatic calculation.

Example 4: Using VBA Macro to Change Formula Calculation Options

• To insert a VBA code for changing the Calculation Options, go to Developer > Visual Basic.
• Or use the keyboard shortcut Alt + F11 to directly open the Visual Basic window.

• Select Module from the Insert menu in the Visual Basic window to open a new Module.

• Enter the following code in the Module and click the Run button or press F5 from the keyboard 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 Automatic.

• After running the code you’ll get a message like this stating “The Calculation Option is set to Manual”.
• Click OK to change the Calculation Options.

• The Calculation Options are now in Manual mode.
• If you again run the code, the Calculation Options will change to Automatic again.

Example 5: Enabling Iterative Calculation from File Tab

We have entered the following formula using the IF and NOW functions in cell D5 and then use the Fill Handle tool so that we can obtain the delivery time of the products if the delivered status is yes.

But it isn’t giving any results as the formula is using circular reference. Due to the possibility of an endless loop created by a circular reference, Excel does not by default calculate these calculations. The number of times you want a formula to recalculate must be specified in order to enable circular references.

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

• Select the Options menu from the Home tab.

• The Excel Options window will open.
• Go to the Formulas tab and give a tick beside the Enable iterative calculation option.
• Press OK and you’ll see that the formula is working now.

Example 6: Setting Calculation Precision from File Tab

• We entered 200.002 in cell C5 and 200.003 in cell C6 but Excel is displaying 200.00 in both cells because we choose to display only 2 decimal places in those cells.
• We add up those numbers in cell E13 and it is showing 400.01 because it computes the stored values (200.002 and 200.003) rather than the values that were visible.

• To change the calculation to the displayed value, go to the File tab.

• Select the Options menu from the Home tab.

• The Excel Options window will open.
• Go to the Advanced tab and give a tick beside the Set precision as displayed option.
• Press OK and you’ll see that the value in cell E13 is changed to 400.00 because now it is computing the displayed values.

Things to Remember

• The Automatic Except for Data Tables option only affects data tables from the What-If Analysis tool, not the standard Excel tables.
• If you want to update only the current sheet values when the Calculation Options are set to Manual, use the Calculate Sheet option instead the Calculate Now option. Because it’ll take less time to update the sheet.
• The keyboard shortcut for the Calculate Now option is F9.
• The keyboard shortcut for the Calculate Sheet option is Shift + F9.
• The keyboard shortcut for opening the Visual Basic window is Alt + F11.
• Enable the Iterative Calculation option for any formulas using circular reference.

Conclusion

In conclusion, you have learned some suitable examples regarding using Excel Calculation Options in this article. By learning how to switch between Automatic and Manual calculation modes, manually calculate datasets, and utilize VBA Macros, you can efficiently manage large datasets and improve productivity in Excel. Additionally, you can also enable the Iterative Calculation option for circular references and set calculation precision as your wish. Please let us know in the comment section if there is any query or suggestions related to this topic.

Excel Calculation Options : Knowledge Hub

• How to Recalculate in Excel
• Excel Recalculate Button
• Excel Force Recalculation VBA

<< Go Back to How to Calculate in Excel | Learn Excel