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
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.
To see a quick overview of how you can create a hyperlink by drag and drop, you can watch this short video.
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
To see a quick overview of how you can stop automatic hyperlinks from being created automatically, you can watch this short video.
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.
To see a quick overview of how you can create a hyperlink with the HYPERLINK function, you can watch this short video.
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.
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:
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.
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.
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.
In this example, the file is 2 levels up.
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.
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
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
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
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:
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
To see a quick overview of how you can create your own UDF to extract a hyperlink location, you can watch this short video.
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 Video Tutorials
Contextures Inc., Copyright ©2013
All rights reserved.
Last updated: May 16, 2013 4:01 PM