Calculating Formulas

You've probably noticed that the formulas in your worksheet get calculated immediately. If you change a cell that a formula uses, the formula displays a new result with no effort on your part. This is what happens when the Excel Calculation mode is set to Automatic. In this mode (which is the default mode), Excel uses the following rules when calculating your worksheet:

♦ When you make a change — enter or edit data or formulas, for example — Excel immediately calculates those formulas that depend on the new or edited data.

♦ If it's in the middle of a lengthy calculation, Excel temporarily suspends calculation when you need to perform other worksheet tasks; it resumes when you're finished.

♦ Formulas are evaluated in a natural sequence. In other words, if a formula in cell D12 depends on the result of a formula in cell D11, cell D11 is calculated before D12.

Sometimes, however, you might want to control when Excel calculates formulas. For example, if you create a worksheet with thousands of complex formulas, you'll find that operations can slow to a snail's pace while Excel does its thing. In such a case, you should set Excel's calculation mode to Manual. You can do this in the Calculation panel of the Options dialog box.

When you're working in Manual Calculation mode, Excel displays Calculate in the status bar when you have any uncalculated formulas. You can press the following shortcut keys to recalculate the formulas:

♦ F9 calculates the formulas in all open workbooks.

♦ Shift+F9 calculates only the formulas in the active worksheet. Other worksheets in the same workbook won't be calculated.

♦ Ctrl+Alt+F9 forces a recalculation of everything. Use it if Excel (for some reason) doesn't seem to be calculating correctly, or if you want to force a recalculation of formulas that use custom functions created with Visual Basic for Applications (VBA).

Excel's Calculation mode isn't specific to a particular worksheet. When you change Excel's Calculation mode, it affects all open workbooks, not just the active workbook.

0 0

Post a comment