Making an Exact Copy of a Formula

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 values, 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. Choose Home> Clipboard> Paste (or press Ctrl+V). This operation pastes an exact copy of the formula text into cell A2.

You also can 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 using 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 "non-formula" to a new location. Finally, edit both the original and the copied formula by inserting the initial equal sign.