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:
- Double-click A1 (or press F2) to get into Edit mode.
- 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 Shift+Home.
- Choose Home> Clipboard> Copy(or press Ctrl+C). This copies the selected text (which will become the copied formula) to the Clipboard.
- Press Esc to get out of Edit mode.
- Select cell A2.
- Home> Clipboard> Paste (or press Ctrl+V) to paste the text into cell A2.
You also can 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 are pasted to 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.
