When and How Does Excel Recalculate it's Formulas?

Question

As I understand Excel can be set up to manually or automatically recalculate formulas, but after reading some information on the internet I am starting to become confused as to what Excel does when it automatically recalculates a formula.

I had previously assumed Excel would only recalculate formulas when, a cell that a formula references is changed, or if a formula contains volatile data (like the TODAY() function).

My questions:

Does Excel recalculate all the formulas of a workbook, every time that specif Workbook (Excel file) is opened?
When a cell that is referenced in a formula changes, does excel recalculate only the one formula involved or will Excel also recalculate other formals within the entire workbook (or worksheet)?

My reason for asking these questions:

I have made an Excel sheet that keeps track of my Hearthstone statistics. The Worksheets within the Excel file handle a lot of data and I am adding data for every individual match I play.

An example of a portion of one of the worksheets looks like this:

Match Date | Match Number | Match Type | Match Outcome |
 6/10/14          50          Casual         Win
 6/10/14          51          Ranked         Loss
 6/10/14          52          Ranked         Loss
 6/10/14          53          Ranked         Win

I do not manually type in the match number, instead I have this formula =(COUNT($E$3:E52)+1.

So, if I have all of these COUNT formulas in the worksheet, and I am adding more each day, will Excel have to recalculate every COUNT formula every time I open the Excel File? Will Excel have to recalculate every COUNT formula every time another formula calculates within the worksheet? I am worried I will eventually slow down my Excel file if it has too many formulas that must recalculate all the time.

Charles Williams Charles Williams · Accepted Answer · 2014-06-17T17:38:13

When a workbook is recalculated Excel will try to recalculate only the minimum number of formulas requires to get the correct answers. Ignoring things like volatile formulas and full calculation this means that Excel will only recalculate those formulas that depend on a cell that has been changed, or depend on a formula that has been changed, or depend on another formula that has been recalculated.

In most circumstances Excel will not recalculate a workbook when it opens it because it usually will not need recalculating (again ignoring things like volatile functions, full calculation, changes in the calculation engine between versions etc).

For more information see http://www.decisionmodels.com/calcsecrets.htm and associated pages.

When and How Does Excel Recalculate it's Formulas?

1 Answers