Excel Hack: Copy Formula Without Changing Cell References (or Without File References)
HTML-код
- Опубликовано: 21 фев 2018
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
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.
Get access to the full Excel Essentials Course 👉 www.xelplus.com/course/learn-...
✨ 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.
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
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
Discover more practical Excel tricks like the one in this video by joining our full "Excel Essentials" course here 👉 www.xelplus.com/course/learn-excel-essentials/
I'm shocked copying exact formulas to another location is not one of the basic functions of Excel. Thanks for this workaround!
I love it when a little creativity results in a simpler solution than complexity. Thanks Leila!
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!
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!
I'm glad I could help with that.
4/10/2020: Using Excel from 6 Years now, still learning and thanks to you..!
Thanks Leila, I was surprised that this wasn't a standard option in Excel, but your workaround has saved me a lot of time.
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 !!
Great advice. So simple but finding the solution was not intuitive at all. Thank you for the video.
That’s a super slick trick with the find and replace to get rid of those sheet references. As usual, thank you so much.
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.
What a simple but very smart and effective trick, thank you Leila.
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) )
You literally saved me from a mental breakdown. I want to thank you and wish you all the best things in life!
Happy to help! :)
You are a Genius !!!....I have a huge spreadsheet with hundreds of unique formulas...This is just brilliant
You can't imagine how much time it just saved for me and with just so simple a trick. Thank You!
been doing this trick
and also cut part of it, paste and drag to expand to do this similarly.
I didn't mean to compare anything to you.
You are ways more super inspirational. :-)
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)
So simple - and yet this one thing has been bugging me for YEARS!!!! Thanks so much! I knew I should have looked for this before now!!
Happy to help 😊
OMG. Simply genius. I just got done doing all of that by hand just earlier today. Thanks for the tip. I'll certainly remember that for the next time.
Sorry it came too late - but yes - for next time :)
Wonderful Leila! I have struggled many times copying and pasting manual... Even if the video is 4 years old it will create magic for me! :-) Thanks!!!
Glad it was helpful!
Wonderful, I have never thought of something that intuitive !! It saved almost 2 hours of my time where I was replicating an entire sheet into a power query !!
I'm glad I could help with that.
WOW! This worked. Incredible. Thank you so much! Saved me a couple hours.
So simple, Yet so amazing! Thank you!!! Saved like 5 hours of working for me!! :D
You are a real blessings to me. I can't tell how this site has been of a great importance to me in my excel lessons, just keep it up! 😍😍
You're amazing! Videos are quick to watch but have lots of clever tips to make life easier with spreadsheets. Keep them coming.
Thank you! Will do!
I am so thankful,, i was looking for a way to copy formulas from one sheet to another without going one at a time, cell by cell, fighting the editing mode function. you might want to re do this video looking at pasting between workbooks.
Just used the notinfile method to make 5616 substitutions for a work file. Saved me a lot of time. Thanks for this idea.
You are simply digging the hidden treasures of excel and spreading them to world👏👏
Glad you like the content :)
Fabulous! Thank you. This is should be a basic function in Excel. You have saved me a lot of time and effort!
You're very welcome!
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
I like your suggestion! I use FormulaText during training sessions so people can easily read the formulas. I like how you can apply here too. Thank you for the suggestion.
Doesn't work for me though.
To get this to work, select the cell(s) and then right-click and hold the edge of one of the selected cells and drag it/them to the right slightly and then back to their original location and release. A dialog box will appear. Click on Paste Here as Values Only and now you will see the formula(s) in the cell(s) as a text string. Use the keyboard shortcut Alt+A+E to open the Text to Columns wizard, then simply click on Finish and voila! your text string(s) are now formulas.
worked for me thanks
This method only seems to work one cell at a time though, rather than copying blocks of multiple cells containing formulae whilst maintaining the absolute cell references.
So creative, and saved me about 6 hours. THANK YOU!! LEILA FOR PREZ!
You just saved me hours of modelling ! Thank you so much !!
Thanks a lot for this hack! I never thought it could be so easy.
I been looking for this for so long....Thank you so much..
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!
Thank you Wayne. There are always great suggestions in the comments :)
Oh my goodness!!!! Thank you so much! This just saved me hours on this late night I'm already working!!!
great trick :-) I use sometimes the combination CTRL+' which copies the formula above, without changing references. Or CTRL+" which copies only the result of the formula above. But they work only with one formula at a time. Your trick instead is useful in many cases, thank you 🙂
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.
With people paying $49.00 without complaint for add-ins in an already expensive Excel, I don't see it happening!
Great tip! What I do is hit Ctrl ~ then I copy them and paste to Word, copy from Word, then I paste them back to Excel.
I never actually ever thought about replacing the = sign with a letter, i just use X, this has saved me hours of calculations in my head
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.
I use ASAP as well, William, and yes it's amazing! I'm wondering why though CUT and paste wasn't mentioned in this video, as that is pretty simple, too?
I am an Excel lover. I need to work daily on excel. I also check your links which is more useful in my. Professional, most of it I had applied. With your help I used to impress everyone in our monthly project meet. I will define you as "MAKING THE IMPOSSIBLE A POSSIBLE TRICKY HEAD". Am still learning from you. You are just unique than others who post about Excel.
Well myself Sarath Prabhakar from India. Am into garments manufacturing field. It will so thankfull if my files gets fine-tune, and I am working on it with the help of you.
Thank you. That means a lot from another Excel lover :)
Hi Gharani, thanks for your valuable reply, which I never expected. Well I like to ask you is there any video where all the formulas are applied in one single sheet which will be helpful for me to apply as many as possible. As my office work sheet extend till "HS" column a lengthy process is used under one item. Thank you.
Awesome Leila. Speeds up the job significantly!
Nice trick...
I required it many time, but didn't know how to do..
Thx
Thanks Leila. Great Trick. This helps alot, cause what I used to do is after I activate the formula with the F2 key, I put a space before the = sign and copy anywhere I want, then I remove the space. Your method is better and faster. Thanks again. :)
Glad this helps John! Thanks for your feedback.
This Hack basically saved me Hours of Work!!! Thank you so much!
Happy to help, Aleksi!
Great exposition. Very useful. Thanks!
This solution still works in late 2023 and was really helpful, thank you, saved me hours!
Glad it helped!
I love you so much! Been fussing what to do!! This helped me so much! 😭😭😭💯💯💯
So cool, I didn't know that u could use find & replace in this way. Most of the time I used find & replace to change the data, not the formula & still be able to keep the same cell references.
Yeah, it's a hidden feature that many don't know of.
So intuitive and simple. loved it thanks
Thank you so much for sharing this with everyone! Such an intuitive trick but it certainly never occurred to me. You are great!
My pleasure. Glad you something new.
Leila you are a lifesaver, thank you for this hack!!!
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
That's great! I'm happy the tutorials were helpful.
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.
You're absolutely right! It's not obvious to most that you can use find and replace in formulas as well....Thank you for your comment & your support Sachin.
Thanks Leila, Very useful and time saving.
Thank you so much I love your postings you are amazing in all ways
Thank you so much. I've saved hours of tedious pressing of f4
Great Video Leila. This is a really good tip. Thanks for taking the time in making this video. Keep up the great work. I enjoy watching the great insight you have with excel. 👍👍👍
I'm glad you find the videos useful Monte. Thank you also for your continuous support of my channel!
Not how I expected to fix the issue, thanks for the tip!
Thank You Gharani. It's work. U saved my time :)
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 "=".
Watched this on my browser, so I wasn't logged in, but I made sure I did so just to thank you. Your sacrifice was not in vain.
That is actual a genius solution! Thanks!
Hi Leila, I love your videos! They have helped me so much. I have a question. I used this method and recorded it to create a macro in vba to get rid of unwanted sheet references when I copy and paste data from sheet to sheet; however, I now have phantom links that I don't know how to get rid of. I now am prompted to update links to another file everytime I open original file. Is there a way to fix this?
Thanks leila, your videos are very useful
OMG I love your voice 💓 as much as your awesome insights in Excel!!!!
Big Salute to my favourite beautiful and Excel Teacher. This type of tricks please post often Madam. Waiting eagerly to learn more and more from you. You are the treasure house of Excel tricks.
You're very welcome Gopala! Glad to hear you like these type of tricks. Will make sure to post anything interesting I come across or get asked during the seminars. Thank you so much for your continuous support.
Great tip! This really saved me some time today
3 year could not find an easy way . wow it's so simple thank you
Glad it was helpful.
Much appreciated, it’s really helpful.
A very good tip can save a considerable amount of time! Thanks
You're very welcome :)
Thanks a lot. will use this onwards!
This was so helpful! Thank you, Leila!
Amazing video as always. Thank you.
That's a great trick! Well done. Thank you.
thank you! this saved me hours of work.
That's a great trick! Helps a lot. Thanks
Exactly what i needed a minute ago !...WowThanks.
Great explanation!! Thank you!!!
loved this simple solution, thank you, GOD bless you.
Wow...definetely an out of the box solution.
Thank you. Control H fixed all my problems
Leila thank you so much for this tip, it has saved me so much time. Your a star!
I'm glad to hear that Arshad :)
Wow so simple and easy but I didbnot though about it. THANKS !!!
Simply and amazing, that's what I was looking for.. thanks for sharing
My pleasure 😊
You're a life saver. Thank you very much.
This is BRILLIANT and saved me endless hours of work
Happy to help, Alicia.
Saves a lot of time, thanks!
BEAUTIFUL trick! Thank you so much - it has saved me for doing so much work
You're very welcome. I'm glad it was helpful.
Great tip, thank you very much!
Love the hack! Thanks for sharing.
Super simple and neat, thanks!
Brilliant. Thank you! I'm surprised there's not a way to do this that's built in.
Thank you thank you! This is a life saver
Ma'am we are anxiously for your valuable videos on Excel.Try to do more and more videos on excel too.
u have just saved my life, ty so much
Thank you for this helpful video!
Many thanks for this video. So helpful. Have done a video for copying conditional formatting?
Thank you so much! This has saved my sanity!!