Create Hyperlinks to Files in a Folder

Today, we’re diving into an exciting way to enhance your data organization skills using Excel. I was recently asked the following question: Is there a way to create a list of files so that the files in the list could be hyperlinked back to the source in the folder? I’m going to answer that question in this post.

Specifically, we’re going to be discussing how you can create a list of files in a folder with Power Query and then how to add a column with a hyperlink back to each file in the list. This step-by-step guide will provide you with clear and concise instructions, making it easy to follow whether you’re just starting with Excel or a seasoned user.

Step-by-step Guide

We’ll walk through the key components in the following exercises. Specifically, we will generate the basic list of files in a target folder and subfolders, learn how to filter out unwanted files, merge the path and name, and then create hyperlinks to each file.

Exercise 1: Retrieving a List of Files Using Power Query

To start this process, we first need a list of files in our target folder. This is where Power Query becomes our best friend, providing us with an easy way to create a list of files in a folder and any subfolders.

To get started:

1. Data > Get Data > From File > From Folder
2. Browse to your desired folder and click Open
3. Power Query will provide a preview of the files, like this:

Click Transform Data to view in the Power Query editor.

Here you can filter out any unwanted files. For example, can can filter based on file extension, name, path, dates, or other. Just use the filter controls in the desired columns.

The two columns we need to create our hyperlinks are the Folder Path and Name columns. You can keep all of the columns if you’d like. Just keep whichever columns you want returned back to Excel.

For our purposes, we’ll keep the Folder Path and Name columns. So, we select both of those columns (hold down Ctrl) and then use the Home > Remove Columns > Remove Other Columns command.

With the file list looking good, it is time to head to the next exercise.

Exercise 2: Merged fields

Now, let’s tweak the query a bit to prepare the data for the hyperlinks. We want to combine the Folder Path and Name columns into one single column.

1. Select the Folder Path column first, and then the Name column (hold Ctrl)
2. Click Add Column > Merge Columns
3. Leave the separator as None
4. Click Ok

Now you’ll have a new column that contains the full file path and file name, which is exactly what we’ll need to create our hyperlink.

With that complete, it is time to send the results back to Excel.

Note: instead of creating a new merged column, you have the option to replace the Folder Path and Name columns with a single column. The steps are the same as above, except that instead of using the Add Column > Merge Columns command, you use the Transform > Merge Columns command.

Home > Close & Load To > Worksheet. You can opt for a new or existing worksheet, depending on your preference. The results table is placed in the specified worksheet:

Now our final step is to create a hyperlink column.

Exercise 3: Adding the Hyperlinks

The final step in our process is adding a hyperlink column to our table.

Start by adding a new column to your data table that will store the hyperlinks. To do this, simply type the column label (ie, Hyperlink) in the cell immediately to the right of the final column label. When you do, the table will automatically expand and create the new column:

Then, in the very first data row in the new column (the cell immediately under the column label), create a formula with the HYPERLINK function.

The HYPERLINK function has two arguments … the file path and the friendly label. For the file path argument, simply click the cell to the left (the Merged column that includes the full path). For the friendly label argument, simply type any text surrounded by quotes. So, your formula would look something like this:

=HYPERLINK([@Merged],"Open file")

Hit Enter, and bam:

You now have a neat column of hyperlinks ready to whisk you away to your files with a single click.

Refreshing Your Data

The beauty of using Power Query is that you can update your list of files and hyperlinks whenever you want by simply clicking Refresh. Power Query will do the rest of the work and updated the hyperlinks table accordingly.

If you have any alternatives, questions, or enhancements, please share by posting a comment below … thanks!

Sample file

Frequently Asked Questions

Q: Can I specify the types of files listed in Power Query?

Absolutely! After you load your data to Power Query, you can apply filters based on file names, extensions, and key dates.

Q: Why can’t I see the “Merge Columns” option in Power Query?

Ensure you’ve selected two columns to merge – the option won’t be available otherwise.

Q: Can I add more than one hyperlink to a cell in Excel?

No, Excel only allows one hyperlink per cell.