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*

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.

Brilliant – a very simple workaround. Thanks!

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!

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

Thanks!!!

Great. really useful….

This is really great….excellent work team..thanks once again

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

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

nice bro good one simple and ease

Brilliant work! Thank You!

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

this did not work