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
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
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.