This tutorial explains the basics of the Excel HYPERLINK function and provides a few tips and formula examples to use it most efficiently.
There are many ways to create a hyperlink in Excel. To link to a certain web page, you can simply type its URL in a cell, hit Enter, and Microsoft Excel will automatically convert the entry into a clickable hyperlink. To link to another worksheet or a specific location in another Excel file, you can use the Hyperlink context menu or Ctrl + K shortcut. If you plan to insert many identical or similar links, the fastest way is to use a Hyperlink formula, which makes it easier to create, copy and edit hyperlinks in Excel.
The HYPERLINK function in Excel is used to create a reference (shortcut) that directs the user to the specified location in the same document or opens another document or web-page. By using a Hyperlink formula, you can link to the following items:
The function is available in all versions of Excel 365 - 2000. In Excel Online, the HYPERLINK function can only be used for web addresses (URLs).
The syntax of the HYPERLINK function is as follows:
Link_location can be supplied as a reference to a cell containing the link or a text string enclosed in quotation marks that contains a path to a file stored on a local drive, UNC path on a server, or URL on the Internet or intranet.
If the specified link path does not exist or is broken, a Hyperlink formula will throw an error when you click the cell.
Friendly_name can be supplied as a numeric value, text string enclosed in quotation marks, name, or reference to a cell that contains the link text.
Clicking a cell with a Hyperlink formula opens the file or web-page specified in the link_location argument.
Below, you can see the simplest example of an Excel Hyperlink formula, where A2 contains friendly_name and B2 contains link_location:
The result may look something similar to this:
More formula examples demonstrating other uses of the Excel HYPERLINK function follow below.
Moving from theory to practice, let's see how you can use the HYPERLINK function to open various documents directly from your worksheets. We will also discuss a more complex formula where Excel HYPERLINK is used in a combination with a few other functions to accomplish a non-trivial challenging task.
The Excel HYPERLINK function enables you to insert clickable hyperlinks of a few different types depending on what value you supply to the link_location argument.
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:
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")
To create a hyperlink to another workbook, you need to specify the full path to the target workbook in the following format:
=HYPERLINK("D:\Source data\Book3.xlsx", "Book3")
To land on a specific sheet and even in a specific cell, use this format:
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.
If you are making a hyperlink to a worksheet-level name, include the full path to the target 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")
To create a link that will open another document, specify the full path to that document in this format:
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")
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")
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:
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")
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.
To create a new message to a specific recipient, provide an email address in this format:
=HYPERLINK("mailto:email@example.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.
When working with large datasets, you may often find yourself in a situation when you need to look up a specific value and return the corresponding data from another column. For this, you use either the VLOOKUP function or a more powerful INDEX MATCH combination.
But what if you not only want to pull a matching value but also jump to the position of that value in the source dataset to have a look at other details in the same row? This can be done by using the Excel HYPERLINK function with some help from CELL, INDEX and MATCH.
The generic formula to make a hyperlink to the first match is as follows:
To see the above formula in action, consider the following example. Supposing, you have a list of vendors in column A, and the sold products in column C. You aim to pull the first product sold by a given vendor and make a hyperlink to some cell in that row so you can review all other details associated with that particular order.
With the lookup value in cell E2, vendor list (lookup range) in A2:A10, and product list (return range) in C2:C10, the formula takes the following shape:
=HYPERLINK("#"&CELL("address", INDEX($C$2:$C$10, MATCH($E2,$A$2:$A$10,0))), INDEX($C$2:$C$10, MATCH($E2,$A$2:$A$10,0)))
As shown in the screenshot below, the formula pulls the matching value and converts it into a clickable hyperlink that directs the user to the position of the first match in the original dataset.
If you are working with long rows of data, it might be more convenient to have the hyperlink point to the first cell in the row where the match is found. For this, you simply set the return range in the first INDEX MATCH combination to column A ($A$2:$A$10 in this example):
=HYPERLINK("#"&CELL("address", INDEX($A$2:$A$10, MATCH($E2,$A$2:$A$10,0))), INDEX($C$2:$C$10, MATCH($E2,$A$2:$A$10,0)))
This formula will take you to the first occurrence of the lookup value ("Adam") in the dataset:
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:
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 ("#").
Note. Please notice the use of absolute cell references to fix the lookup and return ranges. This is critical if you plan to insert more than one hyperlink by copying the formula.
As mentioned in the beginning of this tutorial, one of the most useful benefits of formula-driven hyperlinks is the ability to edit multiple Hyperlink formulas in one go by using Excel's Replace All feature.
Let's say you want to replace the old URL of your company (old-website.com) with the new one (new-website.com) in all hyperlinks on the current sheet or in the entire workbook. To have it done, please follow the steps outlined below:
In a similar fashion, you can edit the link text (friendly_name) in all Hyperlink formulas at the same time. When doing so, be sure to check that the text to be replaced in friendly_name does not appear anywhere in link_location so that you won't break the formulas.
The most common reason for a Hyperlink formula not working (and the first thing for you to check!) is a non-existent or broken path in the link_location argument. If it's not the case, check out the following two things:
Typically, such errors occur when friendly_name is returned by some other function(s), like in our Vlookup and hyperlink to the first match example. In this case, the #N/A error will show up in the formula cell if the lookup value is not found within the lookup table. To prevent such errors, you may consider using the IFERROR function to display an empty string or some user-friendly text instead of the error value.
This is how you create hyperlinks using the Excel HYPERLINK function. I thank you for reading and hope to see you on our blog next week!
Excel Hyperlink formula examples (.xlsx file)
Table of contents