Yesterday, in the 30XL30D challenge, we replaced text with the SUBSTITUTE function, and used it to create flexible reports.
HYPERLINK Function
For day 28 in the challenge, we’ll examine the HYPERLINK function. Instead of manually creating hyperlinks, with the command on the Excel Ribbon, you can use this function.
So, let’s take a look at the HYPERLINK information and examples, and if you have other tips or examples, please share them in the comments.
Function 28: HYPERLINK
The HYPERLINK function creates a shortcut that opens a document stored on a computer, network server, intranet, or Internet.
How Could You Use HYPERLINK?
The HYPERLINK function can open a document, or jump to a specific location, so you can:
- Link to location in same file
- Link to Excel file in same folder
- Link to website
HYPERLINK Syntax
The HYPERLINK function has the following syntax:
- HYPERLINK(link_location,friendly_name)
- link_location is the text string for the location where you want to go.
- friendly_name is the text you want displayed in the cell
HYPERLINK Traps
If you have trouble creating the correct location string for the HYPERLINK function, manually insert a link with the Hyperlink command on Excel’s Ribbon.
That should show you the correct syntax, then recreate that in your link_location argument.
Example 1: Link to location in same file
There are several different ways to create the text string for the link_location argument.
In the first example, the ADDRESS function returns the address for row 1, column 1, on the sheet that is named in cell B3.
The pound sign (#) at the start of the address indicates that the location is within the current file.
=HYPERLINK(“#”&ADDRESS(1,1,,,B3),D3)
You could also use the & operator to construct the link location. Here, the sheet name is in cell B5 and the cell is in C5.
=HYPERLINK(“#”&”‘” & B5 & “‘!” & C5,D5)
For a link to a named range in the same workbook, just use the range name as the link location.
=HYPERLINK(“#”&D7,D7)
Example 2: Link to Excel file in same folder
To create a link to another Excel file, in the same folder, just use the file name as the link_location argument for the HYPERLINK function.
For files that are up a level or more in the hierarchy, use two periods and a backslash for each level.
=HYPERLINK(C3,D3)
Example 3: Link to a website
You can also link to website pages with the HYPERLINK function. In this example, the URL is constructed from text strings, and the website name is used as the friendly name.
=HYPERLINK(“http://www.” &B3 & “.com”,B3)
Download the HYPERLINK Function File
To see the formulas used in today’s examples, you can download the HYPERLINK function sample workbook. The file is zipped, and is in Excel 2007 file format.
Watch the HYPERLINK Video
To see a demonstration of the examples in the HYPERLINK function sample workbook, you can watch this short Excel video tutorial.
_____________
[…] 30 Excel Functions in 30 Days: 28 – HYPERLINK […]