Export Folder and Subfolder List to Excel (with Quick Steps)

The Power Query tool can be used to export .xlsx and .txt file inside a folder and subfolder list to Excel.

Sample folder for exporting folder and subfolder list to Excel

Step 1 – Choose a Folder from the Data Tab

Go to the Data tab and select the From Folder option from the Get Data feature.

Using data feature to export data from a folder

Select the folder to export from local storage and click Open.

Choosing the folder from which data is exported

Read More: Export Folder Structure to Excel

Step 2 – Import Data to Worksheet

A preview window will open, click the Load To option.

Visiting load to option to import data to a chosen location

Import Data window will pop-up, input the cell location for inserting data and click OK.

Selecting cell location to import data

Step 3 – Editing Data in Power Query Editor

Edit the exported data by clicking the Edit option from the Query feature.

Visiting query feature to edit exported data

When selecting multiple columns, choose Remove Columns from the Manage Columns option to delete columns.

Removing columns using the Power Query editor

You can make a duplicate of the column from the Duplicate Column option by choosing the folder path column.

duplicating column using the Power Query editor

Split the column by clicking the By Delimiter option from the Split Column

Spliting columns using delimiter

In the pop-up window, choose the Custom option from the drop-down list.
Input a delimiter (\), choose Columns from the Advanced Options, and enter a column number. Depending on the number of columns you need, enter the desired number.
Click OK.

Splitting into multiple columns using the split column feature

The folder and subfolder lists are split.
Click on Close and Load.

The folder and subfolder list are successfully exported to Excel.

FInal output with exported folder and subfolder list to excel

How to Show Folder and File Lists of a Certain Path in Excel

The Web browser can be used to show folder and file lists for a certain path.

Sample folder containing file lists

Open the folder and copy the folder path.

copying path for the selected folder

Open the web browser and paste it in the Address bar and Press the ENTER

Placing the folder path inside an address bar of a browser

The file lists will be displayed inside your browser.
Select the lists, press CTRL+C to copy.

File list inside a browser

Open your Excel workbook and paste the copied list.

Final result with file list from a certain path in excel

Frequently Asked Questions

Can I export a folder and subfolder list to Excel on a Mac?

Yes, it is possible to export a folder and subfolder list to Excel on a Mac by using the Terminal application to generate a list of files and folders in the desired directory, and then copying and pasting that list into an Excel worksheet.

What information can be exported to Excel from a folder and subfolder list?

The information that can be exported to Excel from a folder and subfolder list includes file name, size, date created, date modified, and other relevant information.

Download Practice Workbook

Export Folder and Subfolder List.xlsx

<< Go Back to Power Query Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Sub SplitFilesBySubfolder() Dim ws As Worksheet Dim newWs As Worksheet Dim lastRow As Long Dim folderCol As Long Dim dict As Object Dim cell As Range Dim folderName As String Dim safeSheetName As String Dim key As Variant Set ws = ActiveSheet Set dict = CreateObject("Scripting.Dictionary") ' Change this column number if your folder path is in another column folderCol = 1 lastRow = ws.Cells(ws.Rows.Count, folderCol).End(xlUp).Row ' Collect unique subfolder names For Each cell In ws.Range(ws.Cells(2, folderCol), ws.Cells(lastRow, folderCol)) folderName = Mid(cell.Value, InStrRev(cell.Value, "\") + 1) If folderName <> "" Then If Not dict.exists(folderName) Then dict.Add folderName, folderName End If End If Next cell ' Create separate sheets and copy matching rows For Each key In dict.keys safeSheetName = Left(CleanSheetName(CStr(key)), 31) On Error Resume Next Application.DisplayAlerts = False Worksheets(safeSheetName).Delete Application.DisplayAlerts = True On Error GoTo 0 Set newWs = Worksheets.Add(After:=Worksheets(Worksheets.Count)) newWs.Name = safeSheetName ws.Rows(1).Copy Destination:=newWs.Rows(1) ws.Range("A1").CurrentRegion.AutoFilter Field:=folderCol, Criteria1:="*" & key & "*" ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=newWs.Range("A1") newWs.Columns.AutoFit ws.AutoFilterMode = False Next key MsgBox "Files have been split into separate sheets by subfolder." End Sub Function CleanSheetName(sheetName As String) As String Dim invalidChars As Variant Dim ch As Variant invalidChars = Array("/", "\", "[", "]", "*", "?", ":") For Each ch In invalidChars sheetName = Replace(sheetName, ch, "_") Next ch CleanSheetName = sheetName End Function

2 Comments

Charles Apr 26, 2026 at 9:30 PM

What a great page Wasim!

I didn’t see it, but I have a large directory that has recipes. In that recipe directory there are dozens of sub directories such as fish, vegetables, appetizers, etc. I have followed your instructions to import that recipes directory, and it’s great! However, is there a way to import the recipes directory, and have it separate all the subdirectories into individual sheets or something like that? It’s great what I have, but it currently has all the files in all the subdirectories in a long list. I could cut and paste manually, but I was just curious if you know a way to automate that somehow, if that makes any sense?

Again, GREAT page!

Charles Bowen

Shamima Sultana Apr 29, 2026 at 10:43 AM

Hello Charles,

Thanks for your kind words—really glad the method worked for your recipe folders!

Yes, what you’re asking is possible, but it does require a bit of automation beyond the basic method shown in the article. Since the current approach lists everything in one sheet, you’d need to use VBA (macro) to split the results by subfolder.

Here’s the idea in simple terms:

1. After importing the full list (with folder paths),
2. You can use a VBA script to:
2.1. Identify each unique subfolder (like Fish, Vegetables, Appetizers, etc.)
2.2. Automatically create a new worksheet for each one
Filter and copy the relevant files into their respective sheets

If you prefer a non-VBA workaround, you could:
1. Use Excel Filters or Pivot Tables based on the folder path column
2. Or use Power Query to group/split data by folder (more dynamic and refreshable)

But for fully automatic sheet creation, VBA is the most efficient option. You can use this VBA macro after importing the folder/subfolder list into Excel.

Just change this line if your folder path is not in column A:

folderCol = 1

For example, use folderCol = 2 if the folder path is in column B.

Regards,
ExcelDemy