Contextures

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.

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:

hyperlink drag 04

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: http://www.contextures.com
  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.

hyperlink typed in cell

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

hyperlink comand on ribbon

  • 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 -- www.google.com (Excel will automatically add the "http://")

insert hyperlink window

(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.

hyperink and screen tip

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.

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.

drag and drop hyperlinks

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. hyperlink drag 02

  6. Once the other sheet is activated, release the Alt key, and drag to the cell where you want the hyperlink
  7. Release the right mouse button, and in the popup menu, click Create Hyperlink Here

    hyperlink drag 03

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

    hyperlink drag 04

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.

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

hyperlink options

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

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

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:

hyperlink arguments

  • Link_Location: This is the email address or URL. For an email address, start with "mailto:" and for a URL, start with "http://"
  • Friendly Name: 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: =HYPERLINK(B2,A2)
  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

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.

Below are the written instructions linking to an 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:

  • Type the Address: 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!B2","Budget")

  • Use Cell References: 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)

    hyperlink formula

  • Type a Range Name: For a link to a workbook-level named range in the same workbook, just use the range name as the link location.
  • =HYPERLINK("#StartCell","StartCell")

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.

=HYPERLINK("MyFileC.xlsx","FileC")

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.xlsx","FileB")

  • In this example, the file is 2 levels up.
  • =HYPERLINK("..\..\MyFileA.xlsx","FileA")

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.

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.

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 selected cells, 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
  Selection.Hyperlinks.Delete
End Sub

Create Sheet List With Links (Macro)

The following code will create a Table of Contents in an Excel file, with a list of all the visible worksheets, 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 = Application.Calculation

On Error GoTo errHandler
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = 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 = Sheets.Add(Before:=Sheets(1))
  wsTOC.Name = strTOC
Else
  wsTOC.Cells.Clear
End If

With wsTOC
  .Range("B1").Value = "Sheet Name"
  lRow = 2
  For Each ws In ActiveWorkbook.Worksheets
    If ws.Visible = xlSheetVisible _
          And ws.Name <> strTOC Then
      .Cells(lRow, 2).Value = ws.Name
      .Hyperlinks.Add _
          Anchor:=.Cells(lRow, 2), _
          Address:="", _
          SubAddress:="'" & ws.Name _
            & "'!" & strCell, _
          ScreenTip:=ws.Name, _
          TextToDisplay:=ws.Name
      lRow = lRow + 1
    End If
  Next ws
  
  Set rngList = .Cells(1, 2).CurrentRegion
  rngList.EntireColumn.AutoFit
  .Rows(1).Font.Bold = True
End With

Application.ScreenUpdating = True
wsTOC.Activate
wsTOC.Cells(1, 2).Activate

exitHandler:
  Application.EnableEvents = True
  Application.ScreenUpdating = True
  Application.Calculation = 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 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 = Target.PivotField
If Not selPF Is Nothing And _
    selPF.Name = strField Then
  myVal = Target.Value
  If InStr(1, myVal, "@") > 0 Then
    strAdd = "mailto:"
  End If
  ThisWorkbook.FollowHyperlink _
    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 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 ActiveSheet.Hyperlinks
				HL.Range.Offset(0, 1).Value = HL.Address
			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 not recognized as worksheet Hyperlinks.

Create a Hyperlink User Defined 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)

Note: 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).Hyperlinks.Count Then
      HLink = rng.Hyperlinks(1).Address
  End If
End Function

hyperlink extract

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 Sh.Name
  Case "Instructions", "MyLinks"
    GoTo exitHandler
  Case Else
    strWs = Target.Parent
    If ActiveSheet.Name <> strWs Then
      'get the target cell/range
      strTgt = Target.SubAddress
      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"
    Sh.Visible = False
End Select

exitHandler:
  Exit Sub
errHandler:
  MsgBox strMsg
  Resume exitHandler

End Sub

Download the Excel Hyperlink Sample Files

  1. General Examples: To see the sample code and the Excel Hyperlink formulas, you can download the Excel Hyperlink sample file. The file is in Excel 2003 formatt, zipped, and contains macros. Enable the macros so that you can use the sample code.
  2. Pivot Table: For the Pivot Table hyperlink code, download Fake Pivot Table Hyperlinks file. The file is in xlsm format, and zipped. Enable macros so that you can use the sample code.
  3. Table of Contents: For the Sheet List code, download the Excel Table of Contents file. The file is in xlsm format, and zipped. Enable macros so that you can use the sample code.
  4. Hide Sheets: For the code to hide sheets after a hyperlink is clicked, download the Hide Sheets After Hyperlink Click file. The file is in xlsm format, and zipped. Enable macros so that you can use the sample code.
Search Contextures Sites

 

30 Excel Functions in 30 Days

 

excel tools

 

 

 

 

excel tools

Last updated: August 9, 2016 2:44 PM