If you have multiple Excel files you need to combine in to one list, Power Query may have the answer for you. Using the import from folder option, you can import multiple xlsx or csv files in to one Excel file without having to manually copy and paste the data one file at a time.
To give Power Query the best chance please be mindful of the below:
- The files need to be of the same structure E.G. same headings in the same place. If not, Power Query will try its best but it cannot work miracles.
- All the files you want to import should ideally be in the same folder to make the import easier.
How To:
- Open Excel
- Click on the "Data" tab
- Click "Get Data" (first option on the left)
- Click "From File"
- Click "From Folder"
- Use the pop up to browse to the folder that contains the xlsx or csv files you want to combined
- The following screen will appear. It displays a list of the all the files found in the folder selected previously. Click "Combine" and then "Combine & Load to"
- Use the screen below to check the data is as expected, and then click ok:
- Choose where you would like to import the data to. If you accept the default options below, the data will be imported in to a new sheet:
- And following a moment, Power Query / Excel will import your data from all the files and add it to your spreadsheet.
If this was useful and you would like more Power Query tips to be added to our blog, please leave a comment below.