HYPERLINK Excel Function

What Is HYPERLINK Function In Excel?

The HYPERLINK function in Excel is an inbuilt Lookup & Reference function. It creates a shortcut (hyperlink) with a user-friendly name, which, if clicked, will take us to the specified location. And this location can be in the current or different workbook, an address of a file, or a webpage.

Users can use the HYPERLINK Excel function to include links to data present at other addresses, making it accessible from the active spreadsheet.

For example, the following table contains websites and their addresses.

Suppose the requirement is to provide shortcuts in cell range C2:C4 for users to reach the target addresses directly from the active worksheet. Then, considering the above mentioned HYPERLINK Excel function meaning, we can apply the HYPERLINK() in the target cells and get the required output.

Hyperlink Excel Function Sample Example-formula

In the above HYPERLINK Excel function example, the function in each target cell accepts the given destination location URL and the display name as inputs. And it returns the hyperlink to the corresponding target location with the specified link name.

For example, cell A2 contains the web address of the website www.excelmojo.com and cell B2 shows the name to display the hyperlink, ExcelMojo. And thus, the HYPERLINK Excel function in cell C3 returns the hyperlink, which the user can click to go to the specified web address.

The HYPERLINK Excel function generates a hyperlink to jump to the specified location from the current worksheet. The given address could be a webpage URL or a path to a file in a drive or local server.
Users can utilize the HYPERLINK() to create a shortcut to access data available at a different location from the active worksheet.
The HYPERLINK function accepts one mandatory argument, link_location, and an optional argument, friendly_name, as input.
When used with other Excel functions, such as INDEX and MATCH, the HYPERLINK function gives excellent results.

HYPERLINK() Excel Formula

The HYPERLINK Excel function syntax is

where,

link_location: The address of a webpage, another worksheet in the current workbook, another workbook, or a path to a file on a server you need to access. This argument can be a cell or a named range in a worksheet or a bookmark in a document. Otherwise, it can be a UNC or URL path.
friendly_name: The jump text with which we require to display the hyperlink.

While the first argument in the HYPERLINK Excel function syntax is mandatory, the second argument is optional.

Further, following the below points will ensure to avoid the possibility of any HYPERLINK Excel function error while using it.

If we provide the link_location argument value as a text string, it should be in double quotes. Otherwise, we can supply the argument as a cell reference to the specific text string.
If the address or path supplied as the link_location argument value is invalid or does not exist, clicking the hyperlink will show an error message.
The friendly_name argument value (jump text) can be a text string, value, or a cell reference to the jump text.
If we ignore the second argument, the HYPERLINK Excel function return value will be the hyperlink with the link_location as its display name.
If the friendly_name argument results in an error, the HYPERLINK() output will be the error value instead of the hyperlink jump text.
If we require to select a cell containing the HYPERLINK Excel function return value but do not wish to click the hyperlink, use the arrow keys for selecting the cell.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

How To Use HYPERLINK Excel Function?

The steps to apply the HYPERLINK Excel Function are as follows:

First, confirm if the given target address or file path is valid and if the required jump text is correct.
Next, select the target cell where we require to display the hyperlink and enter the HYPERLINK Excel Function.
Finally, press Enter to view the hyperlink.

The following example explains the above steps in detail to avoid any HYPERLINK Excel function error occurring when applying the function.

The table below shows the target locations and the jump texts to generate and display the required hyperlinks.

And suppose we require the hyperlinks in cell range C2:C3. Then, here is how we can apply the HYPERLINK() in the target cells and create the links.

Step 1: Select the target cell C2, enter the HYPERLINK(), and press Enter.

=HYPERLINK(A2,B2)

Hyperlink Excel Function Example-use-step 1

Directly supplying the two arguments as values in the HYPERLINK() will give the same result.

