Copying without refreshing references

Copper Contributor



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.

5 Replies


Let's say you have a formula such as


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:


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).
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!


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.

@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!




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