Excel How to Create Hyperlinks to Files in a Folder

This video shows how to use Power Query to generate a list of files in a folder and any sub folders. It shows how you can filter that list if you only want to include certain files or file types. Then it shows how to use the full file path with the HYPERLINK function to create a unique hyperlink to open each file in the list.
- Power Query
- Merge Column
- HYPERLINK


Chapters in this video
00:00 - Introduction
00:15 - Power Query
01:26 - Merge Columns
02:40 - HYPERLINK

Also, check out these videos with time saving Excel Hacks:

[KhushnoodViccaji]

This solution is just perfect for my needs! Thank you so much Jeff! :D)

I have been experimenting with automating Power Query refreshes in my files (using VBA) so that it requires minimum intervention on my part.

One of the things I find very useful is to setup a few named cells in the Excel file to pickup the source data folders / files for further processing.
I do this by using M code at the "Source" step, which refers to the contents of the named cells in the workbook.
This way, I don't have to open the Power Query Editor and manually copy-paste the folder name in the Source step.
The named cells can contain static text (folder names) or formula-based dynamic values which display the folder name of the current workbook.

Then VBA code takes care of refreshing the table.
Another thing I do in the VBA module is to adjust column widths to pre-defined values to prevent extra-wide columns when the fresh data might have long folder names!

[johnboffin]

Thanks Jeff, You are a great teacher, I have done it all by myself seeing your clues, here to tie down column for un-switchable data into wrong column here =OR(C10=Expenses) Expenses is the named range to column E (Validation custom) thanks again

[sandroganci]

Thanks for the tutorial, would it be possible to have the hyperlink column generated directly by powew query ?

[paull]

Great content. Can you add the hyperlink in power query?

[HJay]

Is there a quick way to insert a hyperlink into an excel spreadsheet that is a file in a folder and retain the name of the file in the folder? Let’s say you were attempting to link invoices to a download bank statement.

[JohnMacLeod-wuin]

All the files my team works with are on sharpoint folders we access via MS Teams. Will this work for them, or are file locations and links too complex or dynamic?

[fliptop]

Just can't sort the power query table because the links get shuffled as the new column added is not part of the query. Tried finding a solution to this, and nothing seemed to work other than copying the PQ table and pasting as values, then adding your new column to create as a new table.