Home > Formulas > Lookup > Hyperlink Excel Hyperlinks and Hyperlink Function Create hyperlinks by dragging and dropping, or with the Microsoft Excel HYPERLINK function. Remove hyperlinks manually, or with a macro. Hyperlink security warnings. Make a clickable table of contents in an Excel workbook Tip: Also see workaround for clickable hyperlinks in pivot tables.

Create Excel Hyperlinks

In a Microsoft 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 in an Excel spreadsheet -- click a link to go to those instructions, below:

• type the URL
• or use the Insert Hyperlink command
• or drag and drop
• or use the HYPERLINK function

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: https://www.contextures.com
2. Press Enter

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 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 command, 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 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, with details below:

• a) Text in Cell
• b) Empty Cell
• c) Number in Cell
• d) "Text" Number in Cell

a) Text in Cell

If the selected cell contains text, 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.

b) Empty Cell

If the selected cell is empty, the sheet name, 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.

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

d) "Text" Number in Cell

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

• 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 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:

• 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:

1. Existing file or Web Page
2. Place in This Document
3. Create New Document
4. E-mail Address

At the left 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 web page, for more information.

• 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

Video: 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.

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

   

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

   

Click that link to go to the destination.

Video: 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, follow these steps:

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 Security Warning

In some versions of Excel, you might see a security warning (shown below), when you click on a worksheet hyperlink.

Every morning, I see these messages after clicking on worksheet links that I know are safe, such as the Canadian government's weather forecast, and pages on my own website!

Warning Message Text

For example, the screen shot below shows the message I see after clicking a link to the Microsoft site.

Here is the text shown in the warning message:

• Title: Advanced Threat Protection - Safe Links
• Message: Verification of this link failed due to an error.
• Address: https://www.microsoft.com/
• Buttons:
  • Cancel navigation (or press Esc key)
  • Skip verification (or press Enterkey)

Cannot Turn Warnings Off

These warnings are part of Microsoft's security system, and there's no setting in Excel where you can turn those safety warnings off.

Also, there's no list where you can "whitelist" specific websites, to assure Excel that they're safe. No matter how many times you've previously clicked a link, you'll get the warning message.

Click one of the buttons, based on your trust of the destination website:

• Safe Link: If you click a hyperlink that you know is safe, click the Skip verification button, to continue to the website. Or, tap the Enter key on your keyboard.
• Unknown Link: If you aren't sure that the link is safe, click the Cancel navigation button, or tap the Exc key on your keyboard.

Video: Excel HYPERLINK Function

This video shows a quick overview of how you can create clickable hyperlinks with the Excel HYPERLINK function. There are written steps and examples below the video.

Excel HYPERLINK Function

Instead of creating hyperlinks manually, you can use the HYPERLINK function to build clickable links.

The HYPERLINK function can open a document, or jump to a specific location, so you can:

• Link to location in same file
• Link to Excel file in same folder
• Link to website

For example, if there is a list of people's names and email addresses on an Excel worksheet, HYPERLINK can create a link that shows the name, and opens a blank email addressed to that person.

HYPERLINK Syntax

The HYPERLINK function has two arguments in its syntax:

• link_location: Text string for the location where you want to go
  • For a URL, start with "https://"
  • For an email address, start with "mailto:"
• friendly_name: (optional) Text you want displayed in the cell
  • If omitted, the Link_Location is shown in the cell
  • Tip: Type a company name, or short description

Character Limit

For the HYPERLINK function, there is a character limit when you build the link_location string.

If you exceed the limit, the formula result will be a #VALUE error.

• Link_Location: There is a limit of 255 characters in the Link_Location argument.
  • Characters are counted whether you:
    • type them into the formula, OR
    • refer to cells that contain the text
• Friendly_Name: In my tests, the 255 character limit DID NOT apply to the Friendly_Name argument.

HYPERLINK Function Examples

See the following sections for HYPERLINK function examples:

• 1) Link to website
• 2) Link to email
• 3) Link to Excel file

1) HYPERLINK Example - Website

In the screen shot below, there are company names in column A, and a URL for each company's website in column B.

• Note: Each URL starts with "https://", so Excel recognizes it as a valid link

In the HYPERLINK formula, both arguments will be used:

• Link_Location: URL addresses from column B
• Friendly_Name: Names from column A

To create a hyperlink for each address, in column C, follow these steps:

• In cell C2, type the following formula:
  • =HYPERLINK(B2,A2)
• Press Enter, to see the completed hyperlink in C3.

The formula result shows the name from cell A2.

If you click the link, it goes to the company website URL from cell B2.

• To complete the lisk of hyperlinks, copy the formula down to the remaining rows.

2) HYPERLINK Example - Email

The HYPERLINK function can also create a link that opens an email message.

In the example shown below, there are email addresses in column A, and the HYPERLINK formula will be added in column B.

To create an email link, the Location_Link text string needs to start with "mailto:"

• You can include that in the cell with the email address
• OR, type that "mailto:" string, inside quotation marks, in the HYPERLINK formula
  • =HYPERLINK("mailto:" & B5 , "Click here")

Email Message Settings

For email hyperlinks, that start with "mailto:" you can include extra information in the link_location argument.

For example, the following formula includes text for the email subject line, and for the email body

