Excel List All Files in Folder and Subfolders

To list all files in a folder and its subfolders in Excel, you can use a simple Power Query approach or a VBA macro. This article explains both methods step by step, along with the background, limitations, and alternatives, so you can choose the one that fits your needs.

Why You Might Need This

Manually typing filenames is impractical when you have dozens or hundreds of files across nested folders. You might need a list for auditing, inventory, reporting, or simply to organize your work. Excel doesn’t have a built-in “list files� button, but it offers several ways to get the job done without third-party software.

Method 1: Using Power Query (Recommended for Most Users)

Power Query is a built-in Excel tool that can import data from folders. It’s available in Excel 2016 and later (including Microsoft 365). Here’s how to use it:

1. Open a new Excel workbook.
2. Go to the Data tab and click Get Data > From File > From Folder.
3. Browse to the folder you want to start from (the top-level folder).
4. Click OK – Excel will show a preview of the files it finds.
5. Click Load to import the list into a worksheet.

The result includes columns like Name, Extension, Date accessed, Date modified, and Folder Path. By default, Power Query does not include subfolders – you need to enable that. In the preview window, click Transform Data, then in the Power Query editor, look for the Folder Path column and filter it to include subfolders (or use the “Expand� option if you see a column with the folder structure). Alternatively, you can use a custom function to recursively list all files – a common pattern is to create a blank query with the following M code (replace the path with your own):

let
    Source = Folder.Files("C:\YourFolder"),
    #"Filtered Hidden Files" = Table.SelectRows(Source, each [Attributes]?[Hidden]? = false)
in
    #"Filtered Hidden Files"

This returns all files in the folder and all subfolders, with full paths.

Method 2: Using a VBA Macro

If you prefer a one-time script or need more control, a VBA macro can do the job. Press Alt+F11 to open the VBA editor, insert a new module, and paste the following code:

Sub ListFiles()
    Dim fso As Object
    Dim folder As Object
    Dim file As Object
    Dim subFolder As Object
    Dim rowNum As Long
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder("C:\YourFolder")
    rowNum = 1
    
    ListFilesInFolder folder, rowNum
End Sub

Sub ListFilesInFolder(folder As Object, rowNum As Long)
    Dim file As Object
    Dim subFolder As Object
    
    For Each file In folder.Files
        Cells(rowNum, 1) = file.Path
        Cells(rowNum, 2) = file.Name
        Cells(rowNum, 3) = file.Size
        rowNum = rowNum + 1
    Next file
    
    For Each subFolder In folder.SubFolders
        ListFilesInFolder subFolder, rowNum
    Next subFolder
End Sub

Run the macro (F5) and it will list all files (including those in subfolders) in columns A, B, and C. This method is fast and doesn’t require any special add-ins.

Method 3: Using Windows Command Prompt (CMD)

If you’re comfortable with the command line, you can generate a file list and then import it into Excel:

1. Open Command Prompt (cmd).
2. Navigate to the folder you want: cd C:\YourFolder
3. Type: dir /s /b > filelist.txt
4. Open Excel, go to Data > From Text/CSV, and select the filelist.txt file.

The /s flag includes subfolders, and /b gives a bare format (just the full path). This is a quick way to get a list without any coding.

How to Choose the Right Method

The best method depends on your situation:

• Power Query – Best if you want a refreshable list that updates when files change. It’s also the most user-friendly for non-programmers.
• VBA – Best if you need to run the list repeatedly with custom formatting or if you want to manipulate the data further before outputting.
• CMD – Best for a one-time, quick export without opening Excel first.

If you need to filter by file type (e.g., only .xlsx), use Power Query’s filter on the Extension column, or add an If condition in VBA.

Common Problems and Solutions

Problem: Power Query doesn’t show subfolders.
Solution: Use the Folder.Files function instead of Folder.Contents – it automatically includes all subfolders. Alternatively, after loading, you can append the “Folder Path� column to the “Name� column to get full paths.

Problem: VBA macro gives “Permission denied� or “File not found� errors.
Solution: Check that the folder path is correct and that you have read permissions. Also ensure you don’t have the same file open in Excel – that can cause conflicts.

Problem: The list is too long and slows down Excel.
Solution: Limit the number of files by filtering in Power Query, or use a script that writes directly to a text file instead of a worksheet.

Comparing the Methods

Method	Ease of Use	Includes Subfolders	Refreshable	Requires Coding
Power Query	Easy	Yes (with Folder.Files)	Yes	No (but optional M code)
VBA	Moderate	Yes	No (must re-run)	Yes
CMD	Easy	Yes	No (must re-run)	No

Power Query is the most flexible because you can refresh the list with one click, and you can also transform the data (e.g., extract file extensions, filter by size) before loading it into Excel.

Frequently Asked Questions

Can I list files from multiple folders at once?

Yes. In Power Query, you can combine multiple folders by using the “Combine� feature when you select multiple folders, or you can use a custom function that loops through a list of paths. In VBA, you can call the subroutine for each folder.

How do I include file sizes and dates?

Power Query automatically includes columns for size, date modified, and date accessed. In VBA, you can use file.Size, file.DateLastModified, and file.DateCreated to get those values.

Why does my Power Query list show hidden files?

Power Query includes hidden files by default. To exclude them, filter out rows where the “Attributes� column contains “Hidden� (you may need to expand the attributes). In VBA, you can check the file.Attributes property.

Can I list only certain file types (e.g., PDFs)?

Yes. In Power Query, filter the “Extension� column to “.pdf�. In VBA, add an If condition: If file.Name Like "*.pdf" Then .... In CMD, you can use dir /s /b *.pdf.

Is there a way to do this without any coding?

Yes. Use the CMD method – it’s a single command and no programming knowledge is needed. Or use Power Query’s folder import and simply load the default table – that requires no coding either, though you may need to manually combine subfolder data if you don’t use the custom function.

Limitations and Considerations

All methods have limitations. Power Query can be slow with very large folders (tens of thousands of files). VBA macros may be blocked by security settings – you’ll need to enable macros. CMD output is plain text, so you lose metadata like file size and dates unless you use a different command. Also, remember that file paths can be long; Excel has a limit of 32,767 characters per cell, but that’s rarely an issue.

When sharing workbooks that contain a file list, keep in mind that the list is static unless you refresh the query. If you need a live list, Power Query is the only option that supports refresh.

Related

• Palworld Best Base Locations With Coordinates
• Arc Raiders Tech Loot: How Thermo Lining Works
• Peacock TV Keyboard Shortcuts (Windows & Mac)