Forum Discussion
How to combine multiple sheets in a workbook into one sheet?
We have an excel file, with multiple sheets (1,2,3,4....2000); every sheet has 5 to 50 rows of data. How can I combine all of the sheets into one Master sheet in the excel file. Any help on this is appreciated. Adding an excel file with two sheets (sheet1 & sheet2) want them to come into one master sheet 3 or so. My real file has more than 2000 sheets.
Thanks in advance,
Ramesh
If data in all the sheets in the workbook is formatted as an Official Excel Table, you may create a blank Power Query to combine all the data into one table called MasteData.
let Source = Excel.CurrentWorkbook(), #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Master")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}), #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}) in #"Changed Type"For more details, refer to the attached.
If you insert a new sheet in this file, convert the data in that new sheet into an Official Excel Table and then go to Master Sheet, right click inside the green table and choose Refresh and this table would then be updated and would contain the data from the newly added sheet.
3 Replies
If data in all the sheets in the workbook is formatted as an Official Excel Table, you may create a blank Power Query to combine all the data into one table called MasteData.
let Source = Excel.CurrentWorkbook(), #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Master")), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name"}), #"Expanded Content" = Table.ExpandTableColumn(#"Removed Columns", "Content", {"Column1", "Column2", "Column3"}, {"Column1", "Column2", "Column3"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", Int64.Type}}) in #"Changed Type"For more details, refer to the attached.
If you insert a new sheet in this file, convert the data in that new sheet into an Official Excel Table and then go to Master Sheet, right click inside the green table and choose Refresh and this table would then be updated and would contain the data from the newly added sheet.
Subodh_Tiwari_sktneer Thanks a lot
You're welcome!
