Microsoft Office Tutorials and References
In Depth Information
Tips for Working with Formulas
When you’re working in Manual Calculation mode, Excel displays Calculate in the status
bar when you have any uncalculated formulas. You can use 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 aren’t calculated.
Ctrl+Alt+F9: Forces a complete recalculation of all formulas
Excel’s Calculation mode isn’t specii c to a particular worksheet. When you change the Calculation mode, it affects
all open workbooks, not just the active workbook.
Tips for Working with Formulas
In this section, I offer a few additional tips and pointers relevant to formulas.
Not hard-coding values
When you create a formula, think twice before you use any specifi c value in the formula.
For example, if your formula calculates sales tax (which is 6.5%), you may be tempted to
enter a formula, such as the following:
=A1*.065
A better approach is to insert the sales tax rate in a cell — and use the cell reference. Or
you can defi ne the tax rate as a named constant, using the technique presented earlier in
this chapter. Doing so makes modifying and maintaining your worksheet easier. For
example, if the sales tax rate changed to 6.75%, you would have to modify every formula that
used the old value. If you store the tax rate in a cell, however, you simply change that one
cell, and Excel updates all the formulas.
Using the Formula bar as a calculator
If you need to perform a quick calculation, you can use the Formula bar as a calculator. For
example, enter the following formula — but don’t press Enter:
=(145*1.05)/12
If you press Enter, Excel enters the formula into the cell. But because this formula always
returns the same result, you may prefer to store the formula’s result rather than the formula
itself. To do so, press F9 and watch the result appear in the Formula bar. Press Enter to store
 
Search JabSto ::




Custom Search