Chapter 9: Introducing Formulas and Functions
9
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%, 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)/12If 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 for-
mula 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 refer-
ences 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
different 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:- Double-click A1 (or press F2) to get into Edit mode.
- 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 End, fol-
lowed by Shift+Home. - Choose Home ➪ Clipboard ➪ Copy (or press Ctrl+C). This copies the selected text
(which will become the copied formula) to the Clipboard. - Press Esc to leave Edit mode.
- Select cell A2.
- Choose Home ➪ Clipboard ➪ Paste (or press Ctrl+V) to paste the text into cell
A2.
You can also use this technique to copy just part of a formula, if you want to use that part
in another formula. Just select the part of the formula that you want to copy by drag-
ging the mouse and then use any of the available techniques to copy the selection to the
Clipboard. You can then paste the text to another cell.
Formulas (or parts of formulas) copied in this manner won’t have their cell references
adjusted when they’re pasted into a new cell. That’s because the formulas are being copied
as text, not as actual formulas.