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.

**Note:**

- There is another option called
**Automatic Except for Data Tables**available under the**Calculation Options**feature. Only data tables from**What-If Analysis**will be affected by this option; standard Excel tables will continue to be calculated automatically.

**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.

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

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.

**Note:**

- You can also change values in the
**Maximum Iterations**box and the**Maximum Change**box to modify how frequently Excel formulas can recalculate. - Type the maximum number of iterations in the
**Maximum Iterations**box. A worksheet is recalculated more slowly the higher the number is given here. - Type the maximum amount of change in the
**Maximum Change**box. The accuracy of the result and the length of the worksheet’s recalculation process increase with decreasing numbers. **Maximum Iterations**and**Maximum Change**are set to 100 and 0.001 by default. In other words, Excel will stop recalculating your formulas after 100 iterations or when there has been a change of less than 0.001 between iterations, whichever occurs first.

**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**

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