([Link]

html)

Excel Hyperlinks and

Hyperlink Function
Create hyperlinks by dragging and dropping, or with the HYPERLINK function.
Remove hyperlinks manually, or with a macro. Make a table of contents with a
macro.

Four Ways to Create Hyperlinks

Stop Automatic Hyperlinks

Use the Excel HYPERLINK Function

Remove Hyperlinks

Create Table of Contents With Hyperlinks

Pivot Table - Fake Hyperlinks

Get Hyperlink Address

Hide Sheets After Hyperlink

Download the Sample Files

Create Excel Hyperlinks

In an Excel worksheet, you can create a hyperlink in any cell. Then, click that
hyperlink to go to the specificed location.

Here are four ways create a hyperlink -- click a link to go to those instructions,
below:

type the URL

or use the Insert Hyperlink command
or drag and drop
 or use the HYPERLINK function

Type a Website URL

If you type a valid website address in a cell, Excel should automatically create a
hyperlink, when you press Enter

For example,

1. In cell B3, type the address of the Contextures website:

[Link]
2. Press Enter

The text changes to a hyperlink, and you can click the link to go to the
Contextures website.

If you want to stop hyperlinks from being automatically created, follow the
instructions here.

Insert a Hyperlink
Another way to add a hyperlink is by using a built-in Excel command.

In this example, a hyperlink to the Google site is created:

Select the cell where you want the hyperlink

On the Excel Ribbon, click the Insert tab
Click the Hyperlink command
 In the Insert Hyperlink window, click in the Text to Display box
Delete the default text, and type the text that you want to show in the
selected cell -- Google Site in this example
At the left, click on "Existing File or Web Page"
Click in the Address box, and type the website URL -- [Link]
(Excel will automatically add the "[Link]

(Optional) To set the text for the Screen Tip that appears when you point to a cell
that contains a hyperlink:

Click the Screen Tip button

Type the text for the screen tip -- Google Website
Click OK

Click OK to add the Hyperlink to the cell.

When you pont to the hyperlink, its screen tip appears.

Create Hyperlinks with Drag and Drop

If you're creating hyperlinks within an Excel file, the quickest and easiest method
is to use drag and drop. For example, you could create a table of contents sheet
(TOC), in a workbook, with links to other sheet.

To see a quick overview of how you can create a hyperlink by drag and drop, you
can watch this short video. The written instructions are below the video.
 Create Quick Table of Contents in Excel

If you have trouble dragging to a different sheet, drag to a nearby blank cell, then
cut and paste onto the other sheet. This animated gif shows those steps.

Create a Hyperlink With Drag and Drop

Follow these steps to create drag and drop hyperlinks.

1. First, save the workbook, if it hasn't been saved yet. (In a new workbook,
that hasn't been saved, this technique won't work.)
2. Then, click on a cell that you want to link to. (You're going to drag that cell
to another sheet, where you want to create a hyperlink)
3. Point to the cell's border, and press the right mouse button.
4. To drag the cell to another sheet, press the Alt key, and drag the cell onto the
other sheet's tab.
 5. Once the other sheet is activated, release the Alt key, and drag to the cell
where you want the hyperlink
6. Release the right mouse button, and in the popup menu, click Create
Hyperlink Here

7. A hyperlink will appear in the cell, with the text from the original cell.

Stop Automatic Hyperlinks in

Excel
If you don't want hyperlinks automatically created when you type a website
address, you can turn the hyperlinking feature off. To see a quick overview of how
you can stop automatic hyperlinks from being created automatically, you can
watch this short video. The written instructions are below the video.
 Stop Automatic Hyperlinks in Excel

To turn the hyperlink option off::

1. On the Excel Ribbon, click the File tab, then click Options
2. Click the Proofing category, and click the AutoCorrect Options button
3. On the AutoFormat As You Type tab, remove the check mark from Internet
and network paths with hyperlinks
4. Click OK, twice, to close the dialog boxes

Use the HYPERLINK Function

To see a quick overview of how you can create a hyperlink with the HYPERLINK
function, you can watch this short video. Written instructions are below the video
 Create a Hyperlink with an Excel Formula

