
Mục lục nội dung
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 cellular telephone. then, click that hyperlink to go to the specified location .
here are four ways create a hyperlink — click a liaison to go to those instructions, below :
Type a Website URL
If you type a valid web site address in a cell, Excel should automatically create a hyperlink, when you press enter
For case ,
- In cell B3, type the address of the Contextures website: https://enrolldetroit.org
- Press Enter
The textbook changes to a hyperlink, and you can click the link to go to the Contextures web site .
Tip : If you want to stop hyperlinks from being mechanically created in Excel, follow the instructions hera .
Insert a Hyperlink
Another way to add a hyperlink is by using a built-in Excel command – Insert Hyperlink .
Start the Link
To use the Insert Hyperlink instruction, follow these steps to get started :
- Select the cell where you want the hyperlink
- On the Excel Ribbon, click the Insert tab, and click the Hyperlink command
- OR, right-click the cell, and click Link
- OR, use the keyboard shortcut – Ctrl + K
Text to Display
At the top of the Insert Hyperlink dialogue 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 subject that affect the Text to Display option :
- Text in Cell
- Empty Cell
- Number in Cell
- “Text” Number in Cell
Text in Cell
If the selected cellular telephone contains textbook, that text will appear in the Text to Display box .
- You can edit the Text to Display text
- The revised text will appear in the cell, after you create the hyperlink.
Empty Cell
If the selected cell is empty, the tabloid diagnose, and cell A1 will appear in the Text to Display box .
- You can edit the Text to Display text
- The revised text will appear in the cell, after you create the hyperlink.
Number in Cell
If 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 > >
- NOTE: You will see the same message if you’re adding a hyperlink to a shape on the worksheet.
If you want to edit the Text to Display :
- Create the hyperlink, and then change the value in the cell
OR
- Close the Insert Hyperlink dialog box
- Change the cell’s number to text (format as text, or type an apostrophe before the number)
- Open the Insert Hyperlink dialog box again, and the Text to Display Box will be editable
“Text” Number in Cell
If the selected cell contains a “ text ” issue ( formatted as text, or starting with an apostrophe ), that textbook number will appear in the Text to Display box .
- You can edit the Text to Display text
- The revised text will appear in the cell, after you create the hyperlink.
Add a Screen Tip (Optional)
When you point to a cell that contains a hyperlink, its Screen Tip appears. You can enter your own textbook for the Screen Tip, or leave this rig empty .
NOTE : If you leave the Screen Tip evacuate, the full hyperlink address will appear in the Screen Tip .
To add your own text for the Screen Tip :
- In the Insert Hyperlink window, click the Screen Tip button
- Type the text for the screen tip – Google Website
- Click OK
Choose Link Type
There are four options in the “ Links To ” panel, in the Insert Hyperlink window :
- Existing file or Web Page
- Place in This Document
- Create New Document
- E-mail Address
At the forget side of the Insert Hyperlink window, click the link type that you need .
Existing File or Web Page
Use this option, if you want to make it easy for people to open another Excel file, or go to a network page, for more data .
- In the Link To list, click on Existing File or Web Page
- To link to an existing file, use the folder navigation, to find and select the file that you need
- OR, to link to a web page, type its URL in the Address box
Create Hyperlinks with Drag and Drop
If you ‘re creating hyperlinks within an Excel file, the quickest and easiest method acting is to use drag and drop. For exemplar, you could create a table of contents sheet ( TOC ), in a workbook, with links to other plane .
To see a agile overview of how you can create a hyperlink by embroil and drop, you can watch this short video. The written instructions are below the video .
If you have perturb dragging to a different plane, drag to a nearby blank cell, then cut and glue onto the early plane. This animated gif shows those steps .
Create a Hyperlink With Drag and Drop
Follow these steps to create drag and neglect 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.
Stop Automatic Hyperlinks
in Excel
If you do n’t want hyperlinks automatically created when you type a web site address, you can turn the hyperlinking feature off. To see a agile overview of how you can stop automatic hyperlinks from being created automatically, you can watch this short television. The written instructions are below the video .
To turn the hyperlink option off : :
- On the Excel Ribbon, click the File tab, then click 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
Use the HYPERLINK
Function
To see a quick overview of how you can create a hyperlink with the HYPERLINK routine, you can watch this unretentive video. write instructions are below the television
Below are the written instructions for using the HYPERLINK function to :
Create a Hyperlink to a Website or Email
If there is a tilt of e-mail addresses or web site addresses in Excel, you can create a hyperlink for each row, in a freestanding column. In this exemplar, there is a name in column A, and a web site URL or an e-mail 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 “https://” - 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.
HYPERLINK Formula
to Excel File
With a HYPERLINK formula, you can link to a localization in the current Excel workbook, or to another Excel file. Watch this video to see the steps, and the written instructions are below the television .
Below are the written instructions linking to an Excel file :
Link Within the Same File
You can use a HYPERLINK recipe to create a liaison that goes to a cell or range somewhere within the current workbook. The trick is to add a thump polarity ( # ) at the startle of the address .
here are examples of HYPERLINK formulas within the same workbook :
Type the Address
Start with a #, then type the sheet appoint and cell reference .
Optional – type a friendly identify, such as “ Budget ” to appear in the cell :
- =HYPERLINK(“#Sheet2!B2″,”Budget”)
Use Cell References
Use the & operator to create a link location .
In this case, 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)
Type a Range Name
For a liaison to a workbook-level named range in the lapp workbook, barely use the range name as the connection localization .
- =HYPERLINK(“#StartCell”,”StartCell”)
Next Empty Cell
Go to the empty cell at the conclusion of a worksheet list, or a named table .
Worksheet List
In cell B1, the formula finds the first base empty cell after the worksheet list in column a :
- =HYPERLINK(“#A” & COUNTA(A:A) + ROW(A3), “Add Entry”)
To find the empty row number, add the count of items in column A, plus the row number for the start cell .
Named Table
In cellular telephone E1, the formula finds the first empty cell after the named mesa in column d :
- =HYPERLINK(“#D” & SUM(COUNTA(tblDays[Day]), ROW(tblDays[[#Headers], [Day]]), 1), “Add Entry”)
To find the empty rowing total, the formula calculates the SUM of :
- The table’s starting row
- =ROW(tblDays[[#Headers],[Day]])
- The number of data rows in the table
- =COUNTA(tblDays[Day])
- Plus one, to get to the empty cell below the table
Link to Another Excel File
To create a yoke to another Excel file, in the lapp booklet, fair use the file name as the link_location argument for the HYPERLINK function .
=HYPERLINK(“MyFileC.xlsx”,”FileC”)
For files that are up a degree or more in the hierarchy, use two periods and a backslash for each flat .
- In this example, the file is 1 level
up. - In this example, the file is 2 levels up.
=HYPERLINK(“..\MyFileB.xlsx”,”FileB”)
=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 cursorily remove hyperlinks from selected cells. ( Thanks to Sam for this tap. )
- Select cell(s) that contain hyperlinks
- Right click on any selected cell
- Click Remove hyperlinks
To see the steps, watch this Excel Quick Tips television .
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 television .
Macro to Remove All Hyperlinks in Selected Cells
rather of manually removing the hyperlinks, you can you can use the following macro code. It deletes all hyperlinks in the selected cells, and leaves equitable the cell text .
replicate this code into a regular faculty 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 Hyperlink
Follow these steps to make the point hand appear anywhere in a hyperlink cell, and see how to select a hyperlinked cell, without following the radio link .
Show the Pointing Hand
If a wide-eyed 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 the cell(s)
- On the Home tab of the Excel Ribbon, click the Wrap Text command.
Select a Hyperlink Cell
If you need to select a hyperlink cell, possibly to edit the text, without following the liaison :
- Point to the cell, and press the left mouse button for a couple of seconds.
- The pointer will change to a big white plus sign, to show that the cell was selected.
- You can now edit the hyperlink cell
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
tiptoe : 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 PDF
In some versions of Excel, you might have a trouble with your hyperlinks, when you create a PDF file from your Excel workbook .
Links Not Clickable in PDF
In the PDF file, some of your hyperlinks might not be clickable, if :
- the hyperlink was created with an Excel formula
- the “friendly name” for the hyperlink is different from the link location
For exercise, the PDF hyperlink wo n’t work if the friendly mention is “ Click for information ”, and the yoke location is google.com
Avoid the Problem
To avoid this problem, either :
- manually enter the hyperlinks, instead of using the HYPERLINK function
- or, omit the friendly name argument, so the full location link is displayed in the cell
Create Fake Hyperlinks in Pivot Table
Hyperlinks ca n’t be added to a pivot mesa, even if there are hyperlinks in the reference data. In this video, you ‘ll see how to create bogus hyperlinks in a pivot postpone, by using a few lines of Excel VBA code .
The code is shown below the video, and the code has been updated to handle electronic mail addresses besides .
Create Fake Hyperlinks in Pivot Table
To create bogus hyperlinks in a pivot mesa, you can use a few lines of code on the worksheet ‘s faculty. Copy this code, and change the field name, to match the list in your pivot table. You can besides get this code in a sample file — look for download #2 in the number 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 routine ( 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 veracious of each hyperlink cell. Thanks to Fabio Puccinelli for this exercise .
eminence : 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 build in affair to extract the URL or email savoir-faire from a hyperlink. You can create your own Excel serve — a User Defined Function ( UDF ) — to extract the address .
To see a promptly overview of how you can create your own UDF to extract a hyperlink placement, you can watch this short video. The written instructions are below the video .
note : Hyperlinks that you create with the HYPERLINK officiate 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, barely like any early routine. For exemplar, to find the URL for a hyperlink in cell B3, use this formula :
=HLink(B3)
Note: If multiple cells are selected, the rule will show the hyperlink ( if any ) from the first cell in the choose 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 Hyperlink
In a large workbook, you can use hyperlinks as a table of contents, to cursorily go to a specific sheet. then, on each worksheet, have another hyperlink, to go back to the table of contents .
To keep things looking goodly, you can use code that hides each sheet, after you click a hyperlink on that sheet. Thanks to Jim Williams for suggesting this proficiency .
The pursue code is stored on the ThisWorkbook module, and it runs when a hyperlink is clicked on an worksheet in that workbook. You can besides get this code in a sample file — spirit 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
- General Examples: To see the hyperlink examples, Hyperlink function examples and macro, you can download
the Excel Hyperlink sample file.
The file is in xlsm format, zipped, and contains macros. Enable
macros if you want to use the sample code. - 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. - 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. - 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. - Hyperlink Macro: This workbook has a macro that inserts a hyperlink formula in the active cell. The macro prompts for the target cell and the hyperlink text to show. Use shortcut Ctrl+Shift+H to run the macro. The file is in xlsm format, and
zipped. Enable macros so that you can use the sample code.
More Tutorials
SUM Functions
VLOOKUP
Lookup – 2 Criteria
INDEX and MATCH
Count Functions
INDIRECT Function