Calculation Options in Excel: How to Set When and What to Calculate!

Does this sound familiar to you: Excel takes too much time calculating. Instead of instantly showing the results, you have to wait for several seconds or even minutes for Excel to finish up the calculation. The problem: The larger your Excel model gets, the more you get frustrated by the lack of performance.

In the previous post, we’ve already described 17 methods of speeding up Excel. Now we are going to have a deeper look at the calculation methods. Switching from automatic calculation mode to manual can save you some time. But the basic question is: When do you want what to be calculated?

The moment of calculating

The moment of calculating means, that you can choose between the two basic calculation options ‘Automatic’ and ‘Manual’. The third one (‘Automatic Except for Data Tables’) is similar to ‘Automatic’. Because of that we will now concentrate on the two main options ‘Automatic’ and ‘Manual’.

Automatic calculation calculates after each change

Automatic calculation means, that after each change in your workbook Excel recalculates. Simply speaking, every time you press enter, Excel calculates all the changed cells and the depending cells in your workbook. Also, Excel calculates all volatile functions as INDIRECT or OFFSET. In a small workbook, you won’t notice that, but large workbooks can suffer from performance.

Manual calculation only recalculates when you ask Excel to

Each time, you ask Excel to recalculate. Therefore, go to Formulas. Under Calculation Options you can select Manual (see the image above). Now, Excel will recalculate

• If you actively initiate a recalculation. For example by pressing F9 or by going to Formulas and clicking on Calculate Now (number two on below picture).
• If you save an Excel file, it will also be recalculated. Whereas the first method (pressing F9) can be canceled easily, this method is quite stable.

What to calculate?

When on manual calculation mode, you can (quite roughly though) select, which part of your Excel workbook should be recalculated:

• If you want the whole workbook to be calculated: Switch to manual mode and press F9 or go to Formulas and click on Calculate Now.
• For only calculating the current sheet: In the manual mode, press Shift + F9 or go to Formulas and click on Calculate Sheet.
• If you only press F9, all changes formulas and following cells will be updated. So, when you have the feeling that some formulas aren’t showing correct results, you can force Excel to recalculate the whole workbook by pressing Ctrl + Alt + F9.
• If you only want to calculate a selection of cells/ cell range only, you have to look for a third party solution. We included such function in our Excel add-in ‘Professor Excel Tools’. You can try it for free. There is no sign-up or installation needed, just download and activate it within Excel. 
• Besides that, you can decide, if data tables (probably 99% of Excel workbooks don’t have them…) should be recalculated. Go to Formulas, click on Calculation Option and select “Automatic Except for Data Tables”.

Summary

After we’ve taken a look at the different methods, let’s put them together in one table. In the columns we differentiate between ‘Manual’ and ‘Automatic’ calculation. 

What to calculate?	Calculation Mode: Manual	Calculation Mode: Automatic
All formulas in all open workbooks	Press Ctrl + Shift + F9   + +	Press Ctrl + Shift + F9   + +
All changes in all open workbook	Press F9  or go to ‘Formulas’ –> ‘Calculate Now’	Not selectable when on automatic mode
Worksheet	Press Shift + F9  or go to ‘Formulas’ –> ‘Calculate Sheet’   +	Not selectable when on automatic mode
Selection of cells	Go to ‘Professor Excel’ and click on ‘Calculate Selection’	Not selectable when on automatic mode

In conclusion, in most situations, you would be fine by remembering these three things:

• If Excel becomes slow, switch to manual calculation mode.
• Recalculate everything by pressing F9.
• Recalculate just the current sheet by pressing Shift + F9.