Load a Folder of CSV or Excel Files into a Single Excel Worksheet
February 20, 2023 - by Bill Jelen

Problem: My I.T. department products a CSV file for every customer and I have to load all of those into Excel.
Strategy: Power Query can load all of the CSV files into a single grid. When the I.T. department adds more CSV files, you can simply refresh the query and Excel will load the entire folder again. If you have a folder of Excel files and you need to load a single worksheet from each workbook, the same steps will work.
Follow these steps:
1. Select New Query, From File, From Folder.
-
2. Browse to the folder.
3. In the Preview window, choose Edit instead of Load.
4. Excel will show you a list of all files in the folder.
5. Use the filter dropdown on the Type column to remove anything that is not a .CSV file.
6. Click the icon in the Binary column header. It is two arrows pointing down at a line.

I used Power Query for over a year before I realized this button did anything. I had to read the M is for (Data) Monkey book before I realized the awesome power of loading 200 CSV files into a single Excel worksheet in a single click.
This article is an excerpt from Power Excel With MrExcel
Title photo by Noah Windler on Unsplash