Import all files in a folder into Excel

A common requirement is to import all files in a folder into Excel. These could be text files like .csv’s or even Excel files.

In this case a new option available is to use Excel’s PowerQuery tool (if you are not sure if you have it, visit the Where to find it page). This tool has an option to append a number of files within a folder onto each other to create a single data source.

As shown below (depending on the version this may look different- this is Excel 2016), you can:

  • Click on the Data Ribbon
  • Choose New Query
  • Choose From File, and
  • Choose From Folder
Import all files in a folder into Excel

You then choose the folder that contains the files.

Import all files in a folder into Excel

When you click OK, it will show you the list of files in the folder. You can click Load or Edit

Import all files in a folder into Excel

When you enter the PowerQuery window, you will again see all the files stored in that folder. Note the icon on the first column and the button called Combine Binaries.

Import all files in a folder into Excel

If you click either of these it will combine all the files into a single file. It will first give you the normal CSV options though so that you can specify how it is split.

On clicking OK it will perform the append and you will see a screen similar to the one below. Note the extra column for the Source name. This will be very useful if you ever want to break the file back into its component parts.

Done! You have imported all files in the folder into Excel

What’s great about this is, if more files are added to the folder, you just need to refresh and they will be pulled in automatically!

How to export file names from a folder to excel

Excel CSV opens in one column

Easy fix for promoting headers when name can change issue in PowerQuery

Merge multiple csv files into a single file for use in Excel

Export folder and subfolder list to excel

Pin your regularly used Excel files

Recommended Course for importing all sorts of data into MS Excel