Looking to combine multiple files with a similar structure? Achieve it effortlessly in just a few clicks using Power Query in Excel.
From the example below, let‘s see how to merge multiple files. We have a monthly file that we want to aggregate into an annual file.
To merge multiple files, group all your files into one folder. No other files should be in this folder.
Open an Excel file (the future annual file). Then on the Data menu, click Get Data.
Then click From File, then From Folder.
The Power Query editor opens and shows you the files it found in the folder.
The usual loading and transformation options are present. You have the option to load the data as a table or a connection. But also to transform them before loading them.
To load the data as–is, click Combine & Load.
If your files contain several tabs, you will have to choose which tab to take.
All files have been merged. The lines follow one after the other.
You will notice that the 1st column indicates the source file.
The result is dynamic. So if the source data changes, you can simply refresh to see the changes.
