Excel - Combine Data from Multiple Worksheets (Tabs) into One Master Tab Tutorial

[@matthewchristensen6565]

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.

[@user-wi6rq3nw7l]

Your method is incredible and I can easily follow your instruction without struggling. You are awesome! Big thank you to you!

[@sarahblack8129]

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.

[@nancmadi]

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]

Cool!

[@charlescaison4376]

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.

[@garciaj50]

I've spend hours looking for this solution, thank you thank you thank you!!

[@InsightfulJourney123]

Just found this channel!! Wow great presentation and skilled in teaching the complex steps!! Thank you!! Subscribing!!

[@stepanustungliah7302]

thank you very much for sharing this tutorial, keep up bro and good job

[@kingrehawk]

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.

[@emilytumon1163]

Just what i need! Thank you

[@ExcelGoogleSheets]

:)

[@DaveKidderOwningFresno]

very well laid out...thanks

[@bonzai0331]

Awesome video for someone who needs this for work. Thank you.

[@sachinfilinto]

Fantastic. was looking a lot for these steps. saved me a lot of time. Thanks.

[@gulrahman6225]

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]

Plz tell if you got the solution, Thanks I am also facing same issue

[@johnn.abletis8914]

Very helpful!

[@golamrabbany8011]

thnk u soo much...it helps me lot

[@brayansalazar1834]

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"

[@rhandymaco803]

Nice tutorial. very clear, easy to understand even for beginners. Thank you and more power!

[@ExcelGoogleSheets]

Glad you enjoyed it!

[@rhandymaco803]

@@ExcelGoogleSheets do you have a gannt chart in excel?

[@ExcelGoogleSheets]

No video on gannt charts currently.

[@valycoulibaly5259]

Excellent coaching and guidance...thx a lot

[@mohanakrishna3796]

Perfect Tutorial.

[@MrViper0318]

Great Video, Thanks!

[@elbiemostert3502]

So helpful and easy to follow!! thank you

[@MattGoodreau]

Good explanation!!! Thank you, sir!

[@andrewsnow4078]

(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.

[@Noochbaby]

Super clear tutorial! So Useful. Thank you so much!!!

[@kongveasna7860]

Thank you sir

[@gjmarsh8787]

You do a great job of explaining. Cheers!

[@jean-mariemendy5899]

Thank you very much for sharing this with us! You made my day. Keep up the good job!

[@anhjaden4298]

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]

I believe this should work for you kzbin.info/www/bejne/gJaUpH5-p7uqq7c

[@mohammadrezwan2997]

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.

[@erikah5651]

This was extremely lucid and very useful. Thanks.

[@datageekhub]

Great tutorial! Really appreciated it!!!

[@markarmon2883]

IT works. Thank youuuu very much.

[@rupeshpatel7356]

It really helped me!!! Thanks for posting this video

[@krishnaghorpade136]

Really nice video. Thank you very much..!

[@YoochunViolet]

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.

[@HarpreetSingh1991]

Thanks. That is my daily work.

[@sarahbertoli9757]

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.

[@davidtopp1972]

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]

kzbin.info/www/bejne/nYqal4mDfLVqes0

[@abhishekbhatnagar2737]

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.

[@anneedgington9339]

nice! easy to understand. thank you.

[@kimmallari2658]

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.

[@pringnareth6439]

Good lesson, Thank you. I did it.

[@rupeshpatel7356]

It really helped me !!! Thanks for posting this..👏👏

[@shaam_bunchofnumbers]

Why is this in the fucked up mii music playlist

[@PhillyDaGoat]

Lol i was boutta comment

[@hojasderuta]

Sooo grateful, thank you very much

[@linglinglow5961]

Thank you so much! it's really helpful.

[@ivanmartinovic5371]

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

[@AngelofVerdant]

I Thank You 3000

[@yueyang2738]

Thank you so much!!! Save me a lot of time.

[@melaniebarcelona4631]

Wow! Amaaaazing! Thank you so so much!

[@melaniemarkwell8689]

Good lesson!

[@xuxie8155]

I want to ask better work in spredsheet or excel ? Let say for query, maintain and monitoring from accounting and project management.

[@argonaut119]

Nice job, Again!

[@Valandor_Celestial_Warlock]

Ctrl+T is the keyboard shortcut for creating a table.

[@patricksokol9428]

9899

[@jenilynestorba575]

Thank you so much for this, Sir!

[@nemishsarvaiya7375]

Too good and perfect!!!!!

[@garylillich2311]

Thank you for sharing this knowledge. Maybe I missed it, but can one combine files,, different filenames, only one tab per worksheet?

[@saskeajacobs5710]

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]

You can filter them from the Query

[@marjunalinio8516]

can we add additional column to show what table they came from ?

