The last two tricks were quite creative. Thanks! I can't believe it's 2020 and they haven't integrated this option into the "Paste Special" options yet.
Its not, here is the easiest way: Copy the formula you want to the clipboard (just the text with the = sign). Select the range you want the same formula copied to. Chose Replace, leave the Find what field blank, and paste the formula with the = sign into the Replace with field and choose Replace All. It will fill the same formula in all the cells.
@@wilmarkjohnatty4924 That's fine for a single formula. I frequently work with an array of formulas that need to be copied. It's been a couple years since I left my comment, but the find/replace with # trick is still the only way to do this efficiently.
Thanks.....It makes my work easy. I Use your tricks this way =IF(L3="Include", IF('Data PVT'!$C$4="KTGL",'Data RAW'!I3,IF('Data PVT'!$C$4="PallMall",'Data RAW'!J3,IF('Data PVT'!$C$4="Others",'Data RAW'!K3,'Data RAW'!I3+'Data RAW'!J3+'Data RAW'!K3))), "")
Sorry i was in hurry and could not write the complete review. This learning was very very helpful, I was wondering how would I manage more then 6k data by doing F2 and enter in each cell. But this video helped me alot. Thanks once again
I want a formula for variable cell reference. I am having a row of 100 number A1 to A100. I am having a other row of 100 numbers in column B1 to B100 Now in Column C, I want to average A1 to A100 based on value on column B1 to B100 Suppose if $A$1:A10 = 50, and at that time B10>0, now for next averaging I want to use formula $A$11:A12, $A$11:A13,$A$11:A14 and so on. Again if anytime, say $A$11:A20 = 100 and same time B20>0, I want to change average formula from next cell and it will become $A$21:A22,$A$21:A23, $A$21:A24 and so on Pls guide
Brilliant, indeed... but, in 2020 we cope with this basic problem... Excel should have implemented already a kind of "special paste", which means, "no relative formulas change" or sg. like this and then we won't need to make such a tricky things...
I have a very specific problem. I want to copy the information of a cell into the formula bar but excel wont let me. I normally use it as a shortcut when using equations. Is this how excel just is or is something wrong with my settings. Ive been using google sheets since november last year and this never was a problem. an you shed any light on this?
i watched videos after videos, went through pages after pages to come to you and it was as easy as pressing F4 to lock a reference so autofull wouldnt piss me off. Thank you bro!
I have a summary sheet to where I would like to copy content from the same cell from each sheet from the workbook. I can luck the cell address but how can I tell excel to change sheet name in the copy function? =sheetname1!$d$2, then the next cell should be "sheetname2!$d$2. I do not wan to manulla edit the name of sheet. Is there a way to automatically change the sheet name?
hey man, been using this trick for years but recently when I try to paste the formulas from notepad back to excel, it copies the whole range as text in the same cell, instead of respecting the range structure. Has this happened to you before?
when I copy it in notepad using 3rd show formulas method, it's not pasting the formulas in notepad, instead it is copying the values....can you suggest on the same please
What if it is a table with multiple cells and multiple formula's. I want to copy a table with out changing cell references. I want to copy table as it !
I used to use the notepad method all the time but for some reason when i paste into notepad it take the value and not the formula every time. Do you know what's going on here or how to fix this?
Thank you v.much after watching a lot of videos and sear 100s of pages I found your video...This is v. v. helpful for me and it made my day.,,,Thanks a lot.
That F4 function to use fixed cell references is extremely useful. That is something new I learned from you. But what if I have a column of numbers and I want to return the result of dividing each number by the same number which referenced in absolute cell? If my absolute cell containing the divisor is T1 and my numbers are in Column R and the result in Column S. How do I get the result in S2,S3,S4, etc to be R2/T1, R3/T1, R4/T1 etc and not R2/T2, R3/T3, R4/T4, etc?
You are a life saver! you have no idea how much time you just saved me! I like the show formula and then copy it to the note pad! simple but very effective!
There is afar easier way to do this. Copy the formula you want to the clipboard (just the text with the = sign). Select the range you want the same formula copied to. Chose Replace, leave the Find what field blank, and paste the formula with the = sign into the Replace with field and choose Replace All. It will fill the same formula in all the cells.
Anyone know how to stop the reference from skipping numbers when pasting a formula into every other row? I am using =IF(NAMELIST!$D1="","",NAMELIST!$D1) but in row three it uses D3 then 5 then 7. I just want to skip rows but count normally D1 D2 D3. Any ideas?
Hi everyone! say I put a formula in cell A1 that is =SUM($C$1:$C$100). and in A2 =SUM($D$1:$D$100) now what to do If I drag down to past the formula below in cell A3 so that its shift to coloum E and I I continue to drag than Its automatically choose the cells F,G,H,I,J,K,L,M,N???
I've been looking for a way to explain youtube and google what I meant by "lock" a cell when copy and paste or autofill formulas and you just responded to that by using the phrase "making a cell absolute". Thank you Time-Saver Wizard.
Thx Trump Excel! I have a problem and I do not know if there is a solution for it. In each cell of a certain row range, I placed the same formula. This formula is not fixed. Would it be possible to change the formula in the first cell of the row, so that all other cells in this row range are immediately adopting that changed formula? I would tend to think you have to link a cell to another cells formula, not its value. Is this possible?
Thank you! On my own, I did the # trick by using replacing = with '= (added apostraphe ), but excel doesn't allow you to reverse the find/replace back to original, because it no longer recognizes the ' as a character to remove. Thank you!
![CAMPปลิ้น | EP.82[1/2] แก๊ง 3 สาวเพื่อนซี้บุก CAMPปลิ้นครั้งนี้ห้ามเสียอาการ](http://i.ytimg.com/vi/_6WUjXHe1TM/mqdefault.jpg)
Omg thank you so much! I've been going crazy having to edit them every time I copy/paste. This is genius!!
Glad you found the video useful Amy!
@@trumpexcel kkvv bbvaiejemmenejejenejwjhhbkoejjekin3nrbebkwownenekrjnenrnjdjrjekrirjrjndjdndndudhdjudjdnwihe ballistic coulomb fingjr2ejnenennsnsmmskwkwmwmmendndndndnsnnd dnsnjsjsnshsbd dudjeeueheiwwuhw8wuyhhuhjj--666463161543449=1=4=464913*6+9*96+6+9*96+6+6+99*9(9(9(% 649464397 %,69
Thank you so much I looked at four other guides and didn't understand but you explained it perfectly!
The last two tricks were quite creative. Thanks! I can't believe it's 2020 and they haven't integrated this option into the "Paste Special" options yet.
Its not, here is the easiest way: Copy the formula you want to the clipboard (just the text with the = sign). Select the range you want the same formula copied to. Chose Replace, leave the Find what field blank, and paste the formula with the = sign into the Replace with field and choose Replace All. It will fill the same formula in all the cells.
@@wilmarkjohnatty4924 That's fine for a single formula. I frequently work with an array of formulas that need to be copied. It's been a couple years since I left my comment, but the find/replace with # trick is still the only way to do this efficiently.
I agree, kind of silly that they ignore this use case
Very helpful, thank you sir!
what about if you want to copy the cell contents into another tab - this is proving hard!
Very nice
Thanks.....It makes my work easy. I Use your tricks this way =IF(L3="Include", IF('Data PVT'!$C$4="KTGL",'Data RAW'!I3,IF('Data PVT'!$C$4="PallMall",'Data RAW'!J3,IF('Data PVT'!$C$4="Others",'Data RAW'!K3,'Data RAW'!I3+'Data RAW'!J3+'Data RAW'!K3))), "")
Hi thanks for this video. When I did find and replace = to #, it inserted an apostrophe. How do I replace = to # w/o the apostrophe?
In my darkest hour you were there for me. I swear on my life and honour that you may call on me in your time of need.
omg i was having a hard time to figure this out... thank you
After several hours of hunting through youtube videos... this once finally got the solution I was after! THANKYOUI!
WOW, THANK-YOU, THANK-YOU, AND THANK-YOU AGAIN. This has just saved me a ton of time. I'm very appreciative. Excellent video.
Thanks a bunch. Clear, accurate, and it WORKS!! Who could ask for anything more?
PURE GENIUS. I have been searching for an easy way to copy multiple cell's formulas without changing the cell references. Wonderful techniques.
Sorry i was in hurry and could not write the complete review.
This learning was very very helpful, I was wondering how would I manage more then 6k data by doing F2 and enter in each cell. But this video helped me alot.
Thanks once again
Thanks alot. Love from Pakistan
I want a formula for variable cell reference.
I am having a row of 100 number A1 to A100.
I am having a other row of 100 numbers in column B1 to B100
Now in Column C, I want to average A1 to A100 based on value on column B1 to B100
Suppose if $A$1:A10 = 50, and at that time B10>0, now for next averaging I want to use formula $A$11:A12, $A$11:A13,$A$11:A14 and so on.
Again if anytime, say $A$11:A20 = 100 and same time B20>0, I want to change average formula from next cell and it will become $A$21:A22,$A$21:A23, $A$21:A24 and so on
Pls guide
Great tip! Straight to the point I really appreciate your help. Thank you!
Thanks for this. The find & replace method is exactly what I needed, simple and elegant.
Neat tricks, but i don't understand why Excell just doesn't allow you to just copy paste with fixed reference.
thanks a lot .... your explanation was very easy and clear
Glad you found the video useful
Thank you so much! I needed to figure out the F4 thing and thanks to you I'm able to fix my issue!
Brilliant, indeed... but, in 2020 we cope with this basic problem... Excel should have implemented already a kind of "special paste", which means, "no relative formulas change" or sg. like this and then we won't need to make such a tricky things...
I have a very specific problem. I want to copy the information of a cell into the formula bar but excel wont let me. I normally use it as a shortcut when using equations. Is this how excel just is or is something wrong with my settings. Ive been using google sheets since november last year and this never was a problem. an you shed any light on this?
i watched videos after videos, went through pages after pages to come to you and it was as easy as pressing F4 to lock a reference so autofull wouldnt piss me off. Thank you bro!
I have a summary sheet to where I would like to copy content from the same cell from each sheet from the workbook. I can luck the cell address but how can I tell excel to change sheet name in the copy function? =sheetname1!$d$2, then the next cell should be "sheetname2!$d$2. I do not wan to manulla edit the name of sheet. Is there a way to automatically change the sheet name?
hey man, been using this trick for years but recently when I try to paste the formulas from notepad back to excel, it copies the whole range as text in the same cell, instead of respecting the range structure. Has this happened to you before?
when I copy it in notepad using 3rd show formulas method, it's not pasting the formulas in notepad, instead it is copying the values....can you suggest on the same please
You just saved me HOURS of drudgery. I was afraid I was going to need to do this one cell at a time. Thank you!
Example in cell A value is 11 and in cell B the value is 21 , these two values should to copy in cell C. Like this I want to do so many rows
What if it is a table with multiple cells and multiple formula's. I want to copy a table with out changing cell references. I want to copy table as it !
Thanks for making Excel great again
Thank you oh oo !! sooo much !!!
Thank you! It does seem ridiculous that there isn't an option in paste for this after copying though.
I used to use the notepad method all the time but for some reason when i paste into notepad it take the value and not the formula every time. Do you know what's going on here or how to fix this?
brilliant tricks. but its 2020 now and nothing has been fix by lousy microsoft.
use the f4 and freeze the column only then we can copy-paste easily without changing the reference cell
Thank you v.much after watching a lot of videos and sear 100s of pages I found your video...This is v. v. helpful for me and it made my day.,,,Thanks a lot.
Glad the video helped 🙂
Hard to believe we still have to resort to these tricks to do something as basic as pasting fixed formulas.
i dont know why its always add '# ARGHHH im frustrated now
Thank you.
Very useful and your English accent is great
OMG! After hours of being stucked finally someone explained it properly Congratulations great job!🥰
OMG this is so not worth it, unless you have more than 100 formulas to copy
Thank you for sharing this video,, yes very helpful in my work
Neat Trick 100%. Thank you for making this video!
Thank you so much this was very helpful. Have a nice day!
Best tutorial ever
Thank you!
afte rdoing this every cell says '= and this is text instead of calculating
Thank you so much for your content Sir, very helpful. ..
Greeeeat tip! Saved me a lot of time! Thank you very much!
I thank you
What if we have to copy these such cells in from Sheet1 to Sheet2 ?
Thanks - saved me a load of repetitive work .....😁
How to copy number from excel cell if formula is applied
Brilliant Thanks for this saved me hours of looking at documentation!
Thank you! Using F4 made all the difference
That F4 function to use fixed cell references is extremely useful. That is something new I learned from you. But what if I have a column of numbers and I want to return the result of dividing each number by the same number which referenced in absolute cell? If my absolute cell containing the divisor is T1 and my numbers are in Column R and the result in Column S. How do I get the result in S2,S3,S4, etc to be R2/T1, R3/T1, R4/T1 etc and not R2/T2, R3/T3, R4/T4, etc?
I'm trying to figure this out too!
Thanks you so much for F4 Key to lock cell reference.
Thanks SO MUCH! U saved me from having to manually edit at LEAST 720 cells!! WOW! Subbed and Liked! Keep up the great vids! Awesome!
Thanks my friend, that saved my life 🙂
Thank you so so much! It truly saved me :)
You are a life saver! you have no idea how much time you just saved me! I like the show formula and then copy it to the note pad! simple but very effective!
Glad you found the video useful!
great options thank you
Ditto what Amy Koetz said. Thanksh much.
I love your video 😍😍💕😍😍😍😍😍😍😍😍. I learned amazing trick
You are an absolute legend, big thanks!
Thanks! I like the formula paste trick
Thank you so much! You have saved me so much time
There is afar easier way to do this. Copy the formula you want to the clipboard (just the text with the = sign). Select the range you want the same formula copied to. Chose Replace, leave the Find what field blank, and paste the formula with the = sign into the Replace with field and choose Replace All. It will fill the same formula in all the cells.
Find and Search helped me! Thank you !
Clear explanation, and to the point. I love your work and content sir.
Thank You :)
Love you , solved one of the tricky solution.
thank you so much! you're a great help!
Man!!!! I love youuuuu!!!!! Thank youuuuuuuuuuuu.....!!!!
Anyone know how to stop the reference from skipping numbers when pasting a formula into every other row? I am using =IF(NAMELIST!$D1="","",NAMELIST!$D1) but in row three it uses D3 then 5
then 7. I just want to skip rows but count normally D1 D2 D3. Any
ideas?
It worked.
Thank you very much.
Wow! Great ideas. Thanks man.
copy cell directly in notepad again copy from notepad and paste in column it work perfectly.
WOW huge time saver - THANK YOU!!!
THANK U SO MUCH BROTHER 🥰🥰🥰🥰🥰🥰🥰🥰🥰🥰🥰
Hi everyone!
say I put a formula in cell A1 that is =SUM($C$1:$C$100). and in A2 =SUM($D$1:$D$100) now what to do If I drag down to past the formula below in cell A3 so that its shift to coloum E and I I continue to drag than Its automatically choose the cells F,G,H,I,J,K,L,M,N???
I've been looking for a way to explain youtube and google what I meant by "lock" a cell when copy and paste or autofill formulas and you just responded to that by using the phrase "making a cell absolute". Thank you Time-Saver Wizard.
THANK YOU!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Thanks for your tutorial. It's still beneficial even after 7 years you made it. Congrats!
# technic is great ! Thank you!
I want a value of cell B based on Cell A value
great job. thank you for the help
AWESOME!!! Thank you sir!
what if want to copy in another sheet?
Thank you so much my indian friend
That was actually helpful, thanks
Thank you so much ... I have been searching for a solution for few hours already ❤️🙏
THANKS SUCH A BIG HELP
DUDE! That notepad tip saved my life and sanity. Thank you 👍👍👍👍
Thx Trump Excel! I have a problem and I do not know if there is a solution for it. In each cell of a certain row range, I placed the same formula. This formula is not fixed. Would it be possible to change the formula in the first cell of the row, so that all other cells in this row range are immediately adopting that changed formula? I would tend to think you have to link a cell to another cells formula, not its value. Is this possible?
Thx!! U a life save!!!!!
CAN IT BE DONE FOR DIFFERENT SHEETS?
Thank you very much.
Thank you! On my own, I did the # trick by using replacing = with '= (added apostraphe ), but excel doesn't allow you to reverse the find/replace back to original, because it no longer recognizes the ' as a character to remove. Thank you!
Thanks man. problem solved just at 1:21 minute
Glad it helped :)