Microsoft Office Tutorials and References

In Depth Information

**Making an exact copy of a formula**

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 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:

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 End,

followed by Shift+Home.

3.
Choose Home

Copy (or press Ctrl+C).
This copies the selected text

(which will become the copied formula) to the Clipboard.

➪

Clipboard

➪

4.
Press Esc to leave Edit mode.

5.
Select cell A2.

6.
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

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’re pasted into a new cell. That’s because the formulas are being copied

as text, not as actual formulas.

You can also convert a formula to text by adding an apostrophe (
‘
) in front of the equal sign. 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 apostrophe from the original formula as well.

15

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. For example, if you use the