Excel Tutorial: How to automatically copy data from one Excel worksheet to another
Вставка
- Опубліковано 8 вер 2024
- Excel Tutorial - How to automatically copy data from one Excel worksheet to another without using visual basic with usemybox
Remember to subscribe to Usemybox for computer how to videos in Excel, Word, Powerpoint, Windows, internet browsers and more.
Subscribe for More IT & Software How To Videos:
ua-cam.com/users/us...
Checkout this tip to automatically copy a range of data in Excel: ua-cam.com/video/WC5ofDFwesw/v-deo.html
Thanks alot. I have been copy pasting manually data from one week to another fro like 5yrs now!
Thanks! Happy the video was useful for you
Thank you so much for this. This is brilliant and going to make a huge difference to how I do things. Up to now all i used to do was copy and paste.
Thank you. We're very happy this video was helpful to you
Thank you so much! I lost two days to find this solution. God bless man!
Very happy this solution worked for you. Please check my other videos for more helpful IT and Software videos. Thanks! 🙂
Thanks... sddenly i required this formula and typed on youtube,,,, and whatsoever was in my mind I typed... your video was exactly what I required.. Thanks
Very happy you found the video useful. Thank you for your kind comment 🙂
Thank you very much sir!
Thank you for your kind comment. I'm very happy the video was helpful
I’m commenting just for the reply. Love the vid!
I'm very happy you liked the video. Thank you for your kind words
you are the best broo thank you
Thank you for your kind comment. I'm really happy the video was helpful
Great tutorial - learned something - wonderful sharing
Thanks a lot man.
No problem. Thank you for your kind comment. Remember to subscribe for more great computer tips and hacks
Thank you! Big help!
You're welcome. I'm very happy you found the video useful 🙂
Thanks a lot 😊🙏🙏🙏
You're welcome. I'm very happy the video was helpful and thank you for your positive comment
How can we do this on a larger scale ? For example if I want to select a whole column to do this , or can it only be done individually cell by cell ?
Hi, great question, this formula only works for individual cells rather than a range but if you copy down the formula completing the data copy for a column should work as long as you remove the absolute cell references in the column you copy to. Hope this helps and good luck 🙂
Thank You!
Thank you for your comment. I am very happy you found the video helpful and thank you for your kind words
Thanks! That saved my job 😊
Very happy you found the video useful. Thank you for your kind words
Awesome help bro! Thank you so much for sharing
Happy you found the video useful. Thank you for your comment
Thanks for this beautiful tuto
Thank you so much!
Tq so much bro👍👍👍
Thank you, I'm very happy the video was helpful and thank you for your kind comment
Thanks man 👍👍👍
Your welcome. I'm very happy you found the video helpful. Thank you for your kind comment
Thanks for this tutorial. Is there a way to automatically copy data from one workbook to another workbook?
Hi, yes there is. If for example two workbooks are in the same folder, open both workbooks and in workbook 2 type = in the cell you want to copy to, then in workbook 1 select the cell that you wish to copy. Then return to workbook 2 and press enter or return to complete. This is demonstrated in the video from 1.55 if you wish to see this demonstrated. Good luck!
Do you know a way to copy the font data, like colour, bold underline etc?
Hi, the method used in this video won't copy the cell formatting but you could use Format Painter in the home tab or you could format any cells with one click of a button which I demonstrate in this video: ua-cam.com/video/14mZWE0V-Es/v-deo.html
This is exactly what I was looking for. However if the cell is empty why does it generate “0” in the new cell. How can I get it to not show this?
Hi, to achieve this, lets assume we are copying cell B2 into cell C2. The method is to use the formula =B2 & "". This will not display a zero in place of an empty cell and copy the content when it is populated. Hope this helps. Thank you for watching and good luck
@@usemybox I don’t get it, not working for me . Can you make a video showing it?
@@Xantank Hi, try and use the formula =IF(B2="","",B2). Replace B2 with the cell reference that your copying. I could release a video for demonstration but wouldn't be published until Tuesday. Good luck
@@usemybox hey any update? Is the video up?
@@Xantank Hi, yes it is. ua-cam.com/video/zXOkk13FIm0/v-deo.html
Good luck! 🙂
How do I copy a formula across multiple cell while maintaining the 'Column' reference by having the 'Row' reference change? I tried using the $ sign in from of ONLY the Column letter (not the Row #), but it still locks BOTH the column and the Row.
Hi, sounds like you want to copy a column as you want the column reference to be absolute. Try this solution, it will copy a column range. Good luck! ua-cam.com/video/9kNNo8eBiE8/v-deo.html
Very simple but very effective method
Thank you, we're very happy you found the video helpful
Very useful, thank you. But those loud Mouse clicks are distracting.
Thank you. In later videos I have disabled system sounds so you won't hear them if you decide to watch more videos. Merry Christmas 🎄 😀
Hi I hope u r doing fine, I came across your video today I know its old, but I really enjoyed very much and learnt a lot from it, I just want to make a friendly note here that you might consider, is the voice, I hardly hear your voice, which is essential for these instructive videos, so maybe you can do something about it ? thank you so much and keep up the good work,
Hi Hend Samir, thank you for letting me know. I may record the video again to improve the audio in the future. Thanks for your support
@@usemybox Thank u for being consedret.. I am sure I will learn from a lot from you.. Thks and god bless.
How to do it with the whole worksheet? I created a printable copy of my main worksheet and want it to be automatically updated whenever I make changes to my main worksheet so I won't have to manually update it every time. Is it possible?
Using this method you could drag the formula to apply to your other cells or to copy a range, this video will help: ua-cam.com/video/9kNNo8eBiE8/v-deo.html&feature=share7
Useful video
Thank you. I'm very happy you found the video useful and thanks for your positive comment. There are more useful computer tips on the channel 🙂
My workbook is getting very bulky,
what if i use this connection in second workbook to get the data as pivot table/chart in second one, will that help?
I won't mind if my second one slows down but it shouldn't be the case with my original/first workbook
ua-cam.com/video/9kNNo8eBiE8/v-deo.html This video uses the choose function to copy a range of cells. If your current workbook is beginning to slow try this alternative solution. Good luck
Hi There, I am trying to copy data from one work book to a fresh work book wiht a different order to the columns. Can I do this for the data of a whole column?
@@susanschibli5184 Try the solution in this video, instead select column's instead of cells: ua-cam.com/video/6KMkCkzVk8A/v-deo.htmlsi=kdAkMyHb2_naUqou
@@susanschibli5184 Subscribe for more great computer tips and hacks!
Great tutorial. What if I want the color format to also go to the cell on the other sheet as well as the data?
Hi, that is a good question. Using the formula in the video, copying formatting is outside of that formulas parameters. There is a way to do it I believe using visual basic but that can slow down your workbook and I excluded visual basic from this video to keep it simple. However if you are copying to the same cell (B2 to B2 as an example) in another worksheet or workbook you can select the entire worksheet, click format painter, select the worksheet copying to, select all and format painter should apply the same formatting. Good luck!
@@usemybox thanks for the quick reply. I am building a tracking sheet for my crypto. I buy, sell and store on several exchanges and wallets. So I made different sheets for each and a master page that shows all of those accounts in one place. Would have been great to just have them update to that master page. But, I need color coding so I can tell at a glance which place those buys, sells and storage numbers are coming from. It's very dynamic as a day trader. Oh well. Thanks for the help though!
@@dan.d Yes I see why that's important for you. Good luck with your crypto trading and thank you for your comments
@@usemybox Thank you!
Hi,
Great video and Thanks a lot. But I have a question is there a way to automatically copy data from a raw sheet to distinct individual sheets?
For example: I have data with names and their age and I have 10 individual sheets for those names and I want to automatically send data to those page when I fill in information in the raw page with the concerned names?
Would appreciate any help!
Thanks and Regards,
Debaroon DasGupta
Hi, thank you for your comment. Copy the formula to each cell you require for the number of pages. For example copy the formula for the cell you wish to copy to sheet 1, then sheet 2 etc. If you wish to automate this process use VBA. Hope this helps. Thank you
Use the below tutorial... It will suitable for your requirements..
Do you happen to know, how could I use this formula that every new entry in Sheet 1 would be coppied in Sheet 2 in new row, so I could make a new list of all entries in sheet 2. Thanks
Hi, one method would be to use the formula to copy blank cells that you know will be populated in the future, but using this formula to copy blank cells will display a 0, so to stop this from happening, let's say for example you wanted to copy cell B2 to cell C2, in cell C2 you would use the formula =B2 & "" this will stop zero from being displayed and copy the contents when cell B2 is populated. Good luck!
@@usemybox Thank you for a quick answer.
@@smeks You're very welcome. Thank you for your kind comment
I've used this for a spreadsheet and it's worked on 6 pages, but when I'm trying on a 7th sheet all it says in the sell is =(HELP!D3) instead of copying the data across from the HELP tab.
Do you have any idea why it's only doing it on this page and not all the rest?
@@PaulyFPL first I would check the formula. I would also delete the worksheet your having the issue with and retry. Try another worksheet are you having the same issue?
Lovely...thanks...from INDIA
Thank you Anita. Very happy you liked the video
How to do that in the same sheet but on the different columns and rows?
Hi, if you want to copy to a different cell you can choose any cell to copy to on the same worksheet, the formula will work on same or another worksheet.
Thanks buddy I was searching it and you gave me thanks a lot😍😍😍
Thank you. Very happy the video was useful for you
How to copy the data including its format (like bold italic) Thanks!
Hi, this is a good question, but this formula only copies the raw data. You could use format painter to copy the cell format. Thank you for your comment and good luck
I want to COPY a ROW of data to another sheet ONLY IF the data in one cell EQUALS something.
How?
@@memowilliam9889 Use this and the copy formula: ua-cam.com/video/2FHp0IRUHAQ/v-deo.htmlsi=3k_-FKXA9Xq1vXnR
@@usemybox
I don't think that's exactly what I am looking for.
I Image the following
=IF(A1 = "x", CHOOSEROWS(1:50,3))
But I have no idea if that is possible or even good syntax.
How to copy from single cell to merged cell in different sheets ?
Hi, good question. If for example you wanted to copy cell b5 from sheet 1 to merged cell a2 in the second worksheet, in cell a2 use the formula =(Sheet1!B5). I have tested this and it works. Hope this helps. Good luck!
How do you get the cell to remember the entry? Is that possible? So that a cell will remember every entry, despite deleting an entry and entering a new one in the first cell.
Hi, good question. Using visual basic you can get Excel to remember or store a previous value (sometimes utilising a third cell) though I haven't tried it to prove it works. This solution is beyond the context of this video and too complex for a reply message but I may make a video with that solution. Thanks for your question and good luck
Thankyou so much
We're very happy the video was helpful. Thanks for your comment
How would you have it set up to still copy over, but have it blank if you have not added anything into that cell yet. For ex have lines 1 to 5 filled, but lines 6 to 10 does not at the moment but can change. If its set up this way it has a 0 instead of being blank
Hi, thanks for your question. There are two methods to achieve this that return the same result. Lets assume we are copying cell B2 into cell C2. The first method is to use the formula =B2 & "". The second method is to use the formula =IF(B2="","",B2). Either of these will not display a zero in place of an empty cell and copy the content when it is populated. Hope this helps.
@@usemybox does this apply if its going onto a second sheet within the same workbook? Im trying to have a several sheets to help filter info on a full sheet of specific items
@@musicguybguitar1 Hi, yes it does. Later in the video I demonstrate the formula on another worksheet and workbook. Thank you
@@usemybox i get that. What im needing is the correct formula that goes from one sheet to another that still copys data but does not leave a '0' when there is no info. Every formula ive tried leaves a '0' when i need it blank until i add something into that cell. But i do appreciate the feedback and help. :)
@@musicguybguitar1 Hi, ok I understand. Clicking the cell in a second worksheet completes the reference, I will try this myself later
any tutorial for whole page ?
ua-cam.com/video/9kNNo8eBiE8/v-deo.html Hi, this video let's you copy a row or column range. To copy an entire worksheet would probably require visual basic which is another video I will make in the future. Good luck!
What if you have renamed the sheet? Say to July 21. How would I type that in? I tried typing that in as you did and it didn’t work?
Hi, great question. If for example sheet 2 was renamed July 21 and you wanted to copy cell B2 use this formula ='July 21'!B2 though once you have selected cell B2 in sheet July 21 do not click anywhere else before pressing enter or return on your keyboard. I have just tested this and it worked. Hope this helps and good luck!
@@usemybox thanks for the quick reply that worked! Thank you
@@mhaskins3769 I'm very happy this worked for you and thank you
Why does time not transfer or copy correctly? example: 12:45 on Sheet1 = 0.531 on Sheet2
That is an excellent question. Let me look into it and I will see if I can find a solution for you 🙂
@@usemybox Thank you!
@@christopherdeardorff6478 Hi, I set a number of cells to 'Time' format. For example the time format cell was B3, in cell C3 i used the formula =(B3) and it worked. Hope this helps
Thanks for this! Is there a way to get this formula to copy down consecutive rows in the destination workbook? I want to be able to enter the text once in one workbook and have the formula populate the other one for me.
Hi, thanks for your question. This formula is kept simple so users of all levels can apply it. There is probably a method to copy the formula across multiple rows but that would be beyond the context of the video. I may make another VBA video on how to do that in the future. Thanks for watching 🙂
@@usemybox would really want to know how this can be done... i have searched your videos but couldn't find this drag down other excel sheet video...kindly do and post please...
@@rkirankumarreddy9004 Hi, look at my recent video how to copy a formula to an entire column in Excel. This solution should work for rows too and is VBA free. Good luck! 😀
@@usemybox Not that one...i know how to do within same sheet or same excel workbook....i want to do that with two different excel file(workbooks)....like in excel workbook 1( sheet 1 row 1 col 2) i pinpoint to excel workbook 2 (sheet 2 row 1 and col 2)...i get the real time value over there in desired cell....but if i do the drag down it just copies the same formula rather than like the video you mentioned( not shifting the rows or cols but just copying the formulas)...
@@rkirankumarreddy9004 Hi, did you remove the absolute references from the formula in the second workbook?
Be nice if you were copying and pasting multiple cells instead of using just one cell for your demonstration. That is maybe 4 cells across and 4 cells down for instance? Also what about copying these cells to an Excel spread sheet with a brand new title? How do I open Excel with 2 different titles where both sheets show?
Hi, to implement a copy of a range of cells, Excel VBA would be required, which is a subject for another video which i will be uploading soon. Thank you
How to do for multiple columns to copy ?
Hi, you could use the formula in this video and copy each cell in your column or to copy a range of cells please view this video where I demonstrate how to copy a range of cells in a column ua-cam.com/video/9kNNo8eBiE8/v-deo.html
@@usemybox Thank you , that was very simple to follow.
@@07venky Thank you for your kind comment
Hi I have tried moving cell containing B.yuei which gives error that it contains unrecognized data can i move the "." too?
Hi, the formula for copying data will copy the information within the cell. The "." was too stop empty cells being populated with a zero. Hope this helps
how about I enter a letter in sheet 1 then I want to pop up background color on column in sheet 2..please help
me on this matter..thank you
Hi Aizen, try using conditional formatting and format the cell background. Thank you
How to create different sheet different names from one sheet
Hi, thank you for your question. The video demonstrates copying data from the same or different worksheets from one workbook. The method demonstrated unfortunately doesn't copy successfully from another workbook. Hope this helps. Thank you for watching
What if i need to copy data in same sheet ?
If you're copying data from the same sheet, then you don't need the sheet name at the beginning of the formula you can just use the cell reference. For example, if you wanted to copy the data in cell B3 on the same sheet, type =B3 that will copy cell B3 and update if the data in B3 changes. Good luck
@@usemybox thank u so much its worked, thank u for your quick reply
thx q
Thank you. I'm very happy you found the video helpful.
I wanted to migrate data from one excel file to another. There is data in 1st file but no data in the 2nd file other than column names. The column names are not the same as well. For example the 1st file has 20 columns out of which the 5th to 10th column names are First Name, Last Name, Address, City, State and Pin Code where as the 2nd file has only 5 columns namely Last Name, First Name, Address, City, State and Postal Code (in the same order as mentioned)
How do I do this?
In the video, it demonstrates how to copy data from one workbook to another. This formula will only work cell by cell rather than a range of cells and if you're copying column data to column data then autofill should complete the formula for you. Hope this helps. Good luck!
Dammi xa
Thank you
Very happy you liked the video. Thank you for commenting
How to do the same thing in same excel sheet
Hi, use the same formula, the cell reference will work on same excel sheet. Thank you
Can this be done to workbooks in excel online business if both files are opened in separate tabs?
Hi, thanks for your question. Yes, using this method for Excel online should work the same as using Excel on your local PC, though unfortunately I don't have access to Excel online for testing. Please let me know after you have tested the formula. Good luck
@@usemybox no, it doesn't work. I even tried making it on my computer then when I tried opening them online, there was an alert in my sheets saying: (roughly translated from French) "Links to an external sheet are not supported and were deactivated"
@@kentoutcourt Hi, thanks for replying. That is very interesting. Have you tried Google Sheets or are you restricted to Excel online?
Hi Plz help how to do it if workbook on same sheet ?
Hi, if copying a cell on the same sheet, for example, if you wished to copy cell B2, in the cell you would like to copy the data to, simply type =B2 Hope this helps and thank you for your comment
Hi after copy to one workbook to another workbook what key press?
Hi thanks for your question. The path must not change when copying cell data to another workbook and should update automatically. Hope this helps
Any idea how to update whole row?
Hi, the formula only applies to a single cell. You could repeat the formula for the length of the row, again cell by cell. Using VB you could copy a row to a row, I may make another video to demonstrate this. Thank you
@@usemybox thanks for reply. appreciated.
How to enter data to every new worksheet we insert without copy paste (automatically)
Hi my question is .my excel sheet open in two different tabs and I want to copy different amounts in different columns in one time to paste in another tab excel.is it possible to automatically opt all the amount in different columns in one time to paste in another tab excel sheet.is there any formula please help me.....
Hi, I believe the question you're asking is you wish to copy information from two Excel sheets into a third sheet? The formula in the video unfortunately does not copy across multiple cells, but using the formula for each cell will work. Copy each cell from your first two Excel worksheets into the third worksheet. Thanks for your question
You can use the clipboard method for this requirement..
how to create unhide macro button for this hide macro
Hi, do you need to know how to create a macro to remove hidden columns and rows?
Thanks man
Thank you. Very happy you found the video useful
Thanks, but I want ask If there is a other method
ua-cam.com/video/9kNNo8eBiE8/v-deo.html Hi, try this video, it is an alternative method. Thanks for your comment
@@usemybox Thanks for the quick answer, but my work is in two different documents, as first method 🙏🙏
@@ahmadosily6249 Hi, in the video I demonstrate copying between two workbooks. Did that work for you?
@@usemybox in first method yes not in second method
@@ahmadosily6249 Ok, so using the method from the first video you were able to copy between two Excel workbooks. What you need is a different method to do the same?
plz check your video at 2:44 how did you get that long string address of the cell. i am not getting it. when i click on cell (say e.g. if it contains 13 it shows 13 not that long string address. what am i doing wrong?
Hi, thanks for your great question. If I'm looking at the right part of the video, the string is when I click the cell in workbook 1 after typing = in a cell in workbook 2. If it still doesn't work, please reply and I will try and help. Thank you
@@usemybox OK I'll try today and give u the feedback.
And hey, thank u so much for the reply & help
It is not working for me also (copying data from one workbook to another workbook). I typed = on the workbook where I want the information to be copied. Then I went to the other workbook where I want to copy the data from. The string formula did not appear on the workbook where the data should be copied into. I am not sure what step I missed or what I did incorrectly.
i am copying through arrayformula to new sheet but sorting the source data changes destination lineup. How can i stop this from happening?
Thanks for your comment. The method used in the video copies the data of a single cell, using this method for multiple cells would be the method for copying a range of cells. I used this method to copy a range and sorted the data and the destination cells were copied as expected. Hope this helps
Sir all cell not working how to solve plese send the video
This video maybe the solution you are looking for: ua-cam.com/video/iXTpPO_-7bE/v-deo.htmlsi=wQnVadonLaqKKzqW
whenever i apply this method so it apply to all column why ???
Hi, thanks for your question. When I use the formula it only applies to the cells the formula is entered. Are you perhaps copying the formula to the entire column?
If we close the book the refrence get error not works
Hi, sorry to hear you are experiencing issues with your workbook. Can you provide more detail explaining what you are trying to do? Thanks
You can link the cell instead :)
Thanks for your comment
How do you link the cell?
how to do it fast in 50k row?
Hi, there is a very quick method of applying a formula to an entire column. The best way to demonstrate it is to link to the video ua-cam.com/video/tOLkAlenD4c/v-deo.html
Hope this helps and today's video may interest you 'how to copy a range of data in Excel' which is scheduled for 1pm. Thanks
How to copy whol column from one sheet to another
Hi, good question. I made a video on how to do this recently. Please click the link and it will take you straight to the video. It may not work between two worksheets but does allow a entire column or row to be copied. If you want to copy to another sheet, use the formula in this video and copy the formula down using autofill. Thank you ua-cam.com/video/9kNNo8eBiE8/v-deo.html
If you give formula
@@muhammedfuadkaithodu9906 The formula is shown in the video
how do I stop the 0 (Zero) from appearing in a cell that is empty?
Hi, there are two methods to achieve this that return the same result. Lets assume we are copying cell B2 into cell C2. The first method is to use the formula =B2 & "". The second method is to use the formula =IF(B2="","",B2). Either of these will not display a zero in place of an empty cell and copy the content when it is populated. Hope this helps. Thank you for watching
@@usemybox Good Morning. Could you clarify your response a little more in regards to the formula. Tried copying but dont get any result. maybe I did not understand the formula. This 0 is only showing up with only one specific cell copied. Is there a reason behind this? Thanks so much.
@@luizaga Hi, a 0 usually corresponds to an empty cell being copied by the formula. To resolve this, you can use this formula when copying empty cells. Lets assume we are copying cell B2 into cell C2. The method is to use the formula =B2 & "" in cell C2. Hope this helps. Regards
@@usemybox Thanks for your response. My B2 cell (in another worksheet) has content in it (name of a customer). Copied this B2 into many other sheets (15 sheets) without problem using your =B2. Now copying B2 same way like prior ones and only get a 0 result. Any suggestions? Maybe the format of the target cell or something in settings with this last sheet changed. Opened new sheets and getting same problem when copying only this B2 cell. Use formulta for other cells and still works great. I am really lost. Thanks so much.
Hi, just figured out to some degree. Seems I am getting a zero only with this cell because it is coming from merged cells. Copied same amount of text from a single cell and works fine. Found away around it. Thanks for your time!!
😀😀😀😀😀😀😀😀😀😀
Thank you 🙂
No, its not copying, its just making the cell = another cell.
Thank you for your comment.
not working on mobile app😢
Hi, that's interesting, thanks for letting me know
The first step =(sheet1!B3) doesn't work on my excel.
Hi, this is the formula you used on sheet 2?
@@usemybox that's what I did.
@@fmj8216 is sheet 1 renamed to something else?
@@usemybox yes it is.
Sorry, sheet 2 is renamed to something else.
Too many ads
Thank you. I will review the ad settings for the video.
Hi can i email you i have a question
Hi, I don't usually give out contact information but if you have a question regarding the video I am happy to try and help you? 🙂 Thank you
@@usemybox when i put =then the formula on sheet 2 to connect the data from sheet 1 it just shows the fomula not the data from sheet 1
@@graceantonio9389 Ok, so the formula used in the video to copy to sheet 2 is =(Sheet1!B3) this should copy the data from sheet 1 cell B3 to sheet 2. Please double check the formula and see if your data copied as you expect 🙂
@@usemybox the cell just shows the formula not the data I input in sheet 1. The formula is right tho. Gonna check it tomorrow at work again. Btw you're cool bro I did not expect your fast response. Nice channel you got my subs. Thank you
@@graceantonio9389 Thank you 🙂 If there are still issues when you try again let me know and we can see if we can find a solution. Best of luck!
It doesn’t work
Hi, we're very sorry this method didn't work for you. What formula did you use?
I bet you won't click this: bit.ly/3bEmrWI
Spoiler, It's a subscribe button
@@sourceofmary1326 but it's so much fun to click 😀
🙄🙄🙄
Hi, thank you for your comment
You are not telling right information
Hi, if you please explain the issue maybe we can help.Thank you
Waste of time spending here
Hi, did the solution in the video not work for you?
=(SHEET1!B3)
Thank you