Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Copying without refreshing references

Copper Contributor

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.

5 Replies

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

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

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

 

@SanthoshKunder,

 

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

 

Thanks!