Tom’s Tutorials For Excel: Copying Formulas While Keeping Their Relative and Absolute References
Here’s how you can copy a set of formulas and paste them elsewhere, while keeping the original formulas unaffected and keeping the relative and absolute references unchanged.
Before the copy and paste.
After the copy and paste. Notice the same formula in cell G2 as in cell B7.
Select the range and press Ctrl+H to show the Find and Replace dialog box.
In the Find what field, enter the = character.
In the Replace with field enter some unused character, for example the # character.
Select the formula cells, right-click the selection and click Copy.
Right-click the upper right corner destination cell, and click Paste.
Select the entire used range and press Ctrl+H again to show the Find and Replace dialog box.
In the Find what field, enter the # character to reinstate the formulas.
In the Replace with field enter re-enter the = character.
Hi,
to do so I select in the formula bar, right click, copy, then click annulate (red cross on the left of the fx symbol on the left of the formula bar), paste.
Nothing you wrote makes sense. Annulate is an adjective to describe a ring form or ring shape, as an earthworm’s ringed pattern on its body. The red cross to the left of the fx symbol is the Cancel button which negates whatever you are doing. Nothing was copied in your comment’s instruction because you clicked the Cancel button, so nothing could be pasted, and even if it was, the point of my example is to show how the values and formulas can be shown together on the screen at the same time in the same range, which could not be the case even if your example worked, which it doesn’t.
Hi Tom,
I think what Jan is describing is to copy the cell’s contents as text, and then pasting in the destination cell. While this will work it will only allow for 1 vell to be copied at a time, unlike your example (very good tip by the way, thanks!) – What Jan mentions is basically the process I use, albeit I’ll use keyboard shortcuts (F2, ctrl+a, ctrl+c, esc, then paste into the destination cell.)
But thanks again, very useful trick 🙂
Thanks, Will. This was so long ago I had to go back and look again at what this was about. It turned out that I never heard back from Jan after my reply in December 2018, so I assume it was resolved, but thank you for clearing up the mystery.
many thanks