73. Excel Tips - Copy And Paste The Same Formula

Nick's picture


Why is there no easy way to copy and paste the same formula ?

  • Copy and paste will change the cell references if the references are not fixed.
  • Cut and paste removes the formulae from the original place and changes cell references if not fixed.

So, to explain more... We have this:

copy-and-paste-the-same-formula

 

and we want this:

copy-and-paste-the-same-formula

NOTE - with exactly the same formulae in the newly populated cells.

 

There are 2 ways to do this operation

Solution 1 Steps:

  1. Select each cell one by one 
  2. Press F2
  3. Shift + HOME
  4. Copy
  5. Paste where you want the replica formulae to be
  6. Repeat for each cell

- This might work for a few cells... but what about a lot ? It might take you all day !

 

Solution 2 Steps

  1. Press CTRL + button to left of 1
  2. Select the cells containing the formulae (C7:C9)
  3. Copy
  4. Open a notepad
  5. Paste
  6. Press CTRL + A
  7. Copy
  8. Select D7:D9
  9. Paste
  10. Press CTRL + button to left of 1

My preference is Solution 2 as it's less prone to mistakes..

Download sheet to practise how to Copy And Paste The Same Formula in Excel

Training Video on how to Copy And Paste The Same Formula in Excel:

AttachmentSize
copy-and-paste-the-same-formula.xls28.5 KB