Microsoft Office Tutorials and References
In Depth Information
• F9: Calculates the formulas in all open workbooks (same as the Calculate Now control).
• Shift+F9: Calculates only the formulas in the active worksheet. It does not calculate other worksheets in the
same workbook (same as the Calculate Sheet control).
• Ctrl+Alt+F9: Forces a complete recalculation of all open workbooks. Use it if Excel (for some reason)
doesn't seem to return correct calculations.
• Ctrl+Shift+Alt+F9: Rechecks all the dependent formulas and then forces a recalculation of all open work-
Contrary to what you might expect, Excel's Calculation mode isn't specific to a particu-
lar workbook. When you change Excel's Calculation mode, it affects all open work-
books — not just the active workbook. Also, the initial Calculation mode is set by the
Calculation mode saved with the first workbook that you open.
Cell and Range References
Most formulas reference one or more cells by using the cell or range address (or the name if it has one). Cell
references come in four styles; the use of the dollar sign symbol differentiates them:
• Relative: The reference is fully relative. When you copy the formula, the cell reference adjusts to its new
• Absolute: The reference is fully absolute. When you copy the formula, the cell reference does not change.
• Row Absolute: The reference is partially absolute. When you copy the formula, the column part adjusts, but
the row part does not change.
• Column Absolute: The reference is partially absolute. When you copy the formula, the row part adjusts, but
the column part does not change.
Creating an absolute or a mixed reference
When you create a formula by pointing to cells, all cell and range references are relative. To change a reference
to an absolute reference or a mixed reference, you must do so manually by adding the dollar signs. Or when
you're entering a cell or range address, you can press the F4 key to cycle among all possible reference modes.
If you think about it, you may realize that the only reason you would ever need to change a reference is if you
plan to copy the formula.
Figure 2-4 demonstrates an absolute reference in a formula. Cell D2 contains a formula that multiples the quant-
ity (cell B2) by the price (cell C2) and then by the sales tax (cell B7):