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

1. Manual
2. Automatic
3. 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