Microsoft Office Tutorials and References

In Depth Information

**Converting Formulas to Values**

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 a result that never changes. To

convert these formulas to values:

1.
Select A1:A10.

2.
Choose Home

➜

Clipboard

➜

Copy (or press Ctrl+C).

3.
Choose Home

➜

Clipboard

➜

Paste

➜

Values.

4.
Press Enter or Esc to cancel paste mode.

You can also take advantage of a Smart Tag. In Step 3 in the preceding list, press Ctrl+V to paste.

A Smart Tag appears at the lower-right corner of the range. Click the Smart Tag and select one

of the Paste Values icons (see Figure 2-5).