How to use Excel HYPERLINK function to create different link types

Hyperlink to another worksheet

To insert a hyperlink to a different sheet in the same workbook, supply the target sheet name preceded by a pound sign (#), and followed by the exclamation point and target cell reference, like this:

=HYPERLINK("#Sheet2!A1", "Sheet2")

The above formula creates a hyperlink with the jump text "Sheet2" that opens Sheet2 in the current workbook.

If the worksheet name includes spaces or non-alphabetical characters, it must be enclosed in single quotation marks, like this:

=HYPERLINK("#'Price list'!A1", "Price list")

In the same way, you can make a hyperlink to another cell in the same sheet. For example, to insert a hyperlink that will take you to cell A1 in the same worksheet, use a formula similar to this:

=HYPERLINK("#A1", "Go to cell A1")

Hyperlink to a different workbook

To create a hyperlink to another workbook, you need to specify the full path to the target workbook in the following format:

"Drive:\Folder\Workbook.xlsx"

For example:

=HYPERLINK("D:\Source data\Book3.xlsx", "Book3")

To land on a specific sheet and even in a specific cell, use this format:

"[Drive:\Folder\Workbook.xlsx]Sheet!Cell"

For example, to add a hyperlink titled "Book3" that opens Sheet2 in Book3 stored in the Source data folder on drive D, use this formula:

=HYPERLINK("[D:\Source data\Book3.xlsx]Sheet2!A1", "Book3")

If you plan to move your workbooks to another location soon, you can create a relative link like this:

=HYPERLINK("Source data\Book3.xlsx", "Book3")

When you move the files, the relative hyperlink will continue working as long as the relative path to the target workbook remains unchanged. For more information, please see Absolute and relative hyperlinks in Excel.

Hyperlink to a named range

If you are making a hyperlink to a worksheet-level name, include the full path to the target name:

"[Drive:\Folder\Workbook.xlsx]Sheet!Name"

For instance, to insert a link to a range named "Source_data" stored on Sheet1 in Book1, use this formula:

=HYPERLINK("[D:\Excel files\Book1.xlsx]Sheet1!Source_data","Source data")

If you are referencing a workbook-level name, the sheet name does not need to be included, for example:

=HYPERLINK("[D:\Excel files\Book1.xlsx]Source_data","Source data")

Hyperlink to open a file stored on a hard disk drive

To create a link that will open another document, specify the full path to that document in this format:

"Drive:\Folder\File_name.extension"

For example, to open the Word document named Price list that is stored in the Word files folder on drive D, you use the following formula:

=HYPERLINK("D:\Word files\Price list.docx","Price list")

Hyperlink to a bookmark in a Word document

To make a hyperlink to a specific location in a Word document, enclose the document path in [square brackets] and use a bookmark to define the location you want to navigate to.

For example, the following formula adds a hyperlink to the bookmark named Subscription_prices in Price list.docx:

=HYPERLINK("[D:\Word files\Price list.docx]Subscription_prices","Price list")

Hyperlink to a file on a network drive

To open a file stored in your local network, supply the path to that file in the Universal Naming Convention format (UNC) that uses double backslashes to precede the name of the server, like this:

"\\Server_name\Folder\File_name.extension"

The below formula creates a hyperlink titled "Price list" that will open the Price list.xlsx workbook stored on SERVER1 in Svetlana folder:

=HYPERLINK("\\SERVER1\Svetlana\Price list.xlsx", "Price list")

To open an Excel file at a specific worksheet, enclose the path to the file in [square brackets] and include the sheet name followed by the exclamation point (!) and the referenced cell:

=HYPERLINK("[\\SERVER1\Svetlana\Price list.xlsx]Sheet4!A1", "Price list")

Hyperlink to a web page

To create a hyperlink to a web-page on the Internet or intranet, supply its URL enclosed in quotation marks, like this:

=HYPERLINK("https://www.ablebits.com","Go to Ablebits.com")

The above formula inserts a hyperlink, titled "Go to Ablebits.com", that opens the home page of our web-site.

Hyperlink to send an email

To create a new message to a specific recipient, provide an email address in this format:

"mailto:email_address"

For instance:

=HYPERLINK("mailto:support@ablebits.com","Drop us an email")

The above formula adds a hyperlink titled "Drop us an email", and clicking the link creates a new message to our support team.

How this formula works

Those of you who are familiar with the INDEX MATCH formula as a more versatile alternative to Excel VLOOKUP, have probably already figured out the overall logic.

At the core, you use the classic INDEX MATCH combination to locate the first occurrence of the lookup value in the lookup range:

You can find full details on how this formula works by following the above link. Below, we will outline the key points:

• The MATCH function determines the position of "Adam" (lookup value) in range A2:A10 (lookup range), and returns 3.
• The result of MATCH is passed to the row_num argument of the INDEX function instructing it to return the value from the 3^rd row in range C2:C10 (return range). And the INDEX function returns "Lemons".

This way, you get the friendly_name argument of your Hyperlink formula.

Now, let's work out link_location, i.e. the cell the hyperlink should point to. To get the cell address, you use the CELL("address", [reference]) function with INDEX MATCH as reference. For the HYPERLINK function to know that the target cell resides in the current sheet, concatenate the cell address with the pound character ("#").