HYPERLINK function

Description

HYPERLINK function Creates a shortcut or jump that opens a document stored on 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])

Parameters

Link_location Required. The path and file name to the document to be opened.

Friendly_name Optional. The jump text or numeric value that is displayed in the cell. If friendly_name is omitted, the cell displays the link_location as the jump text. If friendly_name returns an error value (for example, #VALUE!), the cell displays the error instead of the jump text.

Remarks

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, then release the mouse button.

Examples

Jump to a web site

=HYPERLINK("http://www.excelhowto.com","Visit www.excelhowto.com")

Jump to a specific folder

=HYPERLINK("C:\My Documents","Open My Documents")

Jump to a specific file

=HYPERLINK("C:\My Documents\Price List.xlsx","Open Price List")

If the specific file in the same folder with current workbook, you can use below formula:

=HYPERLINK("Price List.xlsx","Open Price List")

Jump to a specific location in the current worksheet

=HYPERLINK("[HYPERLINK function.xlsx]A5","Jump to A5")

If current workbook name is omitted, it must replace with #.

=Hyperlink("#A5","Jump to A5")

Jump to a different cell in the same workbook

=Hyperlink("#sheet2!A5","Jump to Sheet2 A5")
=Hyperlink("#sheet2!A5:B10","Jump and select Sheet2 A5:B10")

Jump to a different workbook

In the same folder:

=Hyperlink("Price List.xlsx#sheet2!A5","Jump to [Price List] Sheet2 A5")

In the different folder:

=Hyperlink("C:\My Documents\Price List.xlsx#sheet2!A5","Jump to [Price List] Sheet2 A5")

R1C1 reference style example

=HYPERLINK("#Sheet2!R2C3","Jump to Sheet2 C2")
=HYPERLINK("#R2C3","Jump to C2")
=HYPERLINK("#sheet2!r1c1:r5c5","Jump and select Sheet2 A1:E5")

Usually, We use MATCH function, LOOKUP function to find the specific cell or range, and use R1C1 reference style of HYPERLINK function to jump to the specific cell or range:

=HYPERLINK("#Sheet2!R"&MATCH(B2,Sheet2!A:A,)&"C1","Jump to Sheet2 "&B2)

Video training

Leave a Reply

Your email address will not be published. Required fields are marked *