Excel full recalculation

Excel full recalculation

When you are working with big financial models, you may notice that sometimes it does not appear that the spreadsheet calculates fully. Conditional formats may only ‘fix’ themselves after you re open the file or numbers don’t make sense, and then suddenly they are right. This often means that you need an Excel full recalculation procedure.

Most people are aware that F9 does a recalculate, typically when you are in manual calculation mode or ‘Automatic except for Data Tables’. However, what you may not realise is that Excel, even when it does recalculate, does not recalculate every cell each time. It makes some intelligent assumptions on what needs to be recalculated. If your spreadsheets has lots of calculations this can cause a problem.

In these situations you may want to try CTRL + ALT + F9 which is an Excel full recalculation short cut.

To better understand how recalculate works, summarised from the Microsoft website:

  • pressing F9 – recalculates only cells in the workbook that need to be recalculated,
  • SHIFT+F9 – calculates only the active sheet, or
  • CTL+ALT+F9 –  does a full recalculation, regardless of whether cells need to be recalculated or not.

Learn more at our Advanced Excel and Financial Modelling courses