Combine all sheets into one in excel | Power Query (the easiest method!)

Merge multiple excel sheets into one using power query
how to merge excel sheets into one
In this video, I have shown step by step how to merge all sheets into one in the current excel workbook using excel power query and automating the process to combine new worksheets also into the combined data.

In this video, we will show you how to use Power Query to combine all sheets in an Excel workbook into one sheet. Power Query is a powerful data transformation and integration tool that is available as a free add-in for Excel, and it allows you to easily combine, shape and clean data from multiple sources.

We will start by opening up an Excel workbook with multiple sheets, and then launching Power Query from the "Data" tab on the ribbon. We will use the "From Table" function to import all of the tables from the Excel file into Power Query.

Next, we will use the "Append" function to combine all of the tables into one. We will demonstrate how to use the "Append" function to merge the tables by matching columns, and we will also show you how to customize the append process by selecting specific columns to include in the merge.

Finally, we will use the "Close and Load" function to load the combined data back into Excel, and we will demonstrate how to update the data in the combined sheet whenever the data in the original sheets changes.

By the end of this video, you will have a solid understanding of how to use Power Query to combine all sheets in an Excel workbook into one sheet, and you will have a powerful new tool in your data analysis toolkit.

[JeJeNtwrk]

thank you so much...

this is a life saving tutorial

[Usmqasim]

Great, Really helpfull as well as easy to explain and save lot of time.

[XCocoaCutieX]

I completely forgot how to consolidate sheets, thank you for sharing. 😀

[nardeenmohammed]

Thanks! this was very helpful
It crashed because of the excel size and limitation; What would be the ideal solution as i want to use it in the power bi not in excel?

[imranali-iywk]

I have a folder containing 19 Excel worksheets with each worksheet containing 16 sheets inside it. While the name of the excel files are different, the name of individual sheets inside them is same i.e. they start from "Sheet 1" and go till "Sheet 16". What I have to do is append each sheet at the end of the next one and continue the same to get a new excel file containing only one sheet. (In other words, sheet 1 from file two (say, B) will be appended at the end of sheet 1 from file one (say, A) and sheet 1 from file three (say, C) will be appended at the end of the above two files containing sheet 1 from files A and B, respectively and so on untill every file is added).

[chingsienstevenfu]

What if i have 20 sheets? Is there a quicker way instead of converting the 20 sheets to Table one by one?

[bappy]

but how to select particular sheets, because I don't need to consolidate all sheets in the existing workbook

[acesolutionssl]

Expression.. error excel.currentworkbook wasnt recognized

[PathologicallySane]

I tried it and I keep getting this error: [Expression.Error] We couldn't find an Excel table named 'New!_FilterDatabase'.