Excel - Combine Data from Multiple Worksheets (Tabs) into One Master Tab Tutorial
Learn how to Combine Data From Multiple Sheets (Tabs) in Microsoft Excel using Power Query, auto expandable Table Objects and make an automatic master sheet with all the data combined into one tab.
Updated Versions:
Use Table Names with Dynamic Updates • Excel Tables - Combine...
Include Worksheet Names • Excel - Combine Data f...
Install add-in for Excel 2010/2013 users www.microsoft.com/en-us/downl...
Пікірлер: 196
It took me watching over 40 other videos to find this one. 10/10! Thank you for your knowledge and ability to speak clearly in a non-technical way to create this video.
This is a great explanation. I know Excel but no prior experience with Power Query. This was very helpful. Thanks for being very thorough, even going back to show how to correct the date format properly. Thank you.
You do a great job of explaining. Cheers!
Your method is incredible and I can easily follow your instruction without struggling. You are awesome! Big thank you to you!
Thank you very much for sharing this with us! You made my day. Keep up the good job!
This was extremely lucid and very useful. Thanks.
Super clear tutorial! So Useful. Thank you so much!!!
Fantastic. was looking a lot for these steps. saved me a lot of time. Thanks.
Awesome video for someone who needs this for work. Thank you.
I've spend hours looking for this solution, thank you thank you thank you!!
Thank you so much! it's really helpful.
Good lesson, Thank you. I did it.
Just found this channel!! Wow great presentation and skilled in teaching the complex steps!! Thank you!! Subscribing!!
So helpful and easy to follow!! thank you
Great tutorial! Really appreciated it!!!
It really helped me !!! Thanks for posting this..👏👏
this is first time i really understand this its really good explained many thanks!
Thank you so much!!! Save me a lot of time.
Thank you so much for this, Sir!
Thanks. That is my daily work.
It really helped me!!! Thanks for posting this video
nice! easy to understand. thank you.
Thank you for sharing this knowledge. Maybe I missed it, but can one combine files,, different filenames, only one tab per worksheet?
Wow! Amaaaazing! Thank you so so much!
Excellent coaching and guidance...thx a lot
very well laid out...thanks
Sooo grateful, thank you very much
Good explanation!!! Thank you, sir!
Thank you, Thank you, Thank you!!!
thank you for this. i've been looking everywhere on how to do this. i'm not an Excel expert, specially with queries. you explaination makes it very eary to understand.
Hi, Thanks so much for this Video. i am working on 3 Auditing Questionaires in the Automotive Sector (VDA, QT13 and BIQS). I would like to consolidate all these questionaires in just one Taster Sheet. But the rows, Columns and 90% of the questions are totally diffrerent. How can i use the power Query to make one Master sheet?
Really nice video. Thank you very much..!
thank you very much for sharing this tutorial, keep up bro and good job
Good lesson!
Great Video, Thanks!
Awesome tutorial, another coworker came to me with something they were inquired to do... super awsome instructions, since I use Power Query in Power BI this was super easy.. thank you for the greate video and great direction.
@ExcelGoogleSheets
2 жыл бұрын
Cool!
now trying to use only google sheets. thanks for your another video on google sheets on this issue. it was great. google sheets is the future.
IT works. Thank youuuu very much.
Nice tutorial. very clear, easy to understand even for beginners. Thank you and more power!
@ExcelGoogleSheets
4 жыл бұрын
Glad you enjoyed it!
@rhandymaco803
4 жыл бұрын
@@ExcelGoogleSheets do you have a gannt chart in excel?
@ExcelGoogleSheets
4 жыл бұрын
No video on gannt charts currently.
Too good and perfect!!!!!
you are so awesome... please make more..... i love you
Great but I've a question if i want to get data from multiple workbooks then what will be code language instead current.sheet
Perfect Tutorial.
This is really helpful. Is there a way that the Tab name also comes along with data in the master file so I can locate which data points I am getting from which of the tabs.
Very helpful!
Xellent tutorial. Very well xplained. Thk u. Is there any other option other than Append.....in case you have like 20 sheets to combine.
Great video. But is there a way that you can remove duplicates?because for me, just want to update my monthly data given that there is a new row/s added.
thank you very much
Nice job, Again!
Just what i need! Thank you
@ExcelGoogleSheets
2 жыл бұрын
:)
Thank You Sir, Being a Pakistani My English communication is very poor, I would like to think of you on the release of this unique video. I was finding it since long and now I success to find it. Sir, Please also release different videos of connection of multiple work sheets into one single having same heading and convert that into pivot table.
@mayascript4229
4 жыл бұрын
Plz tell if you got the solution, Thanks I am also facing same issue
Fantastic video. This worked great at the start and it worked as shown for a while, but now I cant get it to do it anymore and I keep getting errors.
Hello and thank you so much for nice turorial. I have a question please, how can I link several worksheets to a auto updated master worksheet.
What a great video! I just got it to work! Any solutions for a *null* showing up in data filled cells? (I’ve formatted the cells in the sheets and master but it’s still empty in the master table)
@yasserbaiomy
3 жыл бұрын
You can filter them from the Query
thnk u soo much...it helps me lot
is there a way to input data from multiple sheets in a master list where you only have to type the name of the other sheets? cause that's what my boss want. and i really need help with this. :(
is this advanced Editor is the same at macro in excel
Nice video. I am getting a token literal expected at the in part. Can anyone help
someone can you give me, simply excel workbook,, "The query table couldn't be refreshed: Excel was unable to load a necessary component. This component might have been damaged or deleted. Reinstall the client software or data source driver software for your database" write this, Can you help me
(Excel)lent video, very helpful. just a quick question if I could, if the Part1, Part2 etc have blank rows in to allow for growth, is there a way of removing the blank rows, so that it only shows the actual data in each sheet? Thank you.
I Thank You 3000
Thank you sir
Will this work, if all the sheets are full? Then alphabetize as one? Then save as a one-sheet CSV?
I have a google sheets problem that needs solving! I used the query function to pull in data from multiple tabs into one tab, even when new rows are added, using the function in this video. Now that I have my data set, which updates every day automatically with a script, I need to auto-delimit one of the columns as well as concatenate a few columns. However, since my data set keeps increasing in rows daily, I need to find a formula that will scan the all the data and only apply the split/concatenate formulas to cells that have data. Right now i just have the split formula (=(SPLIT(D2,"_"))) and then concatenate formula as is, but i would have to carry down the formula once a day since the data set keeps increasing. Is there a way for the split/concatenate formulas to scan the whole data set and then only apply the formulas to rows with data so that I avoid "@!" in rows that don't have data? hope that made sense!
Thanq u sir
Best video I have come across explaining these steps. I'm running into a problem that when i delete a row from the individual sheets and refresh the query, it does not delete it from the master sheet I created. Also, once I create a new row and refresh the query, it will update, but if i go change any test in the same row whe refreshed, it doesn't update the text in the master sheet. Any idea what i might be doing wrong? Any help is appreciated.
Thank you very much. Is there any different effect if we just traditionally "copy and paste" each different sheet into one sheet master ?
@fabrizziorutigliano1924
3 жыл бұрын
Try to copy and paste 80 sheets... You'll see the difference. Not just time, also the possibility of committing errors.
hello, could you please advise, how to manage the columns all at once for master and child tables? How to add a new column, remove existing, re-name existing, but do this, so it applies in all tabs the same time and doesn't break the rule, and also transfer the data from these new columns to master tab
I have watched several of your videos, and they have been very helpful. I have built a checkbook register with multiple tabs for separate accounts. I have been trying to find a way to create a formula to add and total the “last non-blank” cell in the “Balance” column of each tab and have it displayed in the Dashboard that I have also created. Would you please try to answer this for me. Thank you!
@ExcelGoogleSheets
4 жыл бұрын
kzread.info/dash/bejne/mY1ryLiGd7OddM4.html
Will it be possible to combine multi sheet with different header... Like in sheet 1 headers are name , place, animal.... In sheet 2 it is name , place, animal, marks... In sheet3 itt is name, place, animal,thing, marks?
Hello! Is there a way to access data in Excel file from Google Sheets? I can write in a Google Sheet spreadsheet from an Excel spreadsheet?
Can you please illustrate if I only want to combine few columns from many sheets how can it be done. Thanks
@ExcelGoogleSheets
6 жыл бұрын
Just select & right click on the columns you want in your Power Query editor and click remove all the other columns.
Hi sir, your example by using the same arrangement columns for different sheets. What about the sheets have different columns arrangement ? Is power query can auto arranged the data into the same column title ?
@ExcelGoogleSheets
6 жыл бұрын
It will arrange all the columns with the same name together. So if columns are not in the same order it will be fine, but it won't work when columns don't have the same name, at least not with this method. You can rename those columns in power query and make it work though.
@fadhilahismail3144
6 жыл бұрын
thanks for your fast reply. I'm trying it right now.
hello, thankyou for this video. this was worked for me, but now, if I try changing data in the master sheet, these changes do not reflect in the original table. how can I set this up so that the entries are synchronized and I can make changes on either sheet
@ExcelGoogleSheets
5 жыл бұрын
There is no easy way. It will be very complicated.
Thanks sp much, excelent tutorial. I have the opposite problem: I want to separate one master table into multiple worksheets, for example one for eache salesperson, and I havent found how to do so. Any suggestions?
@ExcelGoogleSheets
3 жыл бұрын
Check if you have FILTER function available in your Excel version.
Great Video and easy to follow! Question - how can I make it so that when an entry is removed from each tab, the master will also update and removes the entry. Currently it;s leaving it out as "null"
How to convert multiple sheets into the table at once, instead of opening each sheet tab? is it possible to do?
Hi, thanks for upload, but please tell me it is not faster than manually copy and paste tables, so what makes this method special?
@ExcelGoogleSheets
5 жыл бұрын
This is not meant to make it faster, the point is that it will automatically update information as you add more lines to your other tabs.
I want to ask better work in spredsheet or excel ? Let say for query, maintain and monitoring from accounting and project management.
Do you have a video for this? I do all my estimates on an Excel spreadsheet. The cells that the address goes in is the same for all my saved estimates. I would like to collect the addresses from all my estimates and place it in one file for mailing. Can you help with this?
@ExcelGoogleSheets
4 жыл бұрын
This may help you kzread.info/dash/bejne/iKmcmc6hYtDOgc4.html
Can this be done backwards ie one master sheet and separate data on to different sheets in one work book?
the Pivot table I am creating will be in another file linked to the data source (master combined data). is there any way that it can be "auto refreshed". seems pivot yes.... but now the combined data needs refresh too.
@ExcelGoogleSheets
6 жыл бұрын
Under Data > Connections > Properties you can set to refresh when you open the file or every X minutes. I don't think it's going to work when the file is closed however.
@ExcelGoogleSheets
6 жыл бұрын
You should try and see though.
can we add additional column to show what table they came from ?
great video, worked fine and smoothly, however if their are over 20 sheets, and they aren't formatted as a table, does this mean we have to make them all as a table one by one ? thanks :)
@ExcelGoogleSheets
6 жыл бұрын
You can simply open a new file and in Power Query do from Excel file option and choose the tabs you want. That way you won't have to convert them to tables, PW will do it for you.
@hamzagt5
6 жыл бұрын
cheers you are the best :D
Hello, Learn Google Spreadsheets. Thank you very much for this video. Can the tables be in a different excel file, instead of being in a Tab in the same file?
@ExcelGoogleSheets
6 жыл бұрын
Yes.
@mohsinnisar5784
6 жыл бұрын
hi Sir can you please guide how can we do that
Dear sir, I don't have power query options. So, could you help to me another options or way.
power query adds column when refreshed
What if I want the far left column to remain the same n just combine the other columns with it?
Thanks. easy to follow. Can you make it automatically combine to master sheet we add on the any other sheet 1 sheet2 sheet 3. any VBA code to make this works perfectly.
@ExcelGoogleSheets
2 жыл бұрын
I believe this should work for you kzread.info/dash/bejne/fJll1a2Borndpbg.html
I have a tracker with formulas like Xlookup or if or vlookup which is linked to my customer master data file and If I made it as table and next time if I add some customer to my tracker will table replicate formulas also and give me data from my master data file?
@ExcelGoogleSheets
3 жыл бұрын
Yes, if everything is done correctly.
I need to append data from worksheets in various workbook files. Is there a way to use the Power Query in this way?
@ExcelGoogleSheets
5 жыл бұрын
Yes. kzread.info/dash/bejne/n4au0M-fkduxdNI.html kzread.info/dash/bejne/dKGs1NV9nte4ZJc.html
Ctrl+T is the keyboard shortcut for creating a table.
@patricksokol9428
3 жыл бұрын
9899
how can i copy this code.
Could use consolidate
Will this work in Excel 2007?
You don't need to highlight all the cells to convert them to a table, just click in one cell, choose a table, done. ;-)
HOW TO DO THIS BY JUST CLICKING A SINGLE CELL LOTS OF DATA WILL APPEAR.. PLEASE HELP
I set this up a few weeks ago based on your instructions and it works great (thank you!). However, this morning I set out to add another worksheet to feed into the master tab. I set up the worksheet, created the table, and went into the advanced editor and added "source9" then added it along with the "source1, source2, source3...." and so on until adding source9. When I selected "done" an error comes up "We couldn't find a table named..." the name I made the tab. Any help around this? For whatever reason it is not recognizing the new worksheet. Wondering if I set about editing the code wrong off the get go.Thank you!
@ExcelGoogleSheets
4 жыл бұрын
Did you select the new data (srouce9) and do from table/range to make a connection out of it first? It seems like you skipped this step.
@mpaul4ever10
4 жыл бұрын
@@ExcelGoogleSheets I am not sure if I understand. I started by adding the new tab then inputting information into the cells. Then made that information a table, then opened Power Query and went straight to the advanced editor. In the editor I named it source9 (under the lines source1-sourc8) and typed in Name="tabname" to link the info in the tab where I created the table. Did I miss something based on what I did?
@ExcelGoogleSheets
4 жыл бұрын
Making a table is not enough. You need to go to power query tool and make a connection from that table by using "From Table/Range" and then you can do your thing.
@mpaul4ever10
4 жыл бұрын
Learn Google Spreadsheets did it!! And it worked. Thanks!!!
@ExcelGoogleSheets
4 жыл бұрын
I'm sure it did :) Cheers!
sir i have installed powerquery but after that also i am not getting powerquery option in my excel sheet so can you please help me here?
@ExcelGoogleSheets
6 жыл бұрын
enable it by following the steps here support.office.com/en-us/article/troubleshooting-power-query-2546eaa8-9893-4574-9ef9-afacb9b0b495
@vijubhutra503
6 жыл бұрын
sir this is also not working, now what to do ?
Can the same be done in Google sheets too. I would love to see this one too
@ExcelGoogleSheets
3 жыл бұрын
kzread.info/dash/bejne/o2yV2bCMibHKoZc.html