### 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 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.