Show
Home > Formulas > Lookup > Hyperlink 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 HyperlinksIn an Excel worksheet, you can create a hyperlink in any cell. Then, click that hyperlink to go to the specified location. Here are four ways create a hyperlink -- click a link to go to those instructions, below:
Type a Website URLIf you type a valid website address in a cell, Excel should automatically create a hyperlink, when you press Enter For example,
The text changes to a hyperlink, and you can click the link to go to the Contextures website. Tip: If you want to stop hyperlinks from being automatically created in Excel, follow the instructions here. Insert a HyperlinkAnother way to add a hyperlink is by using a built-in Excel command - Insert Hyperlink. Start the LinkTo use the Insert Hyperlink command, follow these steps to get started:
Text to DisplayAt the top of the Insert Hyperlink dialog box, there is a "Text to Display" box. What you see in the Text to Display box depends on what is in the cell where you're creating the hyperlink. Here are the 4 different types of content that affect the Text to Display option:
Text in CellIf the selected cell contains text, that text will appear in the Text to Display box.
Empty CellIf the selected cell is empty, the sheet name, and cell A1 will appear in the Text to Display box.
Number in CellIf the selected cell contains a real number, you will not be able to edit the Text to Display box. The box contents are dimmed out, and it shows this message: < < Selection in Document > >
If you want to edit the Text to Display:
OR
"Text" Number in CellIf the selected cell contains a "text" number (formatted as text, or starting with an apostrophe), that text number will appear in the Text to Display box.
Add a Screen Tip (Optional)When you point to a cell that contains a hyperlink, its Screen Tip appears. You can enter your own text for the Screen Tip, or leave this setting empty. NOTE: If you leave the Screen Tip empty, the full hyperlink address will appear in the Screen Tip. To add your own text for the Screen Tip:
Choose Link TypeThere are four options in the "Links To" panel, in the Insert Hyperlink window:
At the left side of the Insert Hyperlink window, click the link type that you need. Existing File or Web PageUse this option, if you want to make it easy for people to open another Excel file, or go to a web page, for more information.
Create Hyperlinks with Drag and DropIf 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. Create a Hyperlink With Drag and DropFollow these steps to create drag and drop hyperlinks.
Stop Automatic Hyperlinks in ExcelIf 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::
Use the HYPERLINK FunctionTo 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 EmailIf 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.
To create a hyperlink for each address:
HYPERLINK Formula to Excel FileWith 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 FileYou can use a HYPERLINK formula to create a link that 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 examples of HYPERLINK formulas within the same workbook: Type the AddressStart with a #, then type the sheet name and cell reference. Optional - Type a friendly name, such as "Budget" to appear in the cell:
Use Cell ReferencesUse the & operator to create a link location. In this example, the sheet name is in cell B5, the cell address is in C5, and the friendly name is in cell D5.
Type a Range NameFor a link to a workbook-level named range in the same workbook, just use the range name as the link location.
Next Empty CellGo to the empty cell at the end of a worksheet list, or a named table. Worksheet ListIn cell B1, the formula finds the first empty cell after the worksheet list in column A:
To find the empty row number, add the count of items in column A, plus the row number for the starting cell. Named TableIn cell E1, the formula finds the first empty cell after the named table in column D:
Link to Another Excel FileTo 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.
=HYPERLINK("..\MyFileB.xlsx","FileB") =HYPERLINK("..\..\MyFileA.xlsx","FileA") Remove HyperlinksBelow are the instructions for two methods of removing hyperlinks Manually removing hyperlinks, and a macro to remove hyperlinks Manually Remove HyperlinksIn Excel 2010 or later:In Excel 2010, you can quickly remove hyperlinks from selected cells. (Thanks to Sam for this tip.)
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 CellsInstead 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 Point to HyperlinkFollow these steps to make the pointing hand appear anywhere in a hyperlink cell, and see how to select a hyperlinked cell, without following the link. Show the Pointing HandIf a wide cell contains a short hyperlink, the pointing hand will only appear when it's over the text, not in the empty space. To fix that:
Select a Hyperlink CellIf you need to select a hyperlink cell, perhaps to edit the text, without following the link:
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 Clickable Hyperlinks for Export to PDFIn some versions of Excel, you might have a problem with your hyperlinks, when you create a PDF file from your Excel workbook. Links Not Clickable in PDFIn the PDF file, some of your hyperlinks might not be clickable, if:
For example, the PDF hyperlink won't work if the friendly name is "Click for info", and the link location is google.com Avoid the ProblemTo avoid this problem, either:
Create Fake Hyperlinks in Pivot TableHyperlinks 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 TableTo 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 AddressYou can use a macro or a User Defined function (UDF) to extract the address from a hyperlink.
Macro to Extract Address to Adjacent CellYou 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 AddressThere'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 Hide Sheets After HyperlinkIn 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 Get the Excel Hyperlink Sample Files
More TutorialsSUM Functions VLOOKUP Lookup - 2 Criteria INDEX and MATCH Count Functions INDIRECT Function Which of the following techniques can you use to remove hyperlink from a cell?Which of the following techniques can you use to remove hyperlink from a cell? Right-click the hyperlink and then click Remove Hyperlink on the shortcut menu.
Which of these series of clicks will you select to add text to a SmartArt?Insert a SmartArt graphic and add text to it
On the Insert tab, in the Illustrations group, click SmartArt. In the menu of types that appears, point at the type you want and then select a layout. Enter your text by doing one of the following: Click [Text] in the Text pane, and then type your text.
Which of the following series of clicks will you follow after clicking on a column to create a measure in the Power Pivot window?To Create a Measure in the Power Pivot Window by using AutoSum. Click on a column. Click Calculations> AutoSum, and then select an aggregation.
Which of these combination keys will you press to switch between two workbook windows?To move back and forth between any open windows (of all file types and browsers), you can use the combination ALT + TAB. You can hold the ALT key down and press TAB to cycle through all of the files until you get to the one you want.
|