Lesson#101: Hyperlink all files and folders in the worksheet within a minute

Here in this post, I will show you how to hyperlink all files and folders in the worksheet within a minute. This can be done very easily. Often we need to make the summary of soft copies and link those files in a single excel sheet. It is very necessary for office people and also for students. With this method, we can hyperlink all the files within a folder in the worksheet easily.

Here in the above picture, I have shown a screenshot of a folder destination on my hard drive. I need to hyperlink all the files and folders within this directory. Then I need to take out a list of all files and folders within this directory. Here’s how it is done.

Press the Windows logo button + R and type cmd and press Enter 

Command Prompt window will appear.

Copy the folder path and go to CMD.

There you have to go to the parent drive as I have typed

C:\Users\Puspendu>e:

You will be in the drive. Now you write there. If you are having a lower version of Windows like Windows 7 then you can’t paste directly by Ctrl+V

You have to write click on the header bar of CMD and go to Edit>Paste

E:\>cd<SPACE><FOLDER PATH>

Like I have entered

E:\>cd E:\Books\MS excel

And you will be in the folder and you write there dir>dir.txt to export all list of files and folders in a .txt file.

Copy the necessary part from the notepad and paste in an excel sheet.

Now select the column and go to DATA>Text to Columns

Select Fixed Width and go to Next there you set the break lines and click Next and Finish.

Paste the folder directory in another column at every row as I have shown in the above picture.

Now join both like FOLDER PATH\FILENAME with & symbol. This will create the file path.

Now use HYPERLINK to make hyperlinks easily in this syntax

=HYPERLINK(link_location, [friendly_name]) 

That is how to create hyperlinks to all files and folders in the worksheet easily.

Related Video Tutorials: