Excel Hyperlinks and Hyperlink Function

Search Contextures Sites
Excel Hyperlinks and Hyperlink Function

Create Excel Hyperlinks with Drag and Drop
Watch the Excel Drag Hyperlinks Video
Stop Automatic Hyperlinks in Excel
Watch the Stop Automatic Hyperlinks Video
Create a Hyperlink with a Formula
Watch the Excel HYPERLINK Formula Video
Video: HYPERLINK Formula to Excel File
HYPERLINK Formula to Excel File
Manually Remove Hyperlinks
Remove All Hyperlinks in Selected Cells With VBA
Remove All Hyperlinks in Active Sheet With VBA
Extract Hyperlink Address to Adjacent Cell
Create Function to Extract Hyperlink Address
Watch the Create Your Own Hyperlink Function Video
Download the Excel Hyperlink Sample File
Excel Function Tutorials

Create Excel 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. Follow these steps to create drag and drop hyperlinks.

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

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)

Point to the cell’s border, and press the right mouse button.

To drag the cell to another sheet, press the Alt key, and drag the cell onto the other sheet's tab.

Once the other sheet is activated, release the Alt key, and drag to the cell where you want the hyperlink

Release the right mouse button, and in the popup menu, click Create Hyperlink Here

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

Watch the Excel Drag Hyperlinks Video

To see a quick overview of how you can create a hyperlink by drag and drop, you can watch this short video.

Stop Automatic Hyperlinks in Excel

When you type an email address or website address in Excel, it might automatically create a hyperlink in the cell, when you press the Enter key. To prevent this, you can turn the hyperlinking feature off.

To turn the hyperlink option off in Excel 2007:

Click the Office Button, then click Excel Options

Click the Proofing category, and click the AutoCorrect Options button

On the AutoFormat As You Type tab, remove the check mark from Internet and network paths with hyperlinks

Click OK, twice, to close the dialog boxes

Watch the Stop Automatic Hyperlinks Video

To see a quick overview of how you can stop automatic hyperlinks from being created automatically, you can watch this short video.

Create a Hyperlink with a Formula

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:

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:

In cell C2, type the formula: =HYPERLINK(B2,A2)

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.

Copy the formula down to the remaining rows.

Video: Excel HYPERLINK Formula

To see a quick overview of how you can create a hyperlink with the HYPERLINK function, you can watch this short video.

Video: HYPERLINK Formula to Excel File

Instead of linking to an external website with a HYPERLINK formula, you can link to a location within the active workbook, or to another Excel file.

Watch this video to see the steps, and the written instructions are below the video.

HYPERLINK Formula to Excel File

You can use a HYPERLINK formula to link to a cell within the active workbook, or to another Excel file.

Link Within the Same File

NOTE: The pound sign (#) at the start of the address indicates that the location is within the current file.

For example, use this formula to create a hyperlink to cell B2 on Sheet2:

=HYPERLINK("#Sheet2!B2","Budget")

You could also 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)

For a link to a 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")

Manually Remove Hyperlinks

In Excel 2010:

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

Select cell(s) that contain hyperlinks

Right click on any selected cell

Click Remove hyperlinks

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.

Remove All Hyperlinks in Selected Cells With VBA

Instead of manually removing the hyperlinks, you can you can use the following 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
Remove All Hyperlinks in Active Sheet With VBA

If you want to remove all hyperlinks on the active worksheet, use the following macro (Thanks to Eric and Rick).
Sub RemoveHyperlinksOnActiveSheet()
'posted by Rick Rothstein
  Cells.Hyperlinks.Delete
End Sub
Extract Hyperlink 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.
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.

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
Watch the Create Your Own Hyperlink Function Video

To see a quick overview of how you can create your own UDF to extract a hyperlink location, you can watch this short video.

Download the Excel Hyperlink Sample File

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.
Excel Function Tutorials

Excel SUM Function
Excel VLOOKUP Function
Excel INDEX function and Excel MATCH Function
Excel Count Function
Excel INDIRECT Function

Excel Video Tutorials