• =HYPERLINK("mailto:" & B5 &
  " ?subject=Urgent Message
     &body=Call my office for details","Click here")

Here's what the email looks like, if you click the HYPERLINK formula link.

• NOTE: Due to the 255 character limit in link_location, you won't have room for too much extra information.

MailTo - Subject and Body Text

To include email subject and body text in the HYPERLINK formula, the link needs to follow the internet standards, using the syntax described in the RFC Editor, in the mailto URL scheme documentation.

• ?subject - The question mark (?) indicates that additional fields are being included, and Subject is the field name
• &body - The Ampersand (&) marks the start of each additional field name -- Body, in this case.

Add a Line Break

There are email URL examples in the RFC Editor, mailto URL scheme documentation, section 6. You can adapt those examples, and use them to build link_location strings for the Excel HYPERLINK function.

I found those examples, when I wanted to put a line break in a message body. Some of the examples show the special code that you can use, to indicate a line break:

%0D%0A

For example, the following formula combines gets the email address from cell B5, then adds a subject line.

In the message body, there are two lines of text, separated by a line break.

• =HYPERLINK("mailto:" & B5 &
      " ?subject=Urgent Message
      &body=Call office for details" &
      "%0D%0A" &
      "Deadline: Tuesday","Click here")

3) HYPERLINK Example - Excel File

With a HYPERLINK formula, you can link to a location in the current Excel workbook, or to another Excel file. This video shows the steps, and there are written steps below the video.

• a) Link in same file
• b) Link to another Excel file

TIP: To follow along with this video, you can download the Open Excel Files with Hyperlinks workbook.

a) Link in Same File

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

• Tip: If you have trouble creating the correct location string for the HYPERLINK function, manually insert a link with the Hyperlink command on Excel’s Ribbon. That should show you the correct syntax, then recreate that in your link_location argument.

Below, there are 6 examples for HYPERLINK formulas within the same workbook:

1) 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")

2) Use Cell References

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

• =HYPERLINK("#"&"'" & B5 & "'!" & C5,D5)

3) 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")

4) Next Empty Cell

Go to the empty cell at the end of a worksheet list, or a named table.

5) Worksheet List

In cell B1, the formula finds the first 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 starting cell.

6) Named Table

In cell E1, the formula finds the first empty cell after the named table in column D:

• =HYPERLINK("#D" & SUM(COUNTA(tblDays[Day]), ROW(tblDays[[#Headers], [Day]]), 1), "Add Entry")

To find the empty row number, 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

b) Link to Another Excel File

To create a link to a different workbook, 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")

Use Cell References

Or, instead of typing the file path details in the HYPERLINK formula, you can refer to cells that contain the location text.

In the screen shot below, the formula in cell E3 refers to the address in C3, and the friendly name in cell D3:

• =HYPERLINK(C3, D3)

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

Point to Hyperlink

Follow 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 Hand

If 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 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, perhaps to edit the text, without following the link:

• 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

Add Hyperlink in Threaded Comment

Thanks to Christian Back (LinkedIn profile), who alerted me to this helful new feature in threaded comments.

In Excel 365, you can add clickable hyperlinks in Excel's threaded comments. That makes it easy to provide links to related information, without filling the worksheet cells with hyperlinks.

If you're not familiar with threaded comments, see the basic steps on my Threaded Comments page. You can also download the Comment Hyperlinks sample file, in the download section, at the end of this page.

Follow Hyperlink in Comment

To use a threaded comment hyperlink, follow these steps:

• Point to a cell that has a purple marker, at the top right corner
• When the threaded comment appears, point to a hyperlink in one of the comments
• On your keyboard, press the Ctrl key, then click the hyperlink, to follow that link
• Note: After clicking the hyperlink, you might see a warning message -- Advanced Threat Protection - Safe Links. See notes on that warning, above.

See Hyperlinks in Comments Pane

In some cases, the Ctrl + click doesn't work, when I use the hyperlink in the cell's pop-up threaded comments.

If that happens in your version of Excel too, you can open the Comments Taskpane, and then Ctrl + Click the hyperlinks there.

For details on opening the taskpane, and adjusting it, go to the Threaded Comments macros page.

• Note: After clicking the hyperlink, you might see a warning message -- Advanced Threat Protection - Safe Links. See notes on that warning, above.

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 PDF

In 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 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 example, the PDF hyperlink won't work if the friendly name is "Click for info", and the link 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

Extract Hyperlink Address

You can use a macro or a User Defined function (UDF) to extract the address from a hyperlink.

• Macro to Extract to Adjacent Cell
• Create a Hyperlink UDF

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

Video: 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

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

Get the Excel Files ✅

1. 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.
2. 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.
3. 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.
4. 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.
5. Comment Hyperlinks: Get the sample file for Excel hyperlinks in threaded comments. The zipped Excel workbook is in xlsx format, and does NOT contain macros.
6. Hidden Hyperlink: Get the sample file for hidden hyperlinks. Use conditional formatting and formulas, to hide a worksheet hyperlink. On first day of month, hyperlink is visible, with link to Monthly Update sheet. The zipped Excel workbook is in xlsx format, and does NOT contain macros.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Pivot Table Hyperlinks

SUM Functions  

VLOOKUP   

Lookup - 2 Criteria

INDEX and MATCH   

HTML Code, Create

Count Functions

INDIRECT Function