Re: Copy Formula without changing cell refence

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Agustus's copy problem interested me as it's something I need to do
myself on occasion. I have been exploring a few ways to copy formulae
without changing the cell references using the keyboard (or mouse if
you prefer).
I know this is an Excel programming group but sometimes the good old
keyboard can be useful.
I'm using Excel version 2002.

Here are a couple of solutions someone might find useful.


1. Copying formulae from a range of contiguous cells. (eg D5 to
G10) without changing the cell references in the formulae.

a) open up a text editor ( Notepad , Word . . .whatever you like)
b) In Excel change the view option on the work*** to show cell
formula rather than values.
The quickest way to do this is to press Ctrl + ` (that's a
single left quotation mark)
This key combination toggles the display between value and
formula view.

c) Select the cells you want to copy the formulae from
d) Press Ctrl+C (to copy to the clipboard)
e) Swap to your text editor
f) Press Ctrl + V to paste the formulae.
g) In the text editor select the text you have just pasted
h) Press Ctrl + C to copy it
i) Back in Excel select the top left hand cell of the range you want to
paste into
j) Press Ctrl + V to paste the formulae
h) Press Ctrl + ` again to put Excel back in view "values" mode. This
step can be done after step (d) if you prefer.

I was surprised at how well this copy method works. Even if the source
range you have copied contains a number of blanks cells Excel still
pastes the text in the clipboard into the right target cells.

2. Copying a formula from a "single" cell without altering the formula

The trick here is to copy the formula FROM THE FORMULA BAR rather than
from the source cell. This can be done by :
a) select the cell you want to copy
b) In the formula bar select all text that makes up the formula
c) Press Ctrl + C to copy to the clipboard
d) select the cell you want to paste into
e) Press Ctrl + V to paste the formula

Cath

.

Quantcast