Adding a Hyperlink to a Cell
Adding a hyperlink to a cell is a simple matter of typing the link information into the cell and pressing ENTER.
Once created, the user can click on the link and be transported to the links destination.
Friendly Names
Some hyperlink addresses can be long and ambiguous, giving the reader no clear indication of where the link will take them.
This is where friendly names help.
If you right-click on an existing hyperlink and select Edit Hyperlink, Excel will display the Edit Hyperlink dialog box.
From the Edit Hyperlink dialog box, in the “Text to display:” field, you can enter something meaningful like “Click here to watch the video”.
You can also add what is known as a ScreenTip; text that appears when the user hovers over the hyperlink without clicking. This can be useful to supply additional information about the link without cluttering the document.
Add Hyperlinks to Shapes
Any shape, image, or icon can become a clickable (possibly a made-up word) hyperlink.
Once one of these objects has been added to the sheet, right-click on the object and select Link.
Within the Insert Hyperlink dialog box there are several options.
- Existing File or Web Page – This can point to a Web address or a file on a local or network drive.
- Place in This Document – This option presents a list of sheets in the current workbook. You can point to a sheet and define a cell location which to place the user.
- E-mail Address – When this type of hyperlink is clicked by the user, a new email message will be created, and the recipient and subject fields of the email will be automatically populated with the information stored in the “E-mail address:” and “Subject:” fields.
If you discover an error in the link, or wish to update the link with different information, you can right-click the hyperlink and select Edit Link.
You can also remove the hyperlink information (without losing the visible text or shape) by right-clicking the hyperlink and selecting Remove Link.
Link Back in One Go
Suppose we want to add a link in cell E1 on every sheet in the workbook that returns the user to the index sheet.
If we select all the sheets except the index sheet and right-click cell E1, notice the option to add a link is not available.
How can we add the return link to all the sheets without creating each one separately?
- Select a single sheet (not the index sheet) and type some text in cell E1, such as “Start”.
- Right-click on cell E1 and select Link.
- In the Insert Hyperlink dialog box, select the index sheet and press OK.
- With the hyperlink working on a single sheet, select cell E1 and click Copy (or CTRL-C).
- Select all the other sheet tabs that you want the hyperlink to be placed.
- Click cell E1 on a selected sheet and click Paste (or CTRL-V).
You now have a return link to the index sheet on all the non-index sheets.
Changing the Color of the Link
Some people are not exactly crazy about the bright blue color assigned to unselected hyperlinks and purple for clicked (or followed) hyperlinks.
These colors can be customized by modifying the hyperlink styles in the Styles library.
To alter the link colors, right-click on a style and select “Modify…”
In the Style dialog box, select the “Format…” button.
In the Format Cells dialog box, select the Font tab and change the color option to a color of your choosing.
Hyperlink Formula
If you have many hyperlinks in a document, and you want to centrally manage their link addresses and friendly names, consider using the HYPERLINK function.
The HYPERLINK function allows you to point to a cell that holds the link information as well as the friendly name information.
The advantage of this becomes obvious when you have a link that appears numerous times throughout a document, and you want to update the pointer or displayed friendly name in a single step.
The syntax for the HYPERLINK function is as follows:
HYPERLINK(link_location, [friendly_name])- link_location – this is the path and filename of the document to be opened. This can refer to a specific cell or named range in the workbook or a bookmark in a Microsoft Word document. This can also be a path to a server location or an Internet Web address.
- [friendly_name] – this is the text that is displayed in the cell. If this argument is left out, the information supplied to the link_location argument will be displayed. This can be statically declared text or a cell pointer to a cell that contains text.
An example of the HYPERLINK function would be as follows:
=HYPERLINK(B7, A7)Data Validation List with Hyperlink
Using the list of topics and associated hyperlinks below…
…, we want the user to select a topic from a Data Validation drop-down list and have the link displayed below the user’s selection.
Begin by setting up the Data Validation list.
- Select the cell for the drop-down and create a Data Validation list that uses the list of topics found in Column A of our example.
- Select the cell for the returned link and we will use a VLOOKUP function to locate the selected item in the previous step’s drop-down cell. For our example, the formula would look something like the following:
=VLOOKUP(B4, A7:B14, 2, False)Although this will return the link address associated with the selected topic, the link is not clickable.
To make the returned information clickable, wrap the VLOOKUP function within a HYPERLINK function.
=HYPERLINK(VLOOKUP(B4, A7:B14, 2, False))We now have a selectable, clickable hyperlink cell.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 5x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
