Excel examples HYPERLINK function to create dynamic hyperlinks

HYPERLINK function in Excel returns a shortcut or hyperlink to a specific object, which can be a web page, a file saved in the PC's permanent memory, a group of cells on a sheet in an Excel workbook.



HYPERLINK function and features of its arguments

HYPERLINK feature simplifies access to objects that are both parts of Excel (cells, sheets of a book), as well as parts of other software products (notepad, Word file) or pages on the Internet. This function has the following syntax entry:

=HYPERLINK(Link_location,[Friendly_name])

Description of 2 parameters of the function arguments:

  • Link_location is a text value corresponding to the name of the object being opened and the full path to access it. This parameter is required. The address may indicate a specific part of the document, for example, a cell or a range of cells, a bookmark in a text editor Word. The path may contain data about the path to the file in the PC file system (for example, “C:\Users\soulp\Documents”) or the URL address to a page on the Internet.
  • [Friendly_name] is the text value that will be displayed as the text of the hyperlink. Displayed in blue with underlined text.

Notes:

  1. The address and [Friendly_name] parameters take values as a text string in quotes (“address”, “name”) or links to cells containing the address and object name, respectively.
  2. In Excel Online (an online version of the Excel program for working through the web interface), the HYPERLINK function can only be used to create hyperlinks to web objects, since browser applications do not have access to device file systems.
  3. If in the cell to which the [name] parameter refers, the value of the error code #VALUE !, was set, the text of the created hyperlink will also display "#VALUE!".
  4. To select a cell containing a hyperlink, without navigating through it, you must hover the mouse over the desired cell, press and do not release the left mouse button until the cursor changes its shape to “+”. Then you can release the mouse button, the desired cell will be highlighted.
  5. In the online version of Excel, to highlight the cell containing the hyperlink, you must move the cursor so that it looks like a normal arrow. To navigate through the hyperlink, you must hover the cursor directly on its text. In this case, the cursor will look like a hand.
  6. To create a hyperlink to an Internet resource, you can use the following entry: =HYPERLINK(“http://www.bing.com/”,”BING Search Engine”).
  7. A hyperlink to a document stored in the PC file system can be created as follows: =HYPERLINK(“C:\Users\soulp\Downloads\document_2”,”Link to document_2”). When you click on this link, a dialog box appears with a warning about the possible presence of malware in the file. To open the file, you must click the "OK" button in this dialog box. If there is no file in the specified path, a corresponding notification will appear.
  8. To create a link to another sheet in the Excel workbook, you can use a similar entry: =HYPERLINK(“[Book1.xlsx]Sheet2!A1”,”Sheet2”). When you click on this link, Sheet2 will be opened, and the focus will be set on cell A1.
  9. The hyperlink can be inserted using the visual user interface (the corresponding context menu item, the button on the taskbar).


Examples of using HYPERLINK function in Excel

How to create a link to a file in Excel? Example 1. An enterprise accountant performs various calculations and stores data tables in Excel in one book (Accounting.xlsx) containing multiple sheets. For convenience, it was decided to create a separate sheet with a table of contents in the form of hyperlinks to each of the available sheets.

On the new sheet, create the following table:

Example 1.

To create a hyperlink we use the formula:

HYPERLINK.

Description of the function arguments:

  • "[Example_1.xlsx] Profit! A1" is the full address of cell A1 of the "Profit" sheet of the book "Example_1.xlsx";
  • "Profit" - the text that will display the link.

Similarly, create hyperlinks for other pages. As a result, we get:

create hyperlinks.

Dynamic HYPERLINK to Excel

Example 2. The Excel spreadsheet contains data on the rates of certain currencies, which are used to perform various financial calculations. Since exchange rates are dynamically changing values, the accountant decided to place a hyperlink to a web page that provides relevant data.

Source table:

Example 2.

To create a link to the resource https://www.xe.com/currencyconverter/, in cell D7, enter the following formula:

Description of parameters:

  • https://www.xe.com/currencyconverter/ - URL address of the required site;
  • "Exchange Rates" - the text displayed in the link.

As a result, we get:

URL address.

Note: the specified web page will be opened in the browser used in the system by default.

Sending emails via Excel HYPERLINK

Example 3. An enterprise employee has difficulty using the IF function in Excel. To solve a problem in one of the documents, it has a ready-made form for sending an email. Sending a letter occurs by clicking on the hyperlink. Consider how this form of sending letters is arranged.

The form is as follows:

Example 3.

The values of cells B3 and B4 can be changed at the discretion of the user (depending on the reason for contacting the support service). Cell B5 contains the function:

CONCATENATE.

CONCATENATE – this function performs concatenation (concatenation of text strings taken as parameters).

Description of parameters:

  • "mailto:" - command to send letters;
  • B2 - cell containing email support services;
  • "?subject=" - command to write the subject of the letter;
  • ENCODEURL(B3) - a function that converts the text of a letter subject to a URL encoding;
  • "& body =" - the command to write the text of the letter;
  • ENCODEURL(B4) - the text of the letter in the encoding URL.

Cell B6 has the following function:

following.

Description of parameters:

  • B5 - URL command to send a letter containing the subject and text of the letter;
  • "Send Letter" is the name of the hyperlink.

Download examples HYPERLINK function to create in Excel

Clicking on the link will open the default mail client, for example, Outlook (but in this case, the standard Windows client).


en ru