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:



and we want this:


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:

copy-and-paste-the-same-formula.xls28.5 KB
Useful's picture

Exact Copy of Formulas (with unusual method)

Video_011_Exact Copy of Formulas (with unusual method). With 8 method. The last one was discovered from my personal experience (solving with "Ctrl+~" and Clipboard trick).

(watch the new method from 3:47 sec)



Aydin Aliyev Microsoft MVP Excel