SharePoint/Excel 2010: Relative Hyperlinks and Local Documents

Relatively linking to local documents is much simpler you’d expect. Just save the Excel file and the documents in the same directory and =HYPERLINK(filename,filename). No need for “file://” or any fancy hyperlinking. This method works anywhere — your local drive, a CD-ROM drive, USB stick, or a networked drive.

If you’d like to add files to a subfolder, the syntax is =HYPERLINK(“foldername\” & Name,Name) I’ve seen a few people around the ‘net who also needed to download documents in SharePoint and have them indexed in Excel. Here’s how you do it:


Go to the Document Library
Download the documents to your local drive using Explorer View.
Once that’s done, select the View and Folder you’d like to export
Ribbon Library Tools -> Library -> Current View, Change to CDR
Don’t click any files or folders -> Export to Excel


Click “Enable” if given a security prompt
Now you’re in Excel
Click Data -> Connections -> Highlight owssvr -> Remove

Rename the workbook to Index
Delete the Path and Item Type Columns

Select Column A -> Right Click -> Remove Hyperlinks

Select Column A2 -> Right Click -> Insert Table Columns to the Left

Name that column FileName
Click the “Name” Column to highlight the whole thing
Click “Formulas” in the Ribbon -> Define Name -> It should say Name, Scope is Workbook, Refers to is autofilled: =Index!$B:$B

Highlight all of the empty cells on the side of the Filenames (A2 on down)
Go to the Function section
Paste: =HYPERLINK(Name,Name)

Save to all columns by clicking CTRL-ENTER

Save the Excel file to the same directory where you initially copied your SharePoint directories.

*BONUS: Highlight the Name column and hide it.

Chrissy is a Cloud and Datacenter Management & Data Platform MVP who has worked in IT for over 20 years. She is the creator of the popular SQL PowerShell module dbatools, holds a master's degree in Systems Engineering and is coauthor of Learn dbatools in a Month of Lunches. Chrissy is certified in SQL Server, Linux, SharePoint and network security. You can follow her on Twitter at @cl.

Posted in SharePoint