Shifting Formula by Multiple Cells

Gold Contributor

Jun 17, 2021

One possibility would be to do it manually by copying the cell / area with the formula with (Ctrl + C). Move the mouse cursor to the cell / area you have selected and press (Ctrl + V). The formula is now copied into the cell.

You can also use the simple mouse method by marking one cell / area and then moving the mouse pointer over the small square at the lower right edge and dragging it to the cell into which the formula is to be transferred. Unfortunately, this only works if the cells are adjacent to one another.

There is also a little trick to change the formula or the reference point. You need the dollar sign ($) for this. If you place the sign ($) in the formula to make the reference absolute, it can no longer be automatically adjusted by Excel. Example, if formula is: = A1 + A2, change it to = $ A $ 1 + $ A $ 2.

Finally, you can also use VBA, here the simple variant.

Sub Worksheet_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)

    Rows (Target.Row) .Copy

    Rows (Target.Row + 1) .Insert Shift: = xlDown

End Sub

That would be like "paste copied cells".

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

Forum Discussion

Resources