MicrosoftВ® Office ExcelВ® 2007 Inside Out (Inside Out (Microsoft))

When you change the value in any of the cells to which a formula refers, Excel updates the displayed values of the formula as well. This updating process is called recalculation, and it affects only those cells containing references to cells that have changed. By default, Excel recalculates whenever you make changes to a cell. If a large number of cells must be recalculated, the word Calculating appears in the status bar, along with a percentage of progress meter if it's going to take a particularly long time. You can interrupt the recalculation process simply by doing something, such as using commands or making cell entries; Excel pauses and then resumes recalculation when you are finished.

Recalculating Manually

To save time, particularly when you are making entries into a large workbook with many formulas, you can switch from automatic to manual recalculation; that is, Excel will recalculate only when you tell it to do so. To set manual recalculation, click the Calculation Options button on the Formulas tab on the Ribbon, and choose the Manual option. You can also click the Microsoft Office Button, Excel Options, and then select the Formulas category to display the additional options shown in Figure 12-27.

Figure 12-27: The Formulas category in the Excel Options dialog box controls worksheet calculation and iteration.

Here are a few facts to remember about calculation options:

• With worksheet recalculation set to manual, the status bar displays the word Calculate if you make a change; click it to initiate recalculation immediately.

• The Recalculate Workbook Before Saving check box helps make sure the most current values are stored on disk.

• To turn off automatic recalculation only for data tables, select the Automatic Except For Data Tables option. For more information, see "Using Data Tables" on page 583.

• To recalculate all open workbooks, click the Calculate Now button in the Calculation group on the Formulas tab on the Ribbon, or press F9.

• To calculate only the active worksheet in a workbook, click the Calculate Sheet button in the Calculation group on the Formulas tab on the Ribbon, or press Shift+F9.

If you have a computer with multiple processors or a hyperthreaded processor, Excel takes full advantage of the additional power by dividing the workload among available processors. Click the Microsoft Office Button, Excel Options, Advanced category, and look in the Formulas area. Excel automatically detects additional processors and displays the total number adjacent to the Use All Processors On This Computer option. The number of processors also appears in the status bar next to the word Calculating, if you have enough formulas to slow the calculation process enough for it to appear. If you want, you can turn this option off if you want to reserve some of your computer's processing bandwidth for other programs you need to run simultaneously.

Calculating Part of a Formula

You might want to see the result of just one part of a complex formula if, for example, you are tracking down a discrepancy. To change only part of a formula to a value, select the part you want to change, and press F9. You also can use this technique to change selected cell references in formulas to their values. Figure 12-28 shows an example.

Figure 12-28: Select any part of a formula, and press F9 to convert it to its resulting value.

If you're just verifying your figures, press the Esc key to discard the edited formula. Otherwise, if you press Enter, you replace the selected portion of the formula.

Working with Circular References

A circular reference is a formula that depends on its own value. The most obvious type is a formula that contains a reference to the same cell in which it's entered. For example, if you type =C1-A1 in cell A1, Excel displays the error message shown in Figure 12-29. After you click OK, Excel opens the Help dialog box, which displays a pertinent topic.

Figure 12-29: This error message appears when you attempt to enter a formula that contains a circular reference.

If a circular reference warning surprises you, this usually means you made an error in a formula. If the error isn't obvious, verify the cells that the formula refers to using the built-in formula-auditing features. For details, see "Auditing and Documenting Worksheets" on page 241.

When a circular reference is present in the current worksheet, the status bar displays the text Circular References followed by the cell address, indicating the location of the circular reference on the current worksheet. If Circular References appears without a cell address, then the circular reference is located on another worksheet.

As you can see in Figure 12-30, when you click the arrow next to the Error Checking button on the Formulas tab and click Circular Reference, any circular references that exist on the current worksheet are listed on a menu that appears only if a circular reference is present. Click the reference listed on this menu to activate the offending cell.

Figure 12-30: The Circular References menu appears if any circular references are present.

You can resolve many circular references. Some circular formulas are useful or even essential, such as the set of circular references shown in Figure 12-31. These formulas are circular because the formula in cell M30 depends on the value in M31, and the formula in M31 depends on the value in M30.

Figure 12-31: The discount formula in cell M29 is circular because it depends on the total, which in turn depends on the discount value in M29.

Figure 12-31 illustrates a useful circular reference scenario called convergence: The difference between results decreases with each iterative calculation. In the opposite process, called divergence, the difference between results increases with each calculation.

When Excel detects a circular reference, tracer arrows appear on the worksheet. To draw additional arrows to track down the source of an unintentional circular reference, select the offending cell, and then click Trace Precedents on the Formulas tab to draw tracer arrows to the next level of precedent cells, as shown in Figure 12-31.

For more information about tracer arrows and other auditing features, see "Auditing and Documenting Worksheets" on page 241.

On the CD You'll find the  Circular Reference.xlsx file in the Sample Files section of the companion CD.

After you dismiss the error message shown in Figure 12-29, the formula will not resolve until you allow Excel to recalculate in controlled steps. To do so, click the Microsoft Office Button, Excel Options, Formulas category, and in the Calculation Options section, select the Enable Iterative Calculation check box. Excel recalculates all the cells in any open worksheets that contain a circular reference.

If necessary, the recalculation repeats the number of times specified in the Maximum Iterations box (100 is the default). Each time Excel recalculates the formulas, the results in the cells get closer to the correct values. If necessary, Excel continues until the difference between iterations is less than the number typed in the Maximum Change text box (0.001 is the default). Thus, using the default settings, Excel recalculates either a maximum of 100 times or until the values change less than 0.001 between iterations, whichever comes first.

If the word Calculate appears in the status bar after the iterations are finished, more iterations are possible. You can accept the current result, increase the number of iterations, or lower the Maximum Change threshold. Excel does not repeat the "Cannot Resolve Circular Reference" error message if it fails to resolve the reference. You must determine when the answer is close enough. Excel can perform iterations in seconds, but in complex circular situations, you might want to set the Calculation option to Manual; otherwise, Excel recalculates the circular references every time you make a cell entry.

For details about calculated columns in tables, see Chapter 21, "Managing Information in Tables."

Understanding the Precision of Numeric Values

Here are three interesting facts about numeric precision in Excel:

• Excel stores numbers with as much as 15-digit accuracy and converts any digits after the 15th to zeros.

• Excel drops any digits after the 15 in a decimal fraction.

• Excel uses scientific notation to display numbers that are too long for their cells.

Table 12-3 contains examples of how Excel treats integers and decimal fractions longer than 15 digits when they are typed in cells with the default column width of 8.43 characters.

Excel can calculate positive values as large as 9.99E+307 and approximately as small as 1.00E-307. If a formula results in a value outside this range, Excel stores the number as text and assigns a #NUM! error value to the formula cell.