Excel 2010 Bible

Chapter 10: Introducing Formulas and Functions

227

Tips for Working with Formulas .......................................................................................

In this section, I offer a few additional tips and pointers relevant to formulas.

Don’t hard-code values ............................................................................................

When you create a formula, think twice before you use any specific value in the formula. For

example, if your formula calculates sales tax (which is 6.5 percent), 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

define 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 percent, 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 exam-

ple, 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 the result in the

active cell. (This technique also works if the formula uses cell references or worksheet functions.)

Making an exact copy of a formula ..........................................................................

When you copy a formula, Excel adjusts its cell references when you paste the formula to a differ-

ent location. Sometimes, you may want to make an exact copy of the formula. One way to do this

is to convert the cell references to absolute values, but this isn’t always desirable. A better approach

is to select the formula in Edit mode and then copy it to the Clipboard as text. You can do this in

several ways. Here’s a step-by-step example of how to make an exact copy of the formula in A1 and

copy it to A2:

1. Double-click A1 (or press F2) to get into Edit mode.


2. Drag the mouse to select the entire formula. You can drag from left to right or from
   right to left. To select the entire formula with the keyboard, press Shift+Home.


3. Choose Home ➪ Clipboard ➪ Copy (or press Ctrl+C). This copies the selected text
   (which will become the copied formula) to the Clipboard.