How formula calculations are performed in Excel

This article describes how formula calculations are performed in the various versions of Microsoft Excel.

Versions of Excel that are earlier than Microsoft Excel 2002

In versions of Excel that are earlier than Excel 2002, formula calculations are performed on a sheet by sheet basis, starting with Sheet1 of your Excel workbook. After the calculations are complete on Sheet1, Excel calculates the formulas on Sheet2 until all the formulas are calculated on all the sheets of your workbook.

This process works fine as long as the precedents and dependents are on the same sheet. If the precedents and dependents are on different sheets but cross sheet references, this may cause some issues.

For example, if the formula in Sheet1!A1 is =Sheet3!A4+1, both Sheet1!A1 and Sheet3!A4 have to calculate. Start with Sheet1. When you try to calculate A1, you may realize that you cannot because Sheet3!A4 has not calculated yet. Continue trying to calculate what you can on Sheet1. When you are finished with Sheet1, calculate Sheet2. Then calculate Sheet3, finally updating Sheet3!A4.

At this point, you have hit all the sheets. However, Sheet1!A1 has still not been calculated, so go back and start calculating Sheet1 again. Finally, Sheet1!A1 can calculate. You will loop over all the sheets trying to calculate an arbitrary number of times, depending on how many cross-sheet dependencies there are and how they are laid out.

This process has been determined to be an inefficient method for calculating formulas in your workbook.

Excel 2002 and Microsoft Office Excel 2003

In Excel 2002 and Excel 2003, one big list of formula precedents is kept for calculations. That is, all the formulas that are contained in your workbook are placed in memory as one continuous list of formulas. When you enter a new formula and it is calculated fully for the first time, is it put in its correct place in the calculation chain.

Excel starts the task of calculating the formulas from the top of the list. If a precedent formula has not been calculated, Excel jumps down the list of formulas and calculates the dependent formula. Then Excel returns to the precedent formula and completes the calculation.

After a formula in the list is complete, Excel moves to the next formula and continues calculating formulas until all the formulas in the list are calculated.

With this new process of calculating formulas, Excel will only make one pass in your workbook when calculating your formulas.
Some other calculation changes to note are as follows:

• Reentrant calculation is now disallowed. You cannot make a call to the range.calculate method in Microsoft Visual Basic for Applications (VBA) if you are already in a calculation. Cell A1 contains a user-defined function, inside the user-defined function you have a range.calculate. You receive a run-time error.
• You cannot perform a Name.Add method or a Name.Delete method in a user-defined function.
• If you are using a SUMIF() function or COUNTIF() function where any of the criteria are more than 255 characters, throw a
  #VALUE
  error message immediately.
• If you copy a whole sheet where any of the cells contain more than 255 characters, dirty the whole sheet for calculation immediately.
• You cannot perform a range.calculate when you have multiple sheets selected. This will also throw a runtime error.
• In some cases, Excel may appear to calculate very slowly compared to Excel 2000 and earlier, particularly when a full recalculation is performed by using Ctrl+Alt+F9 or when the full recalculation is performed when you open a file from a previous version of Excel in Excel 2002 or in Excel 2003 for the first time.

Microsoft Office Excel 2007 and Excel 2010

Excel 2007 performs formula calculations as a set of functions. To optimize performance, the set of functions is aggregated into as few queries as possible. These aggregated queries are generally performed in the background when other Excel functions are idle.

In the aggregated queries, one big list of formula precedents is kept for calculations. All the functions that are contained in your workbook are placed in memory as one continuous list of functions.

When you enter a new formula in a workbook, the new formula is then calculated for the first time. When the new formula is calculated for the first time, Excel 2007 generates a set of functions that belong to the new formula, and then each function in the set of functions is put in its correct place in the calculation chain.

As the calculation chain is being processed, each cell is evaluated. If a cell has no dependency on another function, the cell receives its value right away. If the cell has a dependency on another function, the cell receives a placeholder error value of #GETTING_DATA�, and then calculation proceeds to the next cell in the chain.

After all cells have been evaluated once, Excel 2007 triggers the aggregated query or queries that are needed to retrieve data. While the aggregated query or queries are processed, cells that are waiting for data continue to display the placeholder error value #GETTING_DATA�.

When the processing of the aggregated query or queries is finished, cells that contain the placeholder error value of #GETTING_DATA� are recalculated, and the cell receives its value.