Copying without refreshing references

[JJVega]

Hello, 

I'd like to copy a formula in a cell to another cell without refreshing references, I mean, exactly the same formula en other location. I know that i can move the formula with "CtrlX" but I want to do it without cutting the previous formula (I want a copy process). Is it possible?

Thanks in advance.

[#2]

@JJVega 

Let's say you have a formula such as

=SUM(A1:A4)

If you copy the cell with the formula and paste it elsewhere, the range in the formula will change. You can prevent this by making the range reference absolute:

=SUM($A$1:$A$4)

The range will then remain the same when you copy/paste the cell with the formula.

Another option:

• Select the cell with the formula.
• Press F2 to edit the cell, or click in the formula bar.
• Select the entire formula (Ctrl+A) and copy it (Ctrl+C).
• Press Esc to stop editing the cell.
• Select the target cell and paste (Ctrl+V).

[#3]

Thank you Hans,

The absolute reference is a good point however I can't use it because I'd like to drag the formula to other cell from the new position so I need to conserve it with no dollars.

Copying the formula from the formula bar is the option that I'm using right now but I was wondering if an automatic process without cutting exists.

Thank you so much for your response!

[#4]

@JJVega 

I'm not aware of another built-in way to do what you want.

A VBA macro would be another possibility, but that is probably overkill.

[#5]

@JJVega - If you don't want to change the reference to absolute then this should help.

Let's say you have this formula in cell B1 = SUM(A1:A5). We first need to replace = with #. We can use CTRL+H to do this. Now formula is gone in cell B1 and only the text #SUM(A1:A5) is left. 

Copy the text #SUM(A1:A5) , paste it in the cell you want and do the reverse replace( # to=)

Hope this helps!

[#6]

@SanthoshKunder,

That is a good idea (#/=) when a massive copy is needed.

Thanks!