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 speciﬁ 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 deﬁ 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