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

calculate, calculation, options, manual, automatic, excel
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

  • 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?

wallpaper, mobile, excel, free, keep, calm, spreadsheet, f9
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:

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

 

computer_key_Ctrlcomputer_key_Shiftcomputer_key_F9

Press Ctrl + Shift + F9

 

computer_key_Ctrlcomputer_key_Shiftcomputer_key_F9

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

 

computer_key_F9

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

 

computer_key_Shiftcomputer_key_F9

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:

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

Professor Excel Tools Box

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.


Henrik Schiffner is a freelance business consultant and software developer. He lives and works in Hamburg, Germany. Besides being an Excel enthusiast he loves photography and sports.

Leave a comment

Your email address will not be published. Required fields are marked *