Excel Hack: Copy Formula Without Changing Cell References (or Without File References)

Are you struggling with copying Excel formulas without changing cell references? This video is perfect for anyone who needs to copy formulas in Excel while keeping the original cell references intact.

It's especially helpful if you're dealing with a mix of relative and absolute references. Our expert guides you through a practical example, showing you how to maintain these references even when copying complex formulas across different cells and ranges.

✨ What You'll Learn:
▪️ The common challenge of copying formulas in Excel without altering cell references.
▪️ A step-by-step tutorial using a sample dataset to illustrate the process.
▪️ An efficient method to copy a range of formulas while preserving the original cell references.
▪️ How to use the Find and Replace function (Ctrl+H) in a creative way to achieve this.

Excel default behavior is to adjust the cell references when you copy a cell and paste to another one. You can choose to fix the cell reference by using the dollar sign ($). A $ for the row number, fixes the row and a $ on the column number fixes the column.

You might however need to copy a set of formulas that have relative references from one location to another or from one file to another and keep the existing cell references. This video shows a very simple trick you can use.

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#Excel

[jorisvanh.]

I'm shocked copying exact formulas to another location is not one of the basic functions of Excel. Thanks for this workaround!

[waverider]

I love it when a little creativity results in a simpler solution than complexity. Thanks Leila!

[Antonied]

Use =FORMULATEXT(cell); copy paste the result as value into the same cell - this will return the string of the original formula; use find and replace to replace the "=" with the same "=" this forces the cell to treat the string as formula - voila. I think its a bit shorter than the one suggested in the video but i like it more because it seems more sophisticated.
You can do _AMAZING_ things with FORMULATEXT - like for example manipulate/change multiple formulas by treating them as strings; using LEFT&VLOOKUP&RIGHT to omit the reference and insert a new one and then at the end just select range and replace "=" with "=" - super clean

[msheaver]

Holy cow, that is genius! For years I have used the F2-copy-paste song and dance, and I never thought of using the find and replace before. This is indeed a brilliant hack!

[axifwalu]

4/10/2020: Using Excel from 6 Years now, still learning and thanks to you..!

[aarinisles]

For sheets with a lot of calculations that I may need again somewhere else I use Show Formulas and copy all the formulas and paste in a text editor, (I like Notepad ++). Then they are available whenever I need them. This also works if you want to copy the whole sheet with text and whatever else is there. When you paste that from the text editor use Format Painter with the original sheet and apply to the new sheet.

I have found it really useful in a number of ways beyond the above to copy and paste formulas in a text editor. Just thought I would throw that out there.

[tonyman]

Great advice. So simple but finding the solution was not intuitive at all. Thank you for the video.

[SmeetGajjar]

This worked perfectly in 24, 875 cells !! 😂
I have been trying to copy Table from one workbook to another, but it was pasting link before formulas.

After being tired i searched on Google and got your video, that made everything perfect within minutes...
Thank you so much !!

[radicholakov]

I like the find/replace technique a lot. Besides moving the formulas I find it quite useful when working on a bit more complex formula that return the result in a single cell.
I wanted to calculate workdays (Mondays to Fridays) count and I was having some errors in the result when trying to just directly write it in one cell, however when braking down each element into single cells, combine it all and replace I got it done. :)

find / replaces:
end -> end date reference
start -> start date reference
magic -> =

Guess there might be a build in function for that, but here is my formula:
magic(WEEKNUM(end, 2)-WEEKNUM(start, 2))*5+IF(WEEKDAY(start, 2)<magic5, 5-WEEKDAY(start, 2)+1, 0)-IF(WEEKDAY(end, 2)<magic5, 5-WEEKDAY(end, 2), 0)

[bornbfit]

That was amazing! I have been trying all day to get my references to stay the same, and in 30 seconds after performing this hack, you saved my the rest of my night! Cheers to you!

[axel]

3:00 for copying multiple cells without changing cell references.
Tl;dr: Use find and replace to change the "=" in all formulas of the cells to e.g. "@", then copy the cells in replace back to "=".

[andrewmoss]

This is really useful. I personally have the Kutools add-in which makes doing an identical copy and paste a lot easier. But for those who don't, it's good to know there's a way. I do think Microsoft should consider including the ability to do this in Excel because there's certainly been many times I've needed to.

[wayneedmondson]

Hi Leila.. cool trick. I tried some of the ones below suggested by others (=FORMULATEXT() and copy to Notepad). While some of the others work, I like your solution the most.. everything done from CTRL+H / Find and Replace. If needed often, it could easily be automated with a Sub and a button on the worksheet or the QAT to do the first replace of "=" with a placeholder and then switch it back in reverse. Excellent! Thumbs up!

[williamcurto]

I use ASAP Utilities for these kinds of things....most valuable Excel add in I've ever come across. Been using it since mid-2000's. It's free for home use, but I believe in it so much that I've paid for it to use at work.
Your example, Leila, is a nice easy way around though.

[monwaranik]

your tutorials are always helping me in my professional career

[kadreanaraymond]

Great tip! How would you do this if you want to paste the formulas into another worksheet without changing the cell reference?

[stefanogattoCH]

Thank you Leila! And this trick also works when the equal sign is used in the middle of the formula too! (e.g. =IF(A3=300, 1, 4) )

[riazomar]

What about copying formulas on different tabs for data validation list to another tab in data validation lists, keeping the formulas the same for both tabs

[sachinrv]

That's a useful quick trick. Thanks for sharing.
Leila; as I have observed many a times people don't know (or even if they know tend to do it long way) that in Excel one can even change the ranges or cell reference given in the formula which already exist by using FIND AND REPLACE. Like for e.g. by mistake one types =Sum(a1:a5) instead of =Sum(b1:b5), herein one can easily replace "a" with "b" to have sum of range b1:b5.

[BattleBladeWarrior]

Holy cow Leila, I spent 7 hours trying to write up formulas in excel for one of our spreadsheets at work, and NOTHING was working. Then I found your channel. . . and between your video on sumifs/countifs and this one, I was able to write all the working code within 20 mins. Thank you so much for this. You're a gem :D