Microsoft Office Tutorials and References

In Depth Information

When you copy a formula, Excel adjusts the formula's cell references when you paste it to a different location.

Usually, adjusting the cell references is exactly what you want. Sometimes, however, you may want to make an

exact copy of the formula. You can do this by converting the cell references to absolute references, as discussed

earlier — but this isn't always desirable.

A better approach is to select the formula while in edit mode and then copy it to the Clipboard as text. There are

several ways to do this. Here I present a step-by-step example of how to make an exact copy of the formula in

A1 and copy it to A2:

1.
Select cell A1 and press F2 to activate edit mode.

2.
Press Ctrl+Home to move the cursor to the start of the formula, followed by Ctrl+Shift+End to select all the

formula text.

Or you can drag the mouse to select the entire formula.

Note that holding down the Ctrl key is necessary when the formula is more than one line long, but optional

for formulas that are a single line.

3.
Choose Home
⇒
Clipboard
⇒
Copy (or press Ctrl+C).

This copies the selected text to the Clipboard.

4.
Press Esc to end edit mode.

5.
Activate cell A2.

6.
Press F2, for edit mode.

7.
Choose Home
⇒
Clipboard
⇒
Paste (or press Ctrl+V), followed by Enter.

This operation pastes an exact copy of the formula text into cell A2.

You can also use this technique to copy just part of a formula to use in another formula. Just select the part of

the formula that you want to copy by dragging the mouse or by pressing the Shift+arrow keys. 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 you paste

them to a new cell. This is because you copy the formulas as text, not as actual formulas.

Another technique for making an exact copy of a formula is to edit the formula and remove its initial equal sign.

This converts the formula to text. Then, copy the “nonformula” to a new location. Finally, edit both the original

formula and the copied formula by inserting the initial equal sign.

Converting Formulas to Values

If you have a range of formulas that always produce the same result (that is, dead formulas), you may want to

convert them to values. You can use the Home
⇒
Clipboard
⇒
Paste
⇒
Values command to do this.

Suppose that range A1:A10 contains formulas that calculate results that never changes. To convert these formu-

las to values:

1.
Select A1:A10.

2.
Choose Home
⇒
Clipboard
⇒
Copy (or press Ctrl+C).

3.
Choose Home
⇒
Clipboard
⇒
Paste
⇒
Values (V).