MS Excel Trick Exact Copy of Formula
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 to
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.
3. Click the Copy button on the Standard toolbar. This copies the selected text
to the Clipboard.
4. Press Enter to end Edit mode.
5. Select cell A2.
6. Click the Paste button to paste the text into cell A2.
You also can use this technique to copy just part of a formula, to use that part in
another formula. Just select the part of the formula that you want to copy by dragging
the mouse; 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 because the formulas are being
copied as text, not as actual formulas.
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 to
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.
3. Click the Copy button on the Standard toolbar. This copies the selected text
to the Clipboard.
4. Press Enter to end Edit mode.
5. Select cell A2.
6. Click the Paste button to paste the text into cell A2.
You also can use this technique to copy just part of a formula, to use that part in
another formula. Just select the part of the formula that you want to copy by dragging
the mouse; 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 because the formulas are being
copied as text, not as actual formulas.