=HYPERLINK(“https://www.office.com/”,”Microsoft 365: Login”)

Hyperlink Excel Function Example-use-step 2

Otherwise, we can apply the function from the Formulas tab by selecting the target cell and then going to Formulas – Lookup & Reference – HYPERLINK. This action will open the Function Arguments window.

Next, fill the two fields with the required cell references or values.

Hyperlink Excel Function Example-use-FA-values

Finally, click OK to view the HYPERLINK() output in cell C2.

Step 2: Copy the formula in cell C3 using the fill handle in excel.

Hyperlink Excel Function Example-use-FA-step 2

In row 2, the target location is a web address. And hence, clicking the generated hyperlink in cell C2 will take us to the Microsoft 365: Login page.

On the other hand, the target location specified in row 3 is the address of another worksheet, HYPERLINK_Example, in the same workbook. And the address format includes a ‘#’, the worksheet name, ‘!’ symbol, followed by the target cell reference.

Also, suppose the worksheet name has spaces or non-alphabetical characters. Then, enclose the worksheet name within single quotations when providing the target location.

And thus, if we click the hyperlink in cell C3, it will direct us to the corresponding worksheet, with the specified cell A1 selected.

Examples

Below are a few illustrations to help us grasp the HYPERLINK Excel function meaning effectively.

Example #1

This example shows how to use the HYPERLINK Excel function to create a hyperlink to another workbook.

Suppose the below table contains the location of another workbook, cell, and a named range in a worksheet of the specified workbook.

The requirement is to create shortcuts in the current worksheet to jump to the workbook, Sample_Workbook, the given cell, and the named range in Sheet6 of Sample_Workbook. And we require the hyperlinks with the specified jump texts in cell range C2:C4.

Then, by applying the HYPERLINK Excel function in the target cells, we will get the required links.

Step 1: Select cell C2, enter the below formula, and press Enter.

=HYPERLINK(A2,B2)

Hyperlink Excel Function Example 1-Step 1

Clicking the hyperlink in cell C2 will take us to the Excel file, Sample_Workbook, in the Excel Mojo folder in D drive.

Step 2: Using the fill handle, apply the formula in cell C3:C4.

Hyperlink Excel Function Example 1-Step 2

In rows 3 and 4, the path to the workbook should be in bar brackets, with the worksheet followed by an exclamation mark.

Once we click the cell C3 hyperlink, it will direct us to cell A1 in Sheet6 of Sample_Workbook. And the cell C4 hyperlink will help jump to the named cell range, Sample_Named_Range, in Sheet6 of Sample_Workbook.

Alternatively, we can also enter the formulas as shown below.

=HYPERLINK(“D:\Excel Mojo\Sample_Workbook.xlsx”,”Sample Workbook”)

And

=HYPERLINK(“[D:\Excel Mojo\Sample_Workbook.xlsx]Sheet6!A1″,”Sample Workbook_Sheet 6_Cell A1”)

And

=HYPERLINK(“[D:\Excel Mojo\Sample_Workbook.xlsx]Sheet6!Sample_Named_Range”,”Sample Workbook_Sheet 6_Sample Named Range”)

Example #2

This HYPERLINK Excel function example explains how to generate hyperlinks to word files.

Suppose the table below contains address paths to a word file, a bookmark in the word file, and a document on a local server.

The requirement is to create hyperlinks in cell range C2:C4 to jump to the respective locations.

Then, here is how we can apply the HYPERLINK Excel function in the target cells and achieve the required hyperlinks.

Step 1: To begin with, select cell C2, enter the HYPERLINK() provided in the Formula Bar, and then, press Enter.

Step 2: Next, copy the HYPERLINK Excel function formula in cell range C3:C4 by dragging the fill handle downwards.

The cell C2 hyperlink will take us to the word file, Sample Word File, in the MS Word files folder in D drive.

And in row 3, the path to the word file should be in bar brackets, as the shortcut is for jumping to the bookmark, Sample_Bookmark, in the Sample Word File.docx.

However, as the word file, Sample Word File, specified in row 4, is on a local server, we must follow the UNC format. And hence, we include the double backslashes before the server name in cell A4.

So, clicking the cell C4 hyperlink will open the specified word file in the ExcelMojo folder on a local server, SERVER1.

Example #3

The below illustration shows how to use the HYPERLINK Excel function with Excel functions such as INDEX and MATCH.

The first table contains the order delivery details of five dealers.

Suppose the requirement is to determine the first item ordered with Dealers 4 and 5 and create the links in cell range B15:B16 to the looked-up items in the first table. Then, applying the HYPERLINK Excel function in the target cells can fetch us the desired outcome.

Step 1: Select the target cell B15, enter the below formula, and press Enter.

=HYPERLINK(“#”&CELL(“address”,INDEX($B$2:$B$11,MATCH($A15,$A$2:$A$11,0))),INDEX($B$2:$B$11,MATCH($A15,$A$2:$A$11,0)))

Step 2: Apply the formula in cell B16 with the help of the fill handle option.

Let us consider the cell B16 expression to see how the formula works.

The first MATCH() checks for Dealer_5 (cell A16 value) in the cell range A2:A11. It finds the value in row 6. Then the INDEX() returns the cell reference at the intersection of row 6 and cell range B2:B11, $B$7, as it is the reference argument in the CELL excel function.

Next, the CELL() returns the determined cell address “$B$7”. And the ‘&’ symbol concatenates the cell address with the ‘#’ to show the target location (link_location) to the HYPERLINK(), “#$B$7”.

The second MATCH and INDEX functions work as explained previously, but the INDEX Excel function returns the cell B7 value, “Table_Grade3”. And finally, the HYPERLINK() creates a link to the cell address $B$7, with the jump text, Table_Grade3.

And if we click the cell A16 hyperlink, it will take us to cell B7 in the first table.

Important Things To Note

Ensure to enclose the text string in double quotes if supplying it directly as the link_location argument value in the HYPERLINK Excel function.
If we omit the friendly_name argument, the HYPERLINK() will generate a hyperlink with the link_location as the jump text.
Suppose the provided link_location argument value is invalid, and we click the generated hyperlink. Then, we will see an error message saying the reference is invalid.
For a friendly_name argument resulting in an error value, the HYPERLINK() shows the error value in place of the hyperlink.

Frequently Asked Questions (FAQs)

1. Can you create a hyperlink to send an email from the active worksheet?

We can create a hyperlink to send an email from the active worksheet using the HYPERLINK function.

Let us see the steps using an example.

The table below contains email addresses.

Suppose if we must create hyperlinks to send emails to the specified email addresses with the given jump texts and display the links in cell range C2:C3. Then, here is how applying the HYPERLINK Excel function in the target cells will get us the required outcome.

However, when using the HYPERLINK() to generate a link to send an email, we must prefix the email address with the term mailto: while supplying the link_location argument.

Step 1: Select the target cell C2, enter the below formula, and press Enter.

=HYPERLINK(“mailto:”&A2,B2)

Step 2: Use the fill handle to apply the formula in cell C3.

Clicking the cells C2 and C3 hyperlinks will direct us to the application, from where we can send a new message to the respective email addresses.

We can also enter the formulas as shown below.

=HYPERLINK(“mailto:customer.support@abb.com”,”Email Customer Support Team”)

And

=HYPERLINK(“mailto:james.allen@abb.com; kevin.ford@abb.com”,”Customer Support Team”)

2. How to generate a hyperlink to another cell in the active worksheet?

We can generate a hyperlink to another cell in the active worksheet using the HYPERLINK function.

For example, the below table shows the target cell is cell A11.

Suppose the requirement is to create and display a hyperlink in cell C2 to jump to cell A11 in the same sheet. Then, here is how we can use the HYPERLINK() in the target cell and generate the required link.

While applying the HYPERLINK(), ensure to prefix the cell reference with a ‘#’ while supplying it as the link_location argument value.

Step 1: Select the target cell C2, enter the below formula, and press Enter.

=HYPERLINK(“#A11”,B2)

3. Can you replace an existing website address with a new address in a hyperlink?

We can replace an existing website address with a new address in a hyperlink using the Replace option under the Find & Select feature in the Home tab.

We can proceed by pressing the shortcut keys Ctrl + H to open the Replace tab in the Find and Replace window.

Next, click Options >>.

We can enter the old, and new addresses in the Find what and Replace with fields, respectively. And finally, click Replace All to view the current site address replaced with the required new one.

Download Template

This article must be helpful to understand the HYPERLINK Excel Function, with its formula and examples. You can download the template here to use it instantly.

Hyperlink Excel Function Download

HYPERLINK Excel Function