30 Excel functions in 30 days: HYPERLINK

Yesterday in the marathon 30 Excel functions in 30 days we did the text replacement using the function SUBSTITUTE (SUBSTITUTE) and made flexible reports with it.

On the 28th day of the marathon, we will study the function HYPERLINK (HYPERLINK). Instead of manually creating hyperlinks using the Excel Ribbon command of the same name, you can use this feature.

So let’s get into the details of the function HYPERLINK (HYPERLINK) and examples of its use. If you have additional information or examples, please share them in the comments.

Function HYPERLINK (HYPERLINK) creates a link that opens a document stored on a computer, network server, local network, or the Internet.

Function HYPERLINK (HYPERLINK) allows you to open documents or navigate to specific locations in a document. With it you can:

  • Create a link that navigates to a specific location in the same file.
  • Create a link to an Excel document in the same folder.
  • Create a link to a website.

Function HYPERLINK (HYPERLINK) has the following syntax:

HYPERLINK(link_location,friendly_name)

ГИПЕРССЫЛКА(адрес;имя)

  • link_location (address) – A string of text that specifies the location of the desired location or document.
  • friendly_name (name) is the text that will be displayed in the cell.

If you are not sure that you can create the correct reference for a function HYPERLINK (HYPERLINK), insert it manually using the command Hyperlink (Hyperlink), which is located on the tab Insert Excel ribbons. This way you will learn the correct syntax, which you then repeat for the argument link_location (address).

Example 1: Referencing a location in the same file

There are several different ways to create a text string for an argument link_location (address). In the first example, the function ADDRESS (ADDRESS) returns the address for the first row and first column in the worksheet whose name is specified in cell B3.

Symbol # (pound sign) at the beginning of the address indicates that the location is in the current file.

=HYPERLINK("#"&ADDRESS(1,1,,,B3),D3)

=ГИПЕРССЫЛКА("#"&АДРЕС(1;1;;;B3);D3)

30 Excel functions in 30 days: HYPERLINK

Also, you can use the operator & (concatenation) to blind the link address. Here the sheet name is in cell B5 and the cell address is in C5.

=HYPERLINK("#"&"'"&B5&"'!"&C5,D5)

=ГИПЕРССЫЛКА("#"&"'"&B5&"'!"&C5;D5)

30 Excel functions in 30 days: HYPERLINK

To refer to a named range in the same Excel workbook, simply provide the range name as an argument link_location (address).

=HYPERLINK("#"&D7,D7)

=ГИПЕРССЫЛКА("#"&D7;D7)

30 Excel functions in 30 days: HYPERLINK

Example 2: Referencing an Excel file in the same folder

To create a link to another Excel file in the same folder, simply use the filename as an argument link_location (address) in function HYPERLINK (HYPERLINK).

To specify the path to a file that is one or more levels higher in the hierarchy, use two periods and a backslash (..) for each level.

=HYPERLINK(C3,D3)

=ГИПЕРССЫЛКА(C3;D3)

30 Excel functions in 30 days: HYPERLINK

Example 3: Linking to a website

Using functions HYPERLINK (HYPERLINK) You can link to pages on websites. In this example, the site link is assembled from text strings, and the site name is used as the argument value. friendly_name (name).

=HYPERLINK("http://www." &B3 & ".com",B3)

=ГИПЕРССЫЛКА("http://www."&B3&".com";B3)

30 Excel functions in 30 days: HYPERLINK

Leave a Reply