How to list all filenames in a folder and create hyperlinks for them in Excel?
In many office or project management scenarios, you may need to quickly obtain a list of all filenames from a particular folder and instantly access them by clicking hyperlinks within Excel. This can be especially useful when working with directories filled with contracts, reports, images, or logs, ensuring seamless navigation and organization. While it’s possible to copy filenames and manually insert hyperlinks one by one, this method becomes impractical and extremely time-consuming when dealing with dozens or even hundreds of files. Fortunately, Excel offers several solutions—both built-in and with the help of special tools—to streamline this process and greatly enhance productivity. Here, you will learn about a range of approaches, their application scenarios, and key tips for efficient and error-free operation.
➤ List all filenames in a folder and create hyperlinks with Kutools for Excel
➤ List all filenames in a folder and create hyperlinks with Excel formulas
➤ List all filenames in a folder and create hyperlinks using Power Query
List all filenames in a folder and create hyperlinks with VBA code
When you need to list all files from a folder and generate clickable hyperlinks for each within Excel, using a VBA macro can be a direct, hands-on solution—especially ideal if you are comfortable with macros and require an automated approach for folders that regularly change content.
1. Open a new worksheet where you want to display the results.
2. Press Alt + F11 to open the Microsoft Visual Basic for Applications window.
3. In the VBA window, go to Insert > Module, and paste the following code snippet into the module area:
VBA: List all filenames in a folder and create hyperlinks
Sub Example1()
'Updateby Extendoffice
Dim xFSO As Object
Dim xFolder As Object
Dim xFile As Object
Dim xFiDialog As FileDialog
Dim xPath As String
Dim I As Integer
Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
If xFiDialog.Show = -1 Then
xPath = xFiDialog.SelectedItems(1)
End If
Set xFiDialog = Nothing
If xPath = "" Then Exit Sub
Set xFSO = CreateObject("Scripting.FileSystemObject")
Set xFolder = xFSO.GetFolder(xPath)
For Each xFile In xFolder.Files
I = I + 1
ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
Next
End Sub
4. Press F5 to run the VBA macro. A Browse dialog box will appear, allowing you to select the folder whose files you wish to list.
5. Once you have chosen the desired folder and clicked OK, all the file names within that specific folder (but not in subfolders) will be placed into the active worksheet, each with a clickable hyperlink for immediate file access. See the example output below:
- Note: This VBA script lists files only in the main folder you select; it excludes subfolders. If you need to include subfolders regularly, consider the Kutools method or adjust the VBA to recurse subdirectories.
- Macro settings: If the macro doesn’t run, ensure macros are enabled (File > Options > Trust Center > Trust Center Settings > Macro Settings) and that you have permission to access the selected folder.
List all filenames in a folder and create hyperlinks with Kutools for Excel
For quick and flexible listing of all files in both a folder and its subfolders, including various attributes like size, creation time, and directory, Kutools for Excel offers the Filename List feature. This approach is especially well-suited for users who need to frequently generate such lists, manage large data volumes, or require extended options (e.g., filtered file types, visibility of hidden files/folders, and effortless creation of hyperlinks).
If Kutools for Excel is installed on your system, follow these steps:
1. Click Kutools Plus > Import & Export > Filename List as shown below:
2. In the Filename List dialog window, perform these steps:
(1.) Click the 
button to specify your target folder;
(2.) If you wish to include files within subfolders, check Include files in subdirectories. To show hidden files, also check Include hidden files and folders. This gives you full control over which files are displayed.
(3.) Under Files type, define the extensions or file types you wish to include (for example, *.xlsx, *.docx, *.pdf, etc.);
(4.) Select the unit for file size display as needed (Bytes, KB, MB, etc.);
(5.) Make sure to check Create hyperlinks at the lower-left corner to enable direct file access from the list.
Double-check your selections for accuracy, especially when working with large or shared folders, to avoid unnecessary processing time or privacy issues.
3. After configuring the dialog, click OK. The tool will automatically generate a comprehensive, hyperlinked list of all files meeting your criteria in a new worksheet. This process includes files from all selected subfolders and applies the chosen display options. Here’s a typical result:
Click to know more details about the Filename List utility…
Download and free trial Kutools for Excel Now!
Tips:
- This tool is especially useful for recurring reporting tasks, archiving, or document retrieval needs in team collaborations.
- Consider creating a dedicated output worksheet before generating the list to keep your workbooks organized.
- Always review the results—network or permission issues can occasionally cause missing entries.
Demo: List all filenames in a folder and create hyperlinks with Kutools for Excel
List all filenames in a folder and create hyperlinks with Excel formulas
If you have already acquired the list of filenames and their relative or absolute paths by other means—such as copying from Windows Explorer into Excel, using the DIR command, Power Query, or other import tools—you can leverage Excel’s built-in HYPERLINK function to quickly generate clickable links for each file. This approach is accessible, does not require macros, and suits users who only need to create hyperlinks from a given file list, regardless of how the list was created.
1. Suppose in column A you have full file paths (from A2 downward) like C:\Documents\Report1.xlsx, C:\Documents\Report2.xlsx, etc. In cell B2 (next to the first file path), enter this formula:
=HYPERLINK(A2, A2)2. Press Enter. Then copy the formula down for all rows that contain file paths (drag the fill handle or double-click it). This produces clickable links that display the full path as the link text.
3. If you want the displayed text to be only the file name (not the entire path), use:
=HYPERLINK(A2, MID(A2, FIND("@", SUBSTITUTE(A2, "\", "@", LEN(A2) - LEN(SUBSTITUTE(A2, "\", "")))) + 1, 255))This keeps the hyperlink target as the full path in A2 while displaying only the file name.
Excel 365 shortcut (if available):
If your Excel supports TEXTAFTER, a simpler filename display is:
=HYPERLINK(A2, TEXTAFTER(A2, "\", -1))4. If your cells in column A contain only file names (no paths) and all files are in the same folder, prepend the folder path in the formula (adjust the path for your case):
=HYPERLINK("D:\SharedFiles\" & A2, A2)Tips:
- Check path validity: Ensure the paths in column A are correct and accessible. UNC/network paths (e.g.,
\\server\share\file.xlsx) are supported if your system has access. - Display text flexibility: Replace the second argument of
HYPERLINKwith any descriptive text you prefer. - Relative paths: If your workbook is saved in the same parent folder, relative links also work (e.g.,
.\Sub\file.xlsx).
Limitations: Excel formulas cannot directly scan disk folders. You must obtain the file list (and paths) first—via Windows Explorer copy-paste, Power Query, a VBA script, or another method—then use the formulas above to build hyperlinks.
Advanced scenario (web): In Excel for the web, Office Scripts can automate list gathering and hyperlink insertion for cloud locations (OneDrive/SharePoint). See Microsoft’s Office Scripts documentation for details.
List all filenames in a folder and create hyperlinks using Power Query (Get & Transform)
Power Query (built into modern Excel) provides a reliable, refreshable way to import a folder’s file list and keep it up to date. It’s ideal when you want dynamic updates, optional inclusion of subfolders, and easy filtering by file type or date.
Step-by-step
1. Go to the Data tab > Get Data > From File > From Folder.
2. In the dialog, click Browse, choose your target folder, then click OK.
3. Click Transform Data to open the Power Query Editor. You’ll see a table of files (by default, including subfolders via the Folder.Files connector).
4. Remove columns you don’t need (e.g., Content). Keep Folder Path, Name, Date modified, Extension, etc., as required.
5. Create a full path column: Add Column > Custom Column > formula: [Folder Path] & [Name], name it FullPath.
6. (Optional) Filter by Extension to include only certain types (e.g., .xlsx, .pdf).
7. Click Close & Load to load the table into Excel.
Make the links clickable in Excel:
After the Power Query table is loaded (e.g., Table1) and contains a FullPath column, add a new worksheet column next to it with this formula:
=HYPERLINK([@[FullPath]])Fill the formula down. You now have clickable hyperlinks that display the full path as the link text. Whenever files in the folder change, use Data > Refresh All.
- Subfolders: The From Folder connector uses
Folder.Files, which returns files from the folder and its subfolders by default. Filter by Folder Path if needed. - Hidden/system files: May be excluded depending on permissions and settings.
- Hyperlinks: Power Query outputs text; use the Excel
HYPERLINKformula (shown above) to make clickable links in the worksheet.
Related article:
How to list all files in folder and subfolders into a worksheet?
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