Contents
This is a simple but very useful feature that allows you to create a live link to:
- any cell on any sheet in the workbook
- named range
- smart table snippet
- external Excel file on disk or on a server on the network
- web page
In addition, this function can create a completed email form, which helps a lot when sending out standard alerts.
The function syntax is simple:
=HYPERLINK(Address; Text)
- Text is the visible text of the hyperlink in the cell (usually highlighted in blue and underlined like all hyperlinks) that the user clicks on. It can be anything, it doesn’t matter.
- Address – the place where we want to refer (and this is the most interesting).
Let’s look at all the options in more detail.
Link to web page
This is the simplest and most obvious way to use our function HYPERLINK (HYPERLINK). The first argument here will be the full link to the web page (URL):
Please note that the address must be complete, i.e. it is mandatory to start with the protocol (usually “http”). When you click on the generated link, the specified site will open in the default browser.
Link to a cell inside a workbook
If you need a reference to a cell or range that is inside the current workbook, then the address will need to be specified as follows:
Here, the pound sign (#) denotes the current book. Instead of the address of one cell, you can, of course, specify a whole range.
If the sheet name contains spaces, then it must be enclosed in apostrophes – otherwise the link will not work:
Link to a cell in an external workbook
You can easily link to a cell in an external workbook. Then, as the first argument, you will need the full path to the file (with extension!), Sheet name and cell address:
Similar to an internal link, if the sheet name contains spaces, then it must be additionally enclosed in apostrophes.
Link to a file on the server
Network links are also supported if the file is saved, for example, on a corporate server in a shared folder – in this case, a UNC path starting with two reverse fractions is used:
Referencing a named range
If you have created a named range (for example, via Formulas – Name Manager – New), then you can safely use this name in the link:
If you need to refer to a named range in an external file, you will need to specify its name and path to it, as in the previous paragraph. The sheet name is no longer required:
Link to a smart table or its elements
If you are familiar with smart tables, then you know what a useful thing this is. If desired, you can easily create using the function HYPERLINK (HYPERLINK) a link to any fragment of the smart table we need or to it in its entirety. To do this, in the first argument you need to specify the name of the table (for example Table 1) and the code designation of its element:
The following notations are supported:
- Table1[#All] – link to the entire table, including column headers, data and total row
- Table1[#Data] or simply Table 1 – link to data only (without title and totals line)
- Table1[#Headers] – link only to the first row of the table with column headings
- Table1[#Results] – link to the total row (if it is included)
Generating a completed email form
This is a relatively exotic use of the hyperlink function to create a completed e-mail form:
When you click on such a link, we will see this beauty:
You can specify multiple recipients, recipients of ordinary and blind copies, format the text of the letter, etc. It is very convenient for office notifications and mailings. The only thing that cannot be done in this way is to attach attachments. More details about all the possibilities and parameters of this method are described in a separate article.
Using non-standard characters
Sometimes you can combine the HYPERLINK function with the SYMBOL (CHAR) function for outputting non-standard characters, which we have already discussed. This allows you to replace the simple link text with something prettier:
- Creating emails with the HYPERLINK function
- Dynamic hyperlinks using the MATCH and HYPERLINK functions