Cell A1 has the value 1. Cell A2 has the value 2. Cell A3 has the formula entered " = A1 + A2" and shows the value 3. Similarly Columns B1,B2, B3, C1, C2, C3 etc.. etc..

I want to copy the formula (in A3, B3 etc) into cells A4, B4 etc such that A4 reads "A1 + A2", B4 reads "B1 + B2" etc. Can this be done ?

Perhaps the request appears odd, but only because I have simplified my project to show the essence of the problem. I need to do this to check for repetitive entry, if any of the same expression, say A1 or A2 or C3, in more than one formula.


Rather than using:

=A1 + A2


=$A$1 + $A$2

this will copy without adjustment.

  • I need cell A4 to read: A1 + A2 – WDR Jun 5 '17 at 2:08

The ideal way is to use absolute references as in @Gary's Student's answer. But if you do not want to change the original formulas, then there are other workarounds.

One clever way is to copy a whole range of addresses by first removing the equals sign using Find and Replace (or by replacing with a= as suggested here). Then copy and paste, and finally change it back to =. This is illustrated in Method 3 at https://www.ablebits.com/office-addins-blog/2016/01/20/copy-formula-excel/#copy-formula-without-changing-references

What I have used in the past is the following key sequence:

  1. Edit the formula: F2
  2. Select the formula: Ctrl+a
  3. Copy the formula: Ctrl+c
  4. Exit the formula: Esc

Now paste the unchanged formula.

If this is something that you need to do often, it may be worth setting up a macro like this one, which does steps 1-3.

Sub CopyLiteralFormula
    document   = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    dispatcher.executeDispatch(document, ".uno:SetInputMode", "", 0, Array())
    dispatcher.executeDispatch(document, ".uno:SelectAll", "", 0, Array())
    dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
End Sub

I played around with it for a few minutes but couldn't get it to do step 4 automatically.


enter image description here

1) Select A3:C3, Ctrl+C

2) Select A4, Ctrl+V

enter image description here

3) Ctrl+X

4) Select A5, Ctrl+V

enter image description here

5) Ctrl+C

6) Select A4, Ctrl+V

After that, A4:C4 is the same as A3:C3, And we can clean A5:C5, if needed:

enter image description here

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.