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.
Before we get into forcing Excel to do a full recalculation, below some methods to control how much and how often Excel recalculates.
How Excel calculates – it does not do a full recalculation every time!
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 realize 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.
Force a FULL recalculation in Excel
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
Related
Recalculate only one sheet in Excel
Speed up the opening or closing of an Excel file that recalculates