HYPERLINK function

Creates a shortcut or jump that opens a document stored on a network server, an intranet (intranet: A network within an organization that uses Internet technologies (such as the HTTP or FTP protocol). By using hyperlinks, you can explore objects, documents, pages, and other destinations on the intranet.), or the Internet. When you click the cell that contains the HYPERLINK function, Microsoft Excel opens the file that is stored at link_location.

Syntax

HYPERLINK(link_location,friendly_name)

The HYPERLINK function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Link_location can be a text string enclosed in quotation marks or a reference to a cell that contains the link as a text string.

If the jump specified in link_location does not exist or cannot be navigated, an error appears when you click the cell.

  • Friendly_name    This is the jump text or numeric value that is displayed in the cell. Friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location as the jump text.

Friendly_name can be a value, a text string, a name, or a cell that contains the jump text or value.

If friendly_name returns an error value (for example, #VALUE!), the cell displays the error instead of the jump text.

Remark

To select a cell that contains a hyperlink without jumping to the hyperlink destination, click the cell and hold the mouse button until the pointer becomes a cross Excel selection cursor, then release the mouse button.

Examples

 Important   

  • The following examples are fictional. The files and Internet locations that are used in the examples do not exist.
  • After entering a HYPERLINK formula that links to a location in another worksheet or workbook, save the workbook before you test the hyperlink to avoid getting an error message.

Jump to a workbook and display specific jump text

This example opens the workbook Budget Report.xlsx that is stored on the Internet at http://example.microsoft.com/report, and displays the text Click for report.

=HYPERLINK("http://example.microsoft.com/report/budget report.xlsx", "Click for report")

Jump to a specific cell on a worksheet

This example creates a hyperlink to cell F10 on the Annual worksheet in the workbook Budget Report.xlsx, which is stored on the Internet at http://example.microsoft.com/report. The cell on the worksheet that contains the hyperlink displays the contents of cell D1 as the jump text.

=HYPERLINK("[http://example.microsoft.com/report/budget report.xlsx]Annual!F10", D1)

Jump to a specific range on a worksheet from specific jump text

This example creates a hyperlink to the range named DeptTotal on the First Quarter worksheet in the workbook Budget Report.xlsx, which is stored on the Internet at http://example.microsoft.com/report. The cell on the worksheet that contains the hyperlink displays the text Click to see First Quarter Department Total.

=HYPERLINK("[http://example.microsoft.com/report/budget report.xlsx]'First Quarter'!DeptTotal", "Click to see First Quarter Department Total")

 Note   For sheet names that include spaces, use single quotation marks around the sheet name to avoid getting an error message that notifies you that Excel cannot open the specified file.

Jump to a specific location in a Word document

To create a hyperlink to a specific location in a Microsoft Word document, you must use a bookmark to define the location you want to jump to in the document. This example creates a hyperlink to the bookmark QrtlyProfits in the document Annual Report.doc located at http://example.microsoft.com.

=HYPERLINK("[http://example.microsoft.com/Annual Report.docx]QrtlyProfits", "Quarterly Profit Report")

Jump to a workbook on a network server

The following example displays the contents of cell D5 as the jump text in the cell and opens the workbook 1stqtr.xlsx, which is stored on the FINANCE server in the Statements share. This example uses a UNC path.

=HYPERLINK("\\FINANCE\Statements\1stqtr.xlsx", D5)

Jump to a workbook on a different drive

This example opens the workbook 1stqtr.xlsx that is stored in the Finance directory on drive D, and displays the numeric value that is stored in cell H10.

=HYPERLINK("D:\FINANCE\1stqtr.xlsx", H10)

Jump to a specific area in an external workbook

The following example creates a hyperlink to the Totals area in another (external) workbook, Mybook.xlsx.

=HYPERLINK("[C:\My Documents\Mybook.xlsx]Totals")

Jump to a different cell in the same workbook

You can create hyperlinks in a worksheet to jump from one cell to another cell in the same workbook. For example, in the workbook Budget.xlsx, the following formula creates a hyperlink to cell E56 in the active worksheet. The link text itself is the value in cell E56.

=HYPERLINK("[Budget.xlsx]E56", E56)

To jump to a different sheet in the same workbook, include the name of the sheet, followed by an exclamation point (!), in the link. In the previous example, to create a link to cell E56 on the September sheet, include September! in the link.

=HYPERLINK("[Budget.xlsx]September!E56", E56)

Use another cell on the worksheet for the link target

To quickly update all formulas in a worksheet that use a HYPERLINK function with the same arguments, you can place the link target in another cell on the same or another worksheet, and then use an absolute reference to that cell as the link_location in the HYPERLINK formulas. Changes that you make to the link target are immediately reflected in the HYPERLINK formulas.

=HYPERLINK($Z$1)

In cell Z1, you then enter the path to the link target.

 
 
Applies to:
Excel 2007