- Open a new Excel sheet.
- Open the folder where the files are.
- Select the files and right-click any of them. Select Copy as Path. You may select multiple files at the same time.
- Go to Excel and paste the path on Column A.
- Paste the path again on Column B.
- Double-click any cell with a path to edit it. Select only the part of the path that comes before the file name. Copy it.
- Click the A column. If you click the letter A, it will select the entire Column.
- Go to Find & Select on the top menu bar. Go to Replace.
- In Find What, paste what you copied.
- In Replace With, leave it blank.
- Click Replace All.
- Now on Column A, you have only the File Name.
- On C column, write =HYPERLINK(B1), where B1 is any cell that you need to transform into a clickable link. When you press enter, the formula will transform the Path on column B into a clickable link.
- With the hyperlink cell selected, point the mouse on the bottom right corner until you see a black +. Drag and drop to the cells below in the column C. This will copy the Hyperlink formula to the cells underneath and will make all paths in B column a link on C column. Everything inside the green lines will be a Hyperlink.
If you copy the same formula to the entire column C, every time you add a new path to column B, it will automatically turn into a clickable link in column C.
- It should look like this.
- The last step is to transform the path so instead of pointing to a folder in your computer, it will link into something other people can also access. In the pictures above, the Drive is C:\, but in your case it will be I:\
- Click the letter B to select the entire B column. Click Find & Select, then click Replace again.
- In "Find what" write I:\ (that is a capital letter i, not a letter L).
- In Replace with, paste this: \\fas02\Shared\ then click Replace All.
- Test the links to see if they're working. You can make Column B narrower or hide it, so the sheet doesn't look too crowded.
