We combined the data from four different workbooks in one single table in a few seconds without even opening any of the workbooks.
![excel merge multiple cells into one excel merge multiple cells into one](https://i.ytimg.com/vi/vDYiWLDaA6A/maxresdefault.jpg)
Now let’s take a minute and understand what we did with the above steps (which only took us a few seconds). In case it doesn’t find an Excel Table with the same name (Table1 in this example), Power Query will give you an error. Since you have selected Table1 as the key, Power Query will look for Table1 in all the workbooks, and combine all these.The columns don’t need to be in the same order as Power Query uses column headers to map columns.In case those other Tables don’t have a column, which is there in your Template Table, Power Query would just put ‘null’ for it. If other Tables have additional columns, those will be ignored. If you select an Excel Table as the template (in Step 7), Power Query will use the column names in this Table to combine the data from other Tables.This will open the final result in the Power Query editor where you can edit the data. In case you want to first Edit the data before loading it into Excel, in Step 6, select ‘Combine and Edit’. This helps in keeping track of what data came from which workbook. Note that along with the data, Power Query automatically adds the workbook name as the first column of the combined data. This will load the final result (combined data) into your active worksheet. Power Query would now look for ‘Table 1’ in other workbooks and combine it with this one. This file would act as the template (or the key) to combine other files. Note that Power Query shows you the Table from the first file. In the ‘Combine Files’ dialog box that opens, select the Table in the left pane.In the dialog box that opens, click on the combine button.In the Folder dialog box, enter the file path of the folder that has the files, or click on Browse and locate the folder.Hover your cursor on ‘From File’ and click on ‘From Folder’.In the Get & Transform group, click on the New Query drop down.Here are the steps to combine the data from these workbooks into a single workbook (as a single table). Now I have four workbooks in a folder that I want to combine.īelow is a snapshot of the table I have in one of the workbooks. For example, if the template/key table that you select has 5 columns, and one of the tables in some other workbook has 2 additional columns, those additional columns would be ignored. In case there are additional columns in other tables, those will be ignored and only the ones specified in the template/key would be combined.
![excel merge multiple cells into one excel merge multiple cells into one](https://www.excelhow.net/wp-content/uploads/2020/08/How-to-Combine-Texts-in-Multiple-Rows-into-One-Cell-Quickly-4.png)
You can choose one of the Tables as the template (or as the ‘key’ as Power Query calls it), and Power Query would use it to combine all the other Excel Tables with it. The number of rows in each table can vary.ĭon’t worry if some of the Excel Tables have additional columns. The below technique would work when your Excel Tables has been structured the same way (same column names).
![excel merge multiple cells into one excel merge multiple cells into one](https://cdn.ablebits.com/_img-blog/merge-rows/merge-rows-formula.png)
![excel merge multiple cells into one excel merge multiple cells into one](https://www.repairmsexcel.com/blog/wp-content/uploads/2018/06/combine-2-columns-excel.png)
#EXCEL MERGE MULTIPLE CELLS INTO ONE HOW TO#
Let’s see how to combine data from these workbooks in each case. This method can also be helpful when you want to combine table/sheets that don’t have a consistent name.