How to change relative hyperlink to exact link in Excel

You may have used the HYPERLINK function in Excel before but what you may not have released is your links can be either relative or absolute (or sometimes called exact or specific). What does this even mean? And how do you change from relative to exact?

What Are Absolute and Relative Hyperlinks?

An absolute hyperlink reference, or what I call an exact link to the specific file location, shows the entire path to the referenced file. For example: C:\Documents\User\MyExcelFile.xlsm

A relative hyperlink is a link to another file relative to where the current workbook is saved at. So the above example's relative link would look like this: User\MyExcelFile.xlsm

The advantage of using a relative link is you can change the names of everything in the path and the relative links will still work (useful if you have a document with hundreds of links). In our same example, you could change the C: drive to D:\Documents\User\MyExcelFile.xlsmor you could change the Documents folder to C:\MyDocuments\ and all your links will still work because they're still in the same folder/subfolder relationship.

However, the problem with using all relative links is that is you copy or do a Save As on the original template containing all the links and place it in a different folder location, the relative links will no longer work. This is because they are no longer in the same relative location to the parent folder. If you copied the entire folder structure into the new location it would work but you probably don't want to do that.

How to Change Relative Links to Exact Links

Instead, you could have both types of hyperlinks within your Excel file. Here's how to easily transform relative hyperlinks into absolute links.

First, I wrote a simple VBA macro to get the relative hyperlink address and put them into a helper column.

Sub CreateHyperLinks()

'find the last row in column B

Dim LastRow As Long

LastRow = Cells(Rows.Count, 2).End(xlUp).Row

On Error Resume Next

Dim i As Integer

For i = 3 To LastRow

    Cells(i, 3) = Cells(i, 2).Hyperlinks(1).Address

Next

End Sub

You should only really have to run this macro once, unless the sub folder structure changes.

The next column I used the HYPERLINK function to combine the part location in the yellow squares with the address from the macro generated column. 

=HYPERLINK(C1&C4,B4)

If the parent folder location changes, you just simply update the location in the yellow square and all the links will automatically be updated.

Still confused? I try to explain in this how to video:

 

Does that make sense? I hope this saves you time from having to redo any hyperlinks in your Excel spreadsheets.