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.

Figure1: Sales Report
You can see the Sum If function applied in the Cell H3 to H6

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

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

Step 6: Click on Calculate Now Or Press F9

Step 7: Now you can see the change in the value of Korea in Cell H3.

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

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.

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

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%)

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%)

Step 8: Click on FORMULAS tab
Step 9: Click on Calculation Options
Step 10: Click on Automatic from the Calculation group

Notice the change in the value in the Profit depending on the Number of Desktops sold according to Percentage (from 60% to 100%)

You can also change the calculation option by following the below steps.
Step 1: Click on File

Step 2: Click on Options

Step 3: Excel Options Dialog box appears.

Step 4: Click on Formulas option
Step 5: Select Workbook Calculation from Calculation options.
Step 6: Click on OK

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