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: -
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 –
To remove the formula from data we can use the “Paste Special” option:-
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
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.
Simple - effective - love it!
Hey, it was incredible .... simply the best.....
A Big Thank You...You are a Genius!!
Its a awesome trick for multiple formula copy and paste to new destination without changing cell reference.
thank you very much !!
this did not work
Life saver. It's incredible you need to go to such lengths to do a simple copy/paste action.
Brilliant work! Thank You!
nice bro good one simple and ease
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
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 really great....excellent work team..thanks once again
Great. really useful....
Thanks!!!
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
that is the best way.
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!
Brilliant - a very simple workaround. Thanks!