Some time ago I wrote an Excel Tutorial about getting a list of file names from a folder in Excel. In that tutorial, I showed various ways to get the list of file names from a folder (using the FILE function and VBA). However, the limitation of that method is that it can only get the file names from a folder, and not from the sub-folders within the main folder. But you can do this using Power Query (‘Get & Transform’ if you’re using Excel 2016 or later versions).

Where to find Power Query

If you’re using Excel 2016, you don’t need to do anything extra. You will find all the Power Query options in the Get & Transform category in the Data tab.

Note that Power Query and Get & Transform refer to the same thing. For Excel 2010/2013, you need to install the Power Query add-in to use it (steps described below). The above steps would install and activate the Power Query for your Excel.

Get a List of File Names from Folders & Sub-folders

Now let’s see how to get a list of all the files names from a folder and sub-folders within it. Here are the steps to get a list of all the file names from a folder: The above steps would load all the data about the files in your Excel worksheet.

Once you have the data in Excel, you can edit it if needed. With the data that I have in Excel, I can do the following:

Filter the file0 names based on extension (file type) –  it’s in column B. Filter the file names based on the folder name – it’s in column F.

Editing the Columns Data in Power Query

Before loading your data into Excel, you can also edit the data in Power Query. For example, you can delete some columns or get some more metadata for each file. Here are the steps to get additional metadata columns in Power Query editor: This will load the data in the Excel with the selected additional columns. You can also delete columns if you don’t need it. To do this, in the Power Query editor, select the column you want to delete, right-click, and click on Remove.

Get a List of All the Comments in a Worksheet in Excel. How to Combine Multiple Excel Files into One Excel Workbook. How to Combine Data from Multiple Workbooks into One Excel Table (using Power Query). Combine Data From Multiple Worksheets into a Single Worksheet in Excel. Merge Tables in Excel Using Power Query.

Elton Senne