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

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.

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.

• 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.

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”.

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.

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

Comments

1. Simple - effective - love it!

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

3. Life saver. It's incredible you need to go to such lengths to do a simple copy/paste action.

4. Brilliant work! Thank You!

5. nice bro good one simple and ease

6. This is incredible. Just saved me hours of work. Thanks!

• Hi Luke,

Thanks a lot for taking time for appreciating this tip. 🙂

Happy Learning,
Site Admin

7. What a fantastic trick! It's perfect. That's what I've needed to know for a long time. Simple and easy. Thanks!

8. Thanks!!!

9. 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

10. 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!

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.