[@a.t.312]

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?

[@jamesperry3837]

Xellent tutorial. Very well xplained. Thk u. Is there any other option other than Append.....in case you have like 20 sheets to combine.

[@fancho8]

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]

Check if you have FILTER function available in your Excel version.

[@chriss2595]

You don't need to highlight all the cells to convert them to a table, just click in one cell, choose a table, done. ;-)

[@moheuddin_sehab]

you are so awesome... please make more..... i love you

[@hamzagt5]

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]

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]

cheers you are the best :D

[@MingayCalo]

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. :(

[@shimshi2003]

Thank you, Thank you, Thank you!!!

[@GA247GA]

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!

[@mohsinnisar5784]

Great but I've a question if i want to get data from multiple workbooks then what will be code language instead current.sheet

[@VladimirCoutinho]

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?

[@MufaddalMaskawala]

Can the same be done in Google sheets too. I would love to see this one too

[@ExcelGoogleSheets]

kzbin.info/www/bejne/p2nEqIGJjrOXp5Y

[@jessicaanguiano3725]

Can this be done backwards ie one master sheet and separate data on to different sheets in one work book?

[@reskus]

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]

There is no easy way. It will be very complicated.

[@deepaksimba]

How to convert multiple sheets into the table at once, instead of opening each sheet tab? is it possible to do?

[@CustomWeddingGarter]

Will this work, if all the sheets are full? Then alphabetize as one? Then save as a one-sheet CSV?

[@ritikabaluja1053]

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?

[@ozgur937]

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]

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.

[@fadhilahismail3144]

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]

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]

thanks for your fast reply. I'm trying it right now.

[@alexkong93]

thank you very much

[@haroldblancog]

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]

Yes.

[@mohsinnisar5784]

hi Sir can you please guide how can we do that

[@tanmoybhaumik5619]

For example, If there are three duplicate rows in part1 and part2 table, then shall the rows be appended duplicate as it is or they will append only only row instead of three duplicate rows? Thanks in advance.

[@ExcelGoogleSheets]

They will appear twice.

[@tanmoybhaumik5619]

@@ExcelGoogleSheets Thanks Sir

[@GNAPIKASSTEPS]

If the sheets have different headings ?

[@ExcelGoogleSheets]

Check out my Pivot Table from multiple sheets video for that.

[@jijivarghese908]

Will this work in Excel 2007?

[@mpaul4ever10]

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]

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]

@@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]

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]

Learn Google Spreadsheets did it!! And it worked. Thanks!!!

[@ExcelGoogleSheets]

I'm sure it did :) Cheers!

[@JakirHossain-hq3p]

Dear sir, I don't have power query options. So, could you help to me another options or way.

[@pakmustangX]

Can you please illustrate if I only want to combine few columns from many sheets how can it be done. Thanks

[@ExcelGoogleSheets]

Just select & right click on the columns you want in your Power Query editor and click remove all the other columns.

[@superduper00donstar]

What if I want the far left column to remain the same n just combine the other columns with it?

[@utkarshagarwal8773]

Nice video. I am getting a token literal expected at the in part. Can anyone help

[@MrNaturalcure]

Sir how to copy from Master sheet to Multiple sheets at a same time

[@SteveStowell]

Could use consolidate

[@woss2010]

is this advanced Editor is the same at macro in excel

[@edgarg.quilesortiz724]

The tables would have to contain the same data?

[@ExcelGoogleSheets]

Column names don't always have to match, if that's what you're asking.

[@riaariarini8553]

Thank you very much. Is there any different effect if we just traditionally "copy and paste" each different sheet into one sheet master ?

[@fabrizziorutigliano1924]

Try to copy and paste 80 sheets... You'll see the difference. Not just time, also the possibility of committing errors.

[@WilbanksUSMC]

basically this is turning your excel into a matlab/excel combination

[@abilca4697]

Thanks, but how do you convert 100 sheets to tables and how do you do it when you have over 100 sheets in a workbook?

[@ExcelGoogleSheets]

Try this kzbin.info/www/bejne/mIPRiXSwabCUj68

[@saahil0203]

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]

Yes, if everything is done correctly.

[@tinamarie4550]

I need to append data from worksheets in various workbook files. Is there a way to use the Power Query in this way?

[@ExcelGoogleSheets]

Yes. kzbin.info/www/bejne/o4Pdn6Cclt1-etE kzbin.info/www/bejne/eJ7bo6Z6o9mFapY

[@wenkev02]

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]

This may help you kzbin.info/www/bejne/jKbLaJ-eZ9Kbh80

[@salmaigbokwepriscilla1721]

Thanks but I have a question, what happens if the headers are titled differently?

[@ExcelGoogleSheets]

They will be put in different columns.