Excel MATCH Function Video Tutorial
Excel VLOOKUP Function Video Tutorial
Hide Excel VLOOKUP Function Errors With IF and ISNA

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

Privacy Policy

Contextures Inc., Copyright ©2013
All rights reserved.

Debra Dalgleish

Last updated: May 16, 2013 4:01 PM

	Excel Hyperlinks and Hyperlink Function Create Excel Hyperlinks with Drag and Drop Watch the Excel Drag Hyperlinks Video Stop Automatic Hyperlinks in Excel Watch the Stop Automatic Hyperlinks Video Create a Hyperlink with a Formula Watch the Excel HYPERLINK Formula Video Video: HYPERLINK Formula to Excel File HYPERLINK Formula to Excel File Manually Remove Hyperlinks Remove All Hyperlinks in Selected Cells With VBA Remove All Hyperlinks in Active Sheet With VBA Extract Hyperlink Address to Adjacent Cell Create Function to Extract Hyperlink Address Watch the Create Your Own Hyperlink Function Video Download the Excel Hyperlink Sample File Excel Function Tutorials Create Excel 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. Follow these steps to create drag and drop hyperlinks. 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.) 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) Point to the cell’s border, and press the right mouse button. To drag the cell to another sheet, press the Alt key, and drag the cell onto the other sheet's tab. Once the other sheet is activated, release the Alt key, and drag to the cell where you want the hyperlink Release the right mouse button, and in the popup menu, click Create Hyperlink Here A hyperlink will appear in the cell, with the text from the original cell. Watch the Excel Drag Hyperlinks Video To see a quick overview of how you can create a hyperlink by drag and drop, you can watch this short video. Stop Automatic Hyperlinks in Excel When you type an email address or website address in Excel, it might automatically create a hyperlink in the cell, when you press the Enter key. To prevent this, you can turn the hyperlinking feature off. To turn the hyperlink option off in Excel 2007: Click the Office Button, then click Excel Options Click the Proofing category, and click the AutoCorrect Options button On the AutoFormat As You Type tab, remove the check mark from Internet and network paths with hyperlinks Click OK, twice, to close the dialog boxes Watch the Stop Automatic Hyperlinks Video To see a quick overview of how you can stop automatic hyperlinks from being created automatically, you can watch this short video. Create a Hyperlink with a Formula 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: 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: In cell C2, type the formula: =HYPERLINK(B2,A2) 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. Copy the formula down to the remaining rows. Video: Excel HYPERLINK Formula To see a quick overview of how you can create a hyperlink with the HYPERLINK function, you can watch this short video. Video: HYPERLINK Formula to Excel File Instead of linking to an external website with a HYPERLINK formula, you can link to a location within the active workbook, or to another Excel file. Watch this video to see the steps, and the written instructions are below the video. HYPERLINK Formula to Excel File You can use a HYPERLINK formula to link to a cell within the active workbook, or to another Excel file. Link Within the Same File NOTE: The pound sign (#) at the start of the address indicates that the location is within the current file. For example, use this formula to create a hyperlink to cell B2 on Sheet2: =HYPERLINK("#Sheet2!B2","Budget") You could also 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) For a link to a 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") Manually Remove Hyperlinks In Excel 2010: In Excel 2010, you can quickly remove hyperlinks from selected cells. (Thanks to Sam for this tip.) Select cell(s) that contain hyperlinks Right click on any selected cell Click Remove hyperlinks 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. Remove All Hyperlinks in Selected Cells With VBA Instead of manually removing the hyperlinks, you can you can use the following 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 Remove All Hyperlinks in Active Sheet With VBA If you want to remove all hyperlinks on the active worksheet, use the following macro (Thanks to Eric and Rick). Sub RemoveHyperlinksOnActiveSheet() 'posted by Rick Rothstein Cells.Hyperlinks.Delete End Sub Extract Hyperlink 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. 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. 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 Watch the Create Your Own Hyperlink Function Video To see a quick overview of how you can create your own UDF to extract a hyperlink location, you can watch this short video. Download the Excel Hyperlink Sample File 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.