Site icon Professor Excel

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

calculation, options, calculate, manual, automatic, excel, thumbnail

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

You can set Excel to automatic calculation or manual calculation

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

What to calculate?

When on manual calculation mode: Keep calm and press F9.
(Download this and other images as wallpaper to your computer or mobile phone)

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

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: ManualCalculation Mode: Automatic
All formulas in all open workbooksPress Ctrl + Shift + F9

 

Press Ctrl + Shift + F9

 

All changes in all open workbookPress F9  or go to ‘Formulas’ –> ‘Calculate Now’

 

Not selectable when on automatic mode
WorksheetPress Shift + F9  or go to ‘Formulas’ –> ‘Calculate Sheet’

 

Not selectable when on automatic mode
Selection of cellsGo 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:


This function is included in our Excel Add-In ‘Professor Excel Tools’

(No sign-up, download starts directly)

More than 35,000 users can’t be wrong.


Exit mobile version