Page 247 - Excel 2007 Bible

16_044039 ch11.qxp 11/21/06 11:04 AM Page 204
Part II
Working with Formulas and 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 loca-
tion. 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.
4. Press Esc to get out of Edit mode.
5. Select cell A2.
6. Home ➪ Clipboard ➪ Paste (or press Ctrl+V) to paste the text into cell A2.
You also can use this technique to copy just part of a formula, if you want to use that part in another for-
mula. Just select the part of the formula that you want to copy by dragging 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
are pasted to a new cell. That’s because the formulas are being copied as text, not as actual formulas.
TIP You can also convert a formula to text by adding an apostrophe (‘) in front of the equal sign.
TIP
Then, copy the formula as usual and paste it to its new location. Remove the apostrophe from
the pasted formula, and it will be identical to the original formula. And don’t forget to remove the apostro-
phe from the original formula as well.
Converting formulas to values
If you have a range of formulas that will always produce the same result (that is, dead formulas), you may
want to convert them to values. If, say, range A1:A20 contains formulas that have calculated results that will
never change — or that you don’t want to change. For example, if you use the RANDBETWEEN function to
create a set of random numbers and you don’t want Excel to recalculate those random numbers each time
you press Enter, you can convert the formulas to values. Just follow these steps:
1. Select A1:A20.
2. Choose Home ➪ Clipboard ➪ Copy (or press Ctrl+C).
3. Choose Home ➪ Clipboard ➪ Paste Values
4. Press Esc to cancel Copy mode.

204