Hyperlink Excel Function

The HYPERLINK function of Excel helps create a link (or hyperlink), which when clicked, takes the user to the intended location. Every link is given a desired on-screen name. Once a hyperlink is created, this name is underlined and displayed in blue. Apart from text, a picture can also be turned into a clickable hyperlink.

For example, the formula =HYPERLINK("http://www.wallstreetmojo.com/","WSM") creates a hyperlink named “WSM.” When clicked, this hyperlink opens the website “www.wallstreetmojo.com.”

The purpose of creating a hyperlink in Excel is to provide the end-user with an additional source of information. This source is usually related to the current data of the worksheet. The HYPERLINK function is categorized under the Lookup & Reference functions of Excel.

The formula of the HYPERLINK function of Excel is shown in the following image.

The HYPERLINK function of Excel accepts the following arguments:

• Link_location: This is the path to the intended location. It can be supplied as a text string enclosed within double quotation marks or a reference to a cell that contains this text string. The intended location can be a cell, named range (in any workbook), bookmark or file in Word, web page on the intranet or Internet, file on Excel or PowerPoint, etc.
• Friendly_name: This is the on-screen name of the hyperlink. It is also called the jump text, link text or anchor text. It can be supplied as a text string enclosed within double quotation marks, numeric value or reference to a cell containing the on-screen name. The blue color of the jump text indicates that it is clickable.

The argument “link_location” is required while “friendly_name” is optional. If the latter is omitted, the former is considered as the jump text.

The HYPERLINK formula in excel can return errors in the following situations:

• If the path specified as the “link_location” does not exist, an error is displayed on clicking the hyperlink.
• If there is an issue with the “friendly_name,” the cell shows an error in place of the jump text.

Let us consider a few examples to understand the working of the HYPERLINK function in Excel.

Example #1–Send an Email Using a Hyperlink

To send an email using a hyperlink, follow the listed steps:

1. Open the HYPERLINK function by typing =HYPERLINK in Excel.
2. Prefix “mailto:” (within double quotation marks) to an email address. Use the ampersand operator to join this prefix and the cell containing the email address. This serves as the “link_location” argument.
3. Close the parentheses and press the “Enter” key.

The HYPERLINK formula is displayed in cell C2 of the following image.

Result of the HYPERLINK formula: The prefix along with the entire address (mailto:sumit@e2esols.com) will be displayed as a hyperlink. Since the “friendly_name” argument has been omitted in the HYPERLINK formula, the “link_location” is used as the jump text.

Clicking the created hyperlink will open the “compose” page of the default email account. The given email address will be automatically entered in the “To” field. This field contains the email address of one or more recipients.

Likewise, the HYPERLINK formula has been applied to cells C3 and C4. The outputs of the HYPERLINK formulas are displayed in these cells. Notice that the “friendly_name” has been omitted for the hyperlinks of these cells as well.

Note: “Mailto” links are used in HTML to send emails from the default mail client of the user. The address mentioned in the “mailto” link is considered to belong to the intended recipient.

Example #2–Switch Between the Worksheets Using a Hyperlink

To switch to a different worksheet within the same workbook via a hyperlink, follow the listed steps:

1. Open the HYPERLINK function by typing =HYPERLINK in Excel.
2. Prefix the hash (#), followed by the name of the worksheet, the exclamation mark (!), and the cell reference of the intended location. This serves as the “link_location” argument.
3. Enter the jump text enclosed within double quotation marks.
4. Close the parentheses and press the “Enter” key.

The HYPERLINK formula is displayed in cell C5 of the following image.

Result of the HYPERLINK formula: A hyperlink with jump text “to Example1” will be created. Clicking this hyperlink will take the user to cell A1 of the worksheet “Example1.” In this way, one can move within a workbook through hyperlinks.

Likewise, the hyperlinks of cells C6 and C7 will take the user to the intended locations given in cells B6 and B7 respectively.

Therefore, hyperlinks can be used to link the table of contents of a workbook to the different areas of worksheets.

Alternate Method to Switch Between the Worksheets

This section lists the alternative steps to the preceding method. The steps to switch to a different worksheet (within the same workbook) by using a hyperlink are listed as follows:

1. Type the jump text in a cell. Next, select this cell.
2. Click the “hyperlink” option from the “links” group of the Insert tab.
3. The “insert hyperlink” dialog box opens. Under “link to” displayed on the left side, select “place in this document.”
4. In “text to display,” Excel automatically displays the selected jump text. In “type the cell reference,” enter the cell reference of the intended location.
5. Under “or select a place in this document,” choose the worksheet where the user would be taken.
6. Click “Ok.”

The “insert hyperlink” dialog box is shown in the following image. In this case, clicking the jump text “Sheet Example1,” will take the user to cell A1 of worksheet “Example3.”

Example #3–Open a File Using a Hyperlink

To open a file through a hyperlink, follow the listed steps:

1. Open the HYPERLINK function by typing =HYPERLINK in Excel.
2. Enter the reference to a cell containing the full path of the file. The path should include the location, name, and extension (like .docx, .xlsx, .pptx, and so on) of the file. This serves as the “link_location” argument.
3. Enter the jump text enclosed within double quotation marks.
4. Close the parentheses and press the “Enter” key.

The HYPERLINK formula is displayed in cell C3 of the following image.

Result of the HYPERLINK formula: A hyperlink with jump text “NVP Function” is created. Clicking this hyperlink opens the file titled “NPV function.” This file is currently stored on the desktop of the user.

Likewise, the hyperlink of cell C4 opens an image having the “.jpg” extension. The hyperlink of cell C5 opens a Word document titled “top advanced excel formulas and functions.”

Let us learn how to make a picture or cell of Excel clickable. Note that the procedure of creating hyperlinks in both cases is the same.

The important points related to creating hyperlinks in Excel are stated as follows:

• The “insert hyperlink” dialog box (opens by clicking “hyperlink” on the Insert tab) shows a “screen tip” option on the top-right side. When clicked, it allows the user to enter information related to the hyperlink. This information is displayed when the cursor rests on the hyperlink and changes to a hand icon.
• It is possible to select a cell containing a hyperlink without being taken to the intended location. This can be done by following either of the stated methods:
  • Select an adjacent cell and use the arrow keys to select the cell containing the hyperlink.
  • Click the required cell (containing the hyperlink) and keep the mouse button pressed until the cursor assumes a “plus” or “cross” sign. Once this sign is displayed, release the mouse button.