Excel Power Query

You can combine multiple Excel files using Power Query while avoiding the repetition of headers and footers. Here are the steps:

Prepare your files: Ensure all your Excel files are in one folder123.
Open Excel: Open a new Excel file or an existing one where you want to merge the data3.
Go to the Data tab: In the Get & Transform group, click on the New Query drop-down1.
Select From File > From Folder: Hover your cursor on ‘From File’ and click on 'From Folder’1.
Enter the file path: In the Folder dialog box, enter the file path of the folder that has the files, or click on Browse and locate the folder1. Click OK.
Combine the files: In the dialog box that opens, click on the combine button1.
Format the data as table data: Load using “Get Data from Folder” into the Power Query and combine4.
Remove headers and footers: You don’t need to manually delete the header row of all files4. Also, remove the table format after the files are combined if you want4.

This method should help you combine your 55 Excel files into one sheet without repeating the headers and footers. Please note that the structure of your Excel tables should be the same (same column names) for this method to work1. If there are additional columns in other tables, those will be ignored and only the ones specified in the template/key would be combined1.

Reffer below links :

https://trumpexcel.com/combine-data-from-multiple-workbooks/

https://www.xelplus.com/power-query-combine-files-folder/

https://spreadsheetpoint.com/excel/merge-excel-files/

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jithu Thomas2y ago
@Naimish

Kindly note that, the below method is not working and its a method generated from chatGPT.

Thanks & Regards,

JITHU
Naimish Makwana2y ago
You can combine multiple Excel files using Power Query while avoiding the repetition of headers and footers. Here are the steps:
1. Prepare your files: Ensure all your Excel files are in one folder123.
2. Open Excel: Open a new Excel file or an existing one where you want to merge the data3.
3. Go to the Data tab: In the Get & Transform group, click on the New Query drop-down1.
4. Select From File > From Folder: Hover your cursor on ‘From File’ and click on 'From Folder’1.
5. Enter the file path: In the Folder dialog box, enter the file path of the folder that has the files, or click on Browse and locate the folder1. Click OK.
6. Combine the files: In the dialog box that opens, click on the combine button1.
7. Format the data as table data: Load using “Get Data from Folder” into the Power Query and combine4.
8. Remove headers and footers: You don’t need to manually delete the header row of all files4. Also, remove the table format after the files are combined if you want4.
This method should help you combine your 55 Excel files into one sheet without repeating the headers and footers. Please note that the structure of your Excel tables should be the same (same column names) for this method to work1. If there are additional columns in other tables, those will be ignored and only the ones specified in the template/key would be combined1.

Reffer below links :

https://trumpexcel.com/combine-data-from-multiple-workbooks/

https://www.xelplus.com/power-query-combine-files-folder/

https://spreadsheetpoint.com/excel/merge-excel-files/

Thanks