Below are the written instructions for using the HYPERLINK function to:

link to a website or email

link to an Excel file

Create a Hyperlink to a Website or

Email
If there is a list of email addresses or website addresses in Excel, you can create a
hyperlink for each row, in a separate column. In this example, there is a name in
column A, and a website URL or an email address in column B. Hyperlinks will
be created in column C.

You'll use the HYPERLINK function, which takes two arguments:

L i n k _ L o c a t i o n : This is the email address or URL. For an email address,

start with "[Link] and for a URL, start with "[Link]
F r i e n d l y N a m e : This argument is optional, and lets you specify a "friendly"
name for the link. For example, instead of a long URL, you can show a
company name, or short description.

To create a hyperlink for each address:

1. In cell C2, type the formula: = H Y P E R L I N K ( B 2 , A 2 )

2. Press Enter, to see the hyperlink in C3. It uses the link in cell B2, and the
text in cell A2 is shown in cell C2, as the Friendly Name.
3. Copy the formula down to the remaining rows.
HYPERLINK Formula to Excel File
With a HYPERLINK formula, you can link to a location in the current Excel
workbook, or to another Excel file. Watch this video to see the steps, and the
written instructions are below the video.

Open Files with Excel HYPERLINK Function

Below are the written instructions linking to an Excel file:

link within the same file

link to another Excel file

Link Within the Same File

You can use a HYPERLINK formula to create a link to goes to a cell or range
somewhere within the current workbook. The trick is to add a pound sign (#) at the
start of the address.

Here are three examples of HYPERLINK formulas within the same workbook:

Ty p e t h e A d d r e s s : Start with a #, then type the sheet name and cell

reference. Optional - Type a friendly name, such as "Budget" to appear in
the cell:
=HYPERLINK("#Sheet2!B B22" , " B u d g e t " )
 U s e C e l l R e f e r e n c e s : Use the & operator to create a link location. Here,
the sheet name is in cell B5, the cell address is in C5, and the friendly name
is in cell D5.
=HYPERLINK("#"&"'" & B5 & "'!" & C5,D5)

Ty p e a R a n g e N a m e : For a link to a workbook-level n a m e d r a n g e in the

same workbook, just use the range name as the link location.
= H Y P E R L I N K ( " # S t a r t C e l ll"", " S t a r t C e l l " )

Link to Another Excel File

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.

= H Y P E R L I N K ( " M y F i l e C ..xxl s x " , " F i l e C " )

For files that are up a level or more in the hierarchy, use two periods and a
backslash for each level.

In this example, the file is 1 level up.

=HYPERLINK("..\MyFileB B..x l s x " , " F i l e B " )

In this example, the file is 2 levels up.

= H Y P E R L I N K ( " . . \ . . \ M y F i lleeA . x l s x " , " F i l e A " )

Remove Hyperlinks
Below are the instructions for two methods of removing hyperlinks

Manually removing hyperlinks,

and a macro to remove hyperlinks

Manually Remove Hyperlinks

In Excel 2010 or later:
In Excel 2010, you can quickly remove hyperlinks from selected cells. (Thanks to
Sam for this tip.)

1. Select cell(s) that contain hyperlinks

 2. Right click on any selected cell
3. Click Remove hyperlinks

To see the steps, watch this Excel Quick Tips video.

Remove and Prevent Automatic Hyperlinks in ...

In Excel 2007 and earlier versions:

To remove hyperlinks from a group of cells, you can copy and paste the cells as
values. To see the steps, watch this Excel Quick Tips video.

Change Excel Hyperlinks to Text

Macro to Remove All Hyperlinks in

Selected Cells
Instead of manually removing the hyperlinks, you can you can use the following
macro code. It deletes all hyperlinks in the s e l e c t e d c e l l s , and leaves just the cell
text.

Copy this code into a regular module in your workbook, then select the cells, and
run the delHyperlinks macro.

Sub delHyperlinks()
'posted by Rick Rothstein
[Link]
End Sub

Create Sheet List With Links

(Macro)
The following code will create a Ta b l e o f C o n t e n t s in an Excel file, with a list of
all the v i s i b l e w o r k s h e e t s , and a hyperlink to cell A1 on each sheet. You can get
this code in a sample file -- look for download # 3 in the list below

Tip: On each worksheet, put a hyperlink back to the Table of Contents sheet.
Sub CreateTOC()
Dim wsA As Worksheet
Dim ws As Worksheet
Dim wsTOC As Worksheet
Dim lRow As Long
Dim rngList As Range
Dim lCalc As Long
Dim strTOC As String
Dim strCell As String
lCalc = [Link]

On Error GoTo errHandler

[Link] = False
[Link] = False
[Link] = xlCalculationManual

strTOC = "TOC"
strCell = "A1"
Set wsA = ActiveSheet

On Error Resume Next

Set wsTOC = Sheets(strTOC)
On Error GoTo errHandler

If wsTOC Is Nothing Then

Set wsTOC = [Link](Before:=Sheets(1))
[Link] = strTOC
Else
[Link]
End If

With wsTOC
.Range("B1").Value = "Sheet Name"
lRow = 2
For Each ws In [Link]
If [Link] = xlSheetVisible _
And [Link] <> strTOC Then
.Cells(lRow, 2).Value = [Link]
.[Link] _
Anchor:=.Cells(lRow, 2), _
Address:="", _
SubAddress:="'" & [Link] _
& "'!" & strCell, _
ScreenTip:=[Link], _
TextToDisplay:=[Link]
 lRow = lRow + 1
End If
Next ws

Set rngList = .Cells(1, 2).CurrentRegion

[Link]
.Rows(1).[Link] = True
End With

[Link] = True
[Link]
[Link](1, 2).Activate

exitHandler:
[Link] = True
[Link] = True
[Link] = lCalc
Set rngList = Nothing
Set wsTOC = Nothing
Set ws = Nothing
Set wsA = Nothing
Exit Sub
errHandler:
MsgBox "Could not create list"
Resume exitHandler
End Sub

Create Fake Hyperlinks in Pivot

Table
Hyperlinks can't be added to a pivot table, even if there are hyperlinks in the
source data. In this video, you'll see how to create fake hyperlinks in a pivot table,
by using a few lines of Excel VBA code.

The code is shown below the video, and the code has been updated to handle email
addresses too.
 Create Fake Hyperlinks in Excel Pivot Table

Create Fake Hyperlinks in Pivot Table

To create fake hyperlinks in a pivot table, you can use a few lines of code on the
worksheet's module. Copy this code, and change the field name, to match the
name in your pivot table. You can also get this code in a sample file -- look for
download # 2 in the list below

Private Sub Worksheet_SelectionChange _

(ByVal Target As Range)
Dim selPF As PivotField
Dim strField As String
Dim strAdd As String
Dim myVal As String
strField = "Site"

On Error Resume Next

Set selPF = [Link]
If Not selPF Is Nothing And _
[Link] = strField Then
myVal = [Link]
If InStr(1, myVal, "@") > 0 Then
strAdd = "[Link]
End If
[Link] _
Address:=strAdd & myVal, _
NewWindow:=True
End If

End Sub
Extract Hyperlink Address
You can use a macro or a User Defined function (UDF) to extract the address from
a hyperlink.

Macro to Extract to Adjacent Cell

Create a Hyperlink UDF

Macro to Extract Address to Adjacent

Cell
You can use a macro to extract the address from each hyperlink on a worksheet,
and store it in the cell to the right of each hyperlink cell. Thanks to Fabio
Puccinelli for this example.

NOTE: Hyperlinks that you create with the HYPERLINK function are not
recognized as worksheet Hyperlinks. Only hyperlinks that you type or insert are
recognized.

Sub ExtractHL_AdjacentCell()
Dim HL As Hyperlink
For Each HL In [Link]
[Link](0, 1).Value = [Link]
Next
End Sub

Create Function to Extract Hyperlink

Address
There's no built in function to extract the URL or email address from a hyperlink.
You can create your own Excel function -- a User Defined Function (UDF) -- to
extract the address.

To see a quick overview of how you can create your own UDF to extract a
hyperlink location, you can watch this short video. The written instructions are
below the video.

NOTE: Hyperlinks that you create with the HYPERLINK function are n o t
r e c o g n i z e d as worksheet Hyperlinks.
 Create an Excel Function to Extract Hyperlink ...

Create a Hyperlink User De�ned

Function (UDF)
Copy this code into a regular module in your workbook, then use the HLink
function in that workbook, just like any other function. For example, to find the
URL for a hyperlink in cell B3, use this formula:

=HLink(B3)

N o t e : If multiple cells are selected, the formula will show the hyperlink (if any)
from the first cell in the selected range.

Function HLink(rng As Range) As String

'extract URL from hyperlink - posted by Rick Rothstein
If rng(1).[Link] Then
HLink = [Link](1).Address
End If
End Function

Hide Sheets After Hyperlink

In a large workbook, you can use hyperlinks as a table of contents, to quickly go to
a specific sheet. Then, on each worksheet, have another hyperlink, to go back to
the table of contents.

To keep things looking tidy, you can use code that hides each sheet, after you click
a hyperlink on that sheet. Thanks to Jim Williams for suggesting this technique.

The following code is stored on the ThisWorkbook module, and it runs when a
hyperlink is clicked on an worksheet in that workbook. You can also get this code
in a sample file -- look for download # 4 in the list below
Private Sub Workbook_SheetFollowHyperlink _
(ByVal Sh As Object, ByVal Target As Hyperlink)
'shows hidden target sheet and
'hides sheet where hyperlink was clicked
Dim strWs As String
Dim strTgt As String
Dim strRng As String
Dim strMsg As String
Dim lCut As Long
On Error GoTo errHandler

strMsg = "Problem with follow hyperlink code"

Select Case [Link]
Case "Instructions", "MyLinks"
GoTo exitHandler
Case Else
strWs = [Link]
If [Link] <> strWs Then
'get the target cell/range
strTgt = [Link]
lCut = InStr(1, strTgt, "!")
strRng = Right(strTgt, Len(strTgt) - lCut)

With Sheets(strWs)
strMsg = "Could not select the target"
.Visible = True
.Activate
.Range(strRng).Activate
End With
End If
strMsg = "Could not hide the sheet"
[Link] = False
End Select

exitHandler:
Exit Sub
errHandler:
MsgBox strMsg
Resume exitHandler

End Sub

Download the Excel Hyperlink

Sample Files
1. G e n e r a l E x a m p l e s : To see the sample code and the Excel Hyperlink
formulas, you can download the Excel Hyperlink sample file
([Link]). The file is in Excel 2003 formatt, zipped, and
contains macros. Enable the macros so that you can use the sample code.
2. P i v o t Ta b l e : For the Pivot Table hyperlink code, download Fake Pivot
Table Hyperlinks (/pivotsamples/[Link]) file. The file is in
xlsm format, and zipped. Enable macros so that you can use the sample
code.
3. Ta b l e o f C o n t e n t s : For the Sheet List code, download the Excel Table of
Contents (/functionsamples/[Link]) file. The file is in
xlsm format, and zipped. Enable macros so that you can use the sample
code.
4. H i d e S h e e t s : For the code to hide sheets after a hyperlink is clicked,
download the Hide Sheets After Hyperlink Click (/functionsamples
/[Link]) file. The file is in xlsm format, and zipped. Enable
macros so that you can use the sample code.

Search Contextures Sites

Search

More Tutorials
Excel SUM Function ([Link])

Excel VLOOKUP Function ([Link])

Excel INDEX function and Excel MATCH Function ([Link])

Excel Count Function ([Link])

Excel INDIRECT Function ([Link])

Get Excel
News

Name:
 Email:

Get Started

([Link])

([Link]
Want To Place
Ads On Google?

We'll Teach You The

Basics Of AdWords &
Help Set Up Your
Account.

[Link]/AdWords
 ([Link]

Copyright © Contextures Inc. 2017

Privacy Policy (/[Link])

([Link] Dalgleish
([Link]

Last updated: December 14, 2016 4:38 PM