Overview
With the help of Calculation Option you can choose to calculate formulas automatically or manually.
If you make a change that affects a value then excel will automatically recalculate it
You have 3 Options available in Calculation Options
- Manual
- Automatic
- Automatic Except for Data Tables
Working
Let’s understand the working with the help of an example.
We have (Figure 1: Sales report) of Sales Rep. for 4 Countries
We have already calculated the Sales Amount country wise using Sum if function.
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-1-1.jpg)
Figure1: Sales Report
You can see the Sum If function applied in the Cell H3 to H6
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-2.jpg)
Manual Calculation
Let’s understand Manual Calculation first with an example
Step 1: Click on FORMULAS tab
Step 2: Click on Calculation Options
Step 3: Click on Manual from the Calculation group
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-3.jpg)
Step 4: Change the Value in Cell D2 from 22,737 to 10,000
Step 5: There is no change in the Cell H3 for Korea
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-4.jpg)
Step 6: Click on Calculate Now Or Press F9
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-5.jpg)
Step 7: Now you can see the change in the value of Korea in Cell H3.
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-6.jpg)
Automatic Calculation
Let’s understand automatic calculation with an example
Step 1: Click on FORMULAS tab
Step 2: Click on Calculation Options
Step 3: Click on Automatic from the Calculation group
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-7.jpg)
Step 4: Change the Value in Cell D2 from 10,000 to 50,000
Step 5: You can see the value changed automatically in Cell H3 for Korea.
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-8.jpg)
Automatic Except for Data Tables
Let’s understand automatic except for data tables with the help of an example
A data table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem.
Step 1: Automatic is selected to show what it does to the data table.
Step 2: Change the value of 500 to 1000 in cell A3 (Total number of Desktops sold).
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-9.jpg)
Step 3: Notice the change in the value in the Profit depending on the Number of Desktops sold according to Percentage (from 60% to 100%)
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-10.jpg)
Step 4: Click on FORMULAS tab
Step 5: Click on Calculation Options
Step 6: Click on Automatic Except for Data Tables from the Calculation group
Step 7: Change the Number to 750
There is no change in the value in the Profit depending on the Number of Desktops sold according to Percentage (from 60% to 100%)
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-13.jpg)
Step 8: Click on FORMULAS tab
Step 9: Click on Calculation Options
Step 10: Click on Automatic from the Calculation group
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-12.jpg)
Notice the change in the value in the Profit depending on the Number of Desktops sold according to Percentage (from 60% to 100%)
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-13-1.jpg)
You can also change the calculation option by following the below steps.
Step 1: Click on File
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-14.jpg)
Step 2: Click on Options
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-15.jpg)
Step 3: Excel Options Dialog box appears.
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-16.jpg)
Step 4: Click on Formulas option
Step 5: Select Workbook Calculation from Calculation options.
Step 6: Click on OK
![](https://careergrowthpromotion.com/wp-content/uploads/2022/09/T-17.jpg)
Scope of usage
- Manual calculation option helps to calculate formulas manually
- Automatic calculation option calculates automatically in case of any changes
- Automatic Except for Data Tables option is used to calculate formulas except for data tables