Beam Me Up Scotty – Excel Hyperlinks

What are Hyperlinks ?

A Hyperlink is a reference to a document, a location or an action that the reader can directly follow by selecting the link.

Hyperlinks are used extensively on the Internet and are generally Words highlighted in Underlined Blue <– Like that.

The use of Hyperlinks in Excel has been extended to a number of areas and this includes:

• Opening Files (of any type)
• Opening Web Pages (Internet or Intranet)
• Jumping/Navigating to locations within an existing document
• Creating New Documents (Excel files only)
• Sending Emails

Microsoft has added the ability to place Hyperlinks,

• Directly on an Excel worksheet ,
• Connected to a number of worksheet objects, including shapes, charts and wordart
• Included as a worksheet formulas.
• Programmatically using VBA

These 4 methods above will be discussed here.

Inserting Hyperlinks

As described above there are 4 methods for inserting hyperlinks in an Excel Workbook.

Directly on an Excel worksheet

There are 3 ways to insert a Hyperlink directly into a cell, either:

Right click on the cell and select Hyperlink; or

Use the Insert, Hyperlinks Tab; or

Use a Keyboard Shortcut – Ctrl K

Connected to a number of worksheet objects, including shapes, charts and wordart

You can also add a Hyperlink to many objects within Excel including Pictures, Shapes, Text Boxes, Word Art and Charts.

Right clicking a lot of these objects brings up the Objects Shortcuts Menu, select Hyperlink…,

or

Select the object, Use the Insert, Hyperlinks Tab; or

Select the Object and Use the Keyboard Shortcut – Ctrl K

Hint: Right Clicking on Charts Doesn’t Show the Add Hyperlink option, so Select the Chart and Ctrl K

Adding Hyperlinks using Worksheet Formulas.

Hyperlinks can be added using worksheet formulas.

=HYPERLINK( Link Location, Name)

Link Location: This is the path and file name to the document to be opened.

The Link Location can refer to a place in a document – such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file that is stored on a hard disk drive. The path can also be the path on a server or a URL, HTTP or FTP and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet. The Link Location can be a text string enclosed in quotation marks or a reference to a cell that contains the link as a text string.

Name: This is the text or value that is displayed in the cell.  The Name is displayed in blue and is underlined.

Eg:

Jump to a cell on Another sheet

=HYPERLINK(Sheet3!B3,”Monthly Budget”)

The above  will add a Hyperlink, titled “Monthly Budget” and link to Sheet3!B3 of the current workbook

Jump to a Named Range on Another sheet

=HYPERLINK(Budget,”Yearly Budget”)

The above  will add a Hyperlink, titled “Yearly Budget” and link to the Named Range “Budget” of the current workbook

Open a File on a network Drive

=HYPERLINK(“//Server01\01 Administration\Administration.docx”,”Open Admin File”)

The above  will add a Hyperlink, titled “Open Admin File” and link to the file at: //Server01\01 Administration\Administration.docx

Open a File on a network Drive at a specific bookmark

=HYPERLINK(“[//Server01\01 Administration\Administration.docx]Contents”,”Open Admin File @ TOC”)

The above  will add a Hyperlink, titled “Open Admin File @ TOC” and link to the Named Section “Contents” of the file at: //Server01\01 Administration\Administration.docx

Jump to a Web Page

=HYPERLINK(“http://chandoo.org/wp/”,”Goto Chandoo.org”)

The above  will add a Hyperlink, titled “Goto Chandoo.org” and link to http://chandoo.org/wp/

Send an Email

=HYPERLINK(“mailto:chandoo.d@gmail.com”,”Email Chandoo”)

The above will add a Hyperlink, titled “Email Chandoo” and send an email to chandoo.d@gmail.com

Adding Hyperlinks Programmatically using VBA

Hyperlinks can be added to a worksheet or a worksheet object programmatically using some simple code

Where:

SheetName: The Name of the Sheet where the Hyperlink is to go

Range:  The Range where the Hyperlink is to go

Address!Range: The address and Range linked to in the Hyperlink

Name: The Display Name of the Hyperlink

Types of Hyperlinks

There are 5 Types of Hyperlinks which Excel offers, each is described below:

• Existing File
• Existing Web Page
• Place in This Document
• Create a New Document
• Send an Email Link

Existing File

Select the existing File or Web Page icon in the Link to: area

Navigate to the existing file using the Look in: area of the dialog

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

Use the Bookmark… button to jump to predefined Named Ranges and common Cell References dialog

Existing Web Page

Select the Existing File or Web Page icon in the Link to: area

Navigate to the existing file using the Look in: area of the dialog

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

Place in This Document

Select the Place in this Document icon in the Link to: area

Type in Cell Reference using the Type in Cell Reference: area of the dialog or select a Defined Names in the Defined Names area

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

Create a New Document

Select the Create New Document icon in the Link to: area

Type in the Name of the New Document in the Name of the New Document: area of the dialog.

Add your Display Text in the Text to display: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink

You can choose wether to Edit the File Now or Later in the When to Edit area

Send an Email Link

Select the Email Address icon in the Link to: area

Type in the Email Address in the Email Address: area of the dialog.

Add your Display Text in the Text to display: area

Add your Email Subject in the Subject: area

Add a ScreenTip…, a Tip which is displayed when you hover the mouse over a Hyperlink.

Editing Hyperlinks

Once you have a hyperlink established you can edit the hyperlink by right click on the hyperlink and select Edit Hyperlink

The Edit Hyperlink dialog will vary depending on the type of Hyperlink as described above.

Deleting Hyperlinks

Once you have a hyperlink established you can delete the hyperlink by right click on the hyperlink and select Remove Hyperlink

Hyperlink Uses

Hyperlink can be used for a number of uses as described above.

Tables of Contents

One common use of hyperlinks is the creation of Tables of Contents.

The construction of a Table of Contents page was discussed here Table of Contents

The construction of Tables of Contents can also be automated using some simple VBA.

So instead of reinventing the wheel I will direct you to The Microsoft Office Blog where Tables of Conents were recently discussed.

Table of Contents 1 or Table of Contents 2

Dealing with Lots of Hyperlinks

The following 2 posts at http://chandoo.org/forums have solved users problems and will easily be adapted to other Hyperlink issues

Find Dead Hyperlinks

http://chandoo.org/forums/topic/check-broken-external-hyperlinks

Edit Hyperlinks

http://chandoo.org/forums/topic/marco-for-editing-link-in-workbook

How have you used Hyperlinks?

How have you used Hyperlinks?

Let us all know in the comments below: