How to list all files in folder and subfolders into a worksheet?
When working with large sets of files stored across various folders and subfolders, you may sometimes need to generate a comprehensive list of all file names for tracking, cataloging, or batch processing purposes. Excel, however, does not provide a direct built-in feature for listing all filenames from a folder—including its subfolders—into a worksheet. This limitation can pose challenges, especially if folder structures are complex or files are regularly updated. Fortunately, there are practical and reliable methods available to achieve this task efficiently within Excel.
In this guide, you’ll discover several approaches to list all filenames—including those inside subfolders—directly into an Excel worksheet, along with detailed operation steps, parameter clarifications, and tips for avoiding common pitfalls. These solutions will help streamline your file management process and can be adapted for various professional or personal organizational tasks.
List all filenames in folder and subfolder quickly and easily with Power Query
List all filenames in folder and subfolder quickly and easily with Kutools for Excel
List all filenames in folder and subfolder with VBA code
List all filenames in folder and subfolder quickly and easily with Power Query
In recent versions of Excel, the built-in Get Data (Power Query) feature can be used to load the list of files from a folder, including selected attributes.
1. Go to Data > Get Data > From File > From Folder.
2. Select the folder that you want to list all files in, click Open.
A table lists all files of the selected folder, including the files in subfolders.
3. Click Load / Load To to load the list to a worksheet.
List all filenames in folder and subfolder quickly and easily with Kutools for Excel
While VBA is adept at generating simple lists, it may feel less convenient for users who aren't familiar with coding or Excel's macro security settings. Furthermore, if you wish to list not only the file names, but also to display their sizes, types, creation or modification dates, and other detailed attributes all at once, the Kutools for Excel add-in offers an intuitive and robust solution.
The Filename List tool in Kutools for Excel enables you to gather file information from a selected folder as well as all its subfolders with just a few mouse clicks. This approach is particularly beneficial for users who require additional flexibility such as filtering by file type, creating hyperlinks for easy access, or including hidden files and folders in the results. It is especially useful for managing document archives, tracking multimedia libraries, or preparing data inventories for backup or audit purposes. Please note, downloading and installing Kutools for Excel is required before proceeding with the steps below.
After completing the installation, follow these steps to use the Filename List function:
1. Click on the Enterprise tab in the Excel toolbar, select Import & Export, then choose Filename List from the menu. This will open the configuration dialog box.
2. Within the Filename List dialog box, you can specify the following options to tailor the file listing to your needs:
A: Click the folder icon 
to select the folder you wish to scan. The tool will automatically include subfolders if desired.
B: In the Files type section, select either "All Files" to include every file regardless of type, or specify a particular file extension (like *.xlsx, *.docx, etc.) if you are only interested in files of a certain type.
C: In the File size unit section, you can choose between units (such as Bytes, KB, MB) for listing file sizes for easier interpretation.
Note: To include filenames from all subfolders, be sure to check the Include files in subdirectories option. Additionally, you can check Include hidden files and folders if you wish to list those as well. Selecting the Create hyperlinks option automatically generates clickable links to each file and folder, which is helpful for quickly accessing files directly from the worksheet. If any files are missing from the list, confirm your filter selections and that the files are not restricted by permissions or system limitations.
3. Click OK to generate the file list. All files from the selected folder and its subfolders will be displayed on a new worksheet, along with the chosen file attributes (such as full path, size, type, created and modified dates). This provides a clear and organized overview, and you can use Excel's familiar filtering and sorting features to further manage your data.
Click to know more details about this Filename List utility.
Download and try Kutools for Excel Now!
List all filenames in folder and subfolder with VBA code
By default, Excel does not include a native function to recursively list files from folders and their subfolders. However, you can make use of VBA (Visual Basic for Applications) to automate this process. Utilizing VBA enables advanced users to extend Excel’s capabilities, giving you flexibility to customize the output—for example, to retrieve just the filenames, or include additional properties such as file paths.
This solution is particularly suitable when you are comfortable with the VBA editor and need to automate similar file listing procedures on a regular basis. It is also helpful for anyone who wants to keep their Excel environment uncluttered without installing any add-ins. However, note that VBA macros require enabling macros in Excel, which may be restricted in high-security environments. Always remember to save your workbook before running any macros to prevent accidental data loss if you have unsaved changes.
1. Create or activate a new worksheet where you want to list all the filenames. Ensure this worksheet is empty to avoid overwriting any important data.
2. Press ALT + F11 to open the Microsoft Visual Basic for Applications (VBA) editor window.
3. In the VBA window, click on Insert in the menu bar, then choose Module. This action will insert a new module where you can paste your VBA code.
VBA code: List all filenames in folder and subfolder
Sub MainList()
'Updateby Extendoffice
Set folder = Application.FileDialog(msoFileDialogFolderPicker)
If folder.Show <> -1 Then Exit Sub
xDir = folder.SelectedItems(1)
Call ListFilesInFolder(xDir, True)
End Sub
Sub ListFilesInFolder(ByVal xFolderName As String, ByVal xIsSubfolders As Boolean)
Dim xFileSystemObject As Object
Dim xFolder As Object
Dim xSubFolder As Object
Dim xFile As Object
Dim rowIndex As Long
Set xFileSystemObject = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFileSystemObject.GetFolder(xFolderName)
rowIndex = Application.ActiveSheet.Range("A65536").End(xlUp).Row + 1
For Each xFile In xFolder.Files
Application.ActiveSheet.Cells(rowIndex, 1).Formula = xFile.Name
rowIndex = rowIndex + 1
Next xFile
If xIsSubfolders Then
For Each xSubFolder In xFolder.SubFolders
ListFilesInFolder xSubFolder.Path, True
Next xSubFolder
End If
Set xFile = Nothing
Set xFolder = Nothing
Set xFileSystemObject = Nothing
End Sub
Function GetFileOwner(ByVal xPath As String, ByVal xName As String)
Dim xFolder As Object
Dim xFolderItem As Object
Dim xShell As Object
xName = StrConv(xName, vbUnicode)
xPath = StrConv(xPath, vbUnicode)
Set xShell = CreateObject("Shell.Application")
Set xFolder = xShell.Namespace(StrConv(xPath, vbFromUnicode))
If Not xFolder Is Nothing Then
Set xFolderItem = xFolder.ParseName(StrConv(xName, vbFromUnicode))
End If
If Not xFolderItem Is Nothing Then
GetFileOwner = xFolder.GetDetailsOf(xFolderItem, 8)
Else
GetFileOwner = ""
End If
Set xShell = Nothing
Set xFolder = Nothing
Set xFolderItem = Nothing
End Function
Before running the code, review the comments to familiarize yourself with the functions and variables used. If you wish to modify what data is listed (such as full paths or file types), you may need to adjust the code accordingly.
4. Once the code is pasted in the module window, press the F5 key to run the code. A Macros dialog box will appear; select the MainList macro name and click Run. If you have another macro named similarly, ensure you select the correct one.
5. In the Browse dialog that appears, navigate to and select the target folder whose files and subfolders you wish to list. Choosing the right starting folder is important, as the macro will recursively retrieve files from all nested subfolders.
6. After you specify the desired folder, click OK. The macro will now process the folder and all its subdirectories, outputting a list of all filenames into the current worksheet beginning at cell A2. Depending on the number of files and the depth of the subfolder structure, this process may take several seconds or longer. The resulting list provides a detailed view of your folder's contents.
 |
 |
 |
If you receive an error, double-check that the selected folder exists and contains files, and ensure macros are enabled in your Excel security settings. If you are working with a large folder structure or network drives, processing time may increase and you may receive timeout or memory errors—try running the macro on smaller subfolders if this happens.
Although VBA is efficient for basic file listing, it generally does not capture attributes such as file size, modification date, or file owner by default. If you require these additional details, consider editing the code or use an alternative method as described below.
For best results, periodically review and update your file lists, especially if contents of the folders change frequently. Always confirm that you have the necessary permissions, especially when scanning network locations or shared drives. If you encounter unexpected results, check for issues such as hidden files, sync delays, or file access limitations. If files or folders are skipped, try running Excel as an administrator or using simplified folder structures.
Choosing the best method depends on your comfort level, specific requirements, and how often you need to perform the task. Each method has its advantages—you can use VBA for customization, Kutools for convenience and more attributes, or Windows/Excel built-in features for the simplest solution without external tools. If issues persist, consult Excel or system documentation for additional troubleshooting.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in