Excel Tip > Excel Functions List > Excel Text Formulas > Copying Formulas from a Range of Cells Without Changing the Absolute or Relative References in Excel

Copying Formulas from a Range of Cells Without Changing the Absolute or Relative References in Excel

Fb-Button

In this article we'll learn how to copy formulas in Excel without changing the cell reference or in simple words we can say that how to copy exact formula from one cell to another cell.

There are two ways which you can follow to copy formulas from a range of cells without changing the Absolute reference or Relative reference in Microsoft Excel.

Let’s take an example to copy the formula from a range without changing the cell reference. We have a score card, in which column A contains Student’s name, column B contains scores and column C contains the formula for grades as per the criteria.

 

img1

 

Now we need the grade to be copied to another location instead of column C, along with the formulae.

Follow the below given steps to copy the formula to another location: -

    • Select the range of cells containing the formulas and press “CTRL+H”.
    • In the Find what box, type the = sign.
    • In the Replace with box, type the # sign (to change the formulae to text).
    • Click Replace All, and then click on Close.
    • You will get a popup saying “All done. We made 22 replacements”.
    • Click on Ok.

 

img2

 

  • Copy and paste these cells to a new location by pressing the keys “CTRL + C” to copy and then “CTRL+V” to paste it.
  • Select the new range containing the formulae and press Ctrl+H.
  • In the Find what box, type the # sign.
  • In the Replace with box, type the = sign (to change the text to formula).
  • Click on Replace All and then click on Close.
  • You will get a popup “All done. We made 22 replacements”.
  • Click on Ok.

 

img3

 

You can then revert the column C to its original formulae instead of text, by replacing the “#” to “=” following the above steps.

Incase you want to move the formula to another location, you can follow the below given steps –

  • Select the range of cells containing the formulas and cut the formula by pressing the key “CTRL+X” on your keyboard.
  • Paste the range to the new location by pressing the key “CTRL+V”.

 

img4

 

To remove the formula from data we can use the “Paste Special” option:-

  • Select the range E2:E12, copy by pressing the key “CTRL + C” on your keyboard.
  • Right click on the mouse and select “Paste Special”.
  • In the dialog box select values and click OK.
  • The formula will be replaced by its values.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Previous
Next

Comments

  4. shine narayan

    Its a awesome trick for multiple formula copy and paste to new destination without changing cell reference.
    thank you very much !!

    Reply ↓
  14. Jacob

    I use a small macro:

    Sub COPYPASTE()
    Set Inputtable = Application.InputBox(prompt:="Input", Type:=8)
    Set Outputtable = Application.InputBox(prompt:="Output", Type:=8)
    Number_of_rows = Inputtable.Rows.Count
    Number_of_col = Inputtable.Columns.Count
    For j = 1 To Number_of_rows
    For i = 1 To Number_of_col
    Outputtable(j, i).Formula = Inputtable(j, i).Formula
    Next
    Next
    End Sub

    Reply ↓
  15. Mary Ford

    Thank you for maintaining this site and posting the simple solution with screen shots. You have saved me hundreds of hours on a consulting project I have been tasked to fix. Forever grateful!

    Reply ↓

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Excel Tips

Categories

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.