Forum Discussion

JJVega's avatar
JJVega
Copper Contributor
Sep 26, 2023

Copying without refreshing references

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.

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

     

  • 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).
    • JJVega's avatar
      JJVega
      Copper Contributor
      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.

Resources