51. Excel Tips - Calculation in Excel

Nick's picture


Calculation in Excel is a big topic, but I am going to try to give you the essentials about calculating formulae.

Calculation works by the creation of a calculation tree. This is a map of all cells, their dependents, and their dependents... etc.. To do the fastest calculation, Excel needs to go right to the end of the tree, and calculate those cells first. If on the other hand, Excel were to calculate each cell one by one, it could not guarantee that every cell had calculated correctly.

Lets see how this works... We have 3 cells... Cell1 is dependent on Cell3, and Cell3 is dependent on Cell2. Cell2 is just a number.

The formulae and values look like this:
calculation-in-excel

Now suppose A3 changes to 2:
calculation-in-excel

If we now calculate each cell one by one starting at the top cell, this is what happens:
calculation-in-excel
NOTE: Cell A2 has the wrong value.. It should be 4. The reason it's wrong is that cell A4 should have been calculated first as A2 depends on A4.

The correct calculation should be:
calculation-in-excel

Now Excel could complete this calculation by recalculating each of these cells again. In this case, as there are only 2 dependencies, the calculation would be complete. However, anyone who has designed complex spreadsheets will know that the amount of dependencies can be absolutely HUGE. Hence the most efficient way is for Excel to build a calculation tree, and work backwards.

By building a tree of dependencies, Excel knows to calculate cell A4 first, then A2... hence making the calculation efficient and quick.

Download a spreadsheet to practice calculation in Excel

Excel Study

How can i study microsoft excel.