Excel - Combine Data from Multiple Sheets into One with Sheet Names & Different Columns

Learn how to combine data from multiple sheets (tabs,worksheets,tables) in Microsoft Excel with sheet names listed in one of the columns using Power Query. This also works when your columns are different, not ordered the same way and some don't match. Updates will be automatic automatic and can even pickup new sheets you add to your file and pull it to the master sheet with all the data combined into one tab. This can be done both in the source file or a separate file.

#excel #combine #data #multiple #sheets

[jjensen]

Amazing video - Simple and easy to follow your instructions for a complex action! Thank you so much!!!

[vspeller]

Great teaching Thank you so much !!! So easy to follow !

[jimvwork]

I can't seem to import the file in the same open workbook. Why? I get "Unable to connect. We encountered an error while trying to import. The process cannot access file C:\xxxx\xxxx.xlsx' because it is being used by another process".

[shyamadasgupta]

How does not have more likes? Thank you! I dumbo like me just executed all the steps with you simultaneously and succeeded :) Subscribed

[jeyaprakashjebapandian]

Thanks for the video. It saved me a lot of time.

[StephanAshkenazy]

Thanks a lot for this simply and clearly described complex action! For me it is very useful and I will apply it in my work. Keep making such useful videos. Thank you!!!!

[nejibghediri]

This is great, much more effective than the consolidate function, thank you! Do you know how to do it if the headers were not in the first row, but the second or nth row instead?

[edanpenaroyo]

Thank you very much. Your tutorial made my work faster.

[andrewjones]

Used this site for all my Google dev work. Now starting on Excel. Awesome tutorials.

Is there a similar way of getting data where all the files are in OneDrive? I cannot find one.

[nitanew]

what do you do when replacing "null" with "true" does not pick up the column headers? I have tried using other options such as promote header, value, list all true, etc.

Please help, please help

[eva-lenaengman]

Wow, amazing tutorial, so efficient! Thank you!!

[jhonjairobricenoguatava]

the formula "importrange" can be added to the string

[makeitviral]

Hello <

Thanks for this.

Learned something useful Today 16 Aug 2020.

[luismoscoso]

Can you combine two columns into one instead of using Concatenate?

[NashBashy]

For some reason, when I'm doing this, I see all the data (just like you're showing us)...but when I do "Close and Load", it creates a new worksheet, but it has no data in it. And over on the righthand-side of Excel, it has a section "Queries and Connections" and my named Query is there, but it says under it "Connection only" instead of saying the number of rows like yours does. I'm thinking that maybe I need to start from scratch again & see if I missed an important step or something. Obviously, it's connecting well because I see all of my data from my two worksheets all consolidated nicely. But it's just not putting it into the new sheet it creates. :( If you or anybody else has any tips, I'd welcome them. :)

[labibchoudhry]

Can you do this if you have nested headers i.e. two columns merged with the cell containing the word "2020", under which there are two cells with the titles "January" and "February". But then in the next column you have another two columns merged for "2019", under which there are two cells again with the titles "January" and "February"?

[garylillich]

How does this compare to MS Excel procedure?

[luciapernambuco]

Great and very well done. I have a Question about what you just said about the LINK. What IF I do All this work and send it to my supervisor? He will download and open on his computer, it will loose the connection?

[satwantsingh]

Hello sir, I want that if I make changes to master file the same changes must be happened to the source files. Is there any solution?

[Entertainment-usgt]

Hello. Let me know if there is a way to do this. I would like to program a range of cells in such a way that when I write a number in a cell, that is seemingly empty, Google Sheets will use that number and execute it in a formula that will then display the result in the same cell.