Excel Hyperlink: how to create, change and remove
This tutorial shows you three easy ways to add excel Hyperlink. You will learn how to insert, change, and remove hyperlinks in your worksheets. It also explains how to fix links that don’t work.
Hyperlinks are often used on the internet to move between websites. In Excel, you can create links like that too. You can make a link to another cell, a different sheet, or even another workbook. You can also link to open a new Excel file or start an email message. This guide will show you how to do all of this in Excel 2016, 2013, 2010, and older versions.
Table of Contents
What is hyperlink in Excel
A hyperlink in Excel is a link that takes you to a specific place, document, or web page when you click on it.
Excel lets you create hyperlinks for many reasons, such as:
- Jumping to a certain spot in the same workbook
- Opening another document or going to a specific part of it, like a sheet in an Excel file or a bookmark in a Word document
- Going to a web page on the Internet or a company network
- Creating a new Excel file
- Sending an email to a certain address
Hyperlinks in Excel are easy to spot because they are usually underlined blue text, like you see in the example below.
Absolute and relative hyperlinks in Excel
Microsoft Excel supports two types of links: absolute and relative, based on how much of the address you include.
- An absolute hyperlink has the full address. For a web link, it includes the protocol (like “http” or “https”) and the domain name. For a file, it includes the entire path and file name. For example:
- Absolute web link: https://www.excelsirji.com/correlation-in-excel/
- Absolute file link: C:\Excel files\Source Data\Book1.xlsx
- A relative hyperlink only has part of the address. For example:
- Relative web link: Correlation in excel
- Relative file link: Source data\Book3.xlsx
On the web, people often use relative URLs. However, in Excel, it’s best to use full (absolute) URLs for web pages. Excel can also figure out the correct link even if you don’t include the protocol. For example, if you type “https://www.excelsirji.com” in a cell, Excel will automatically add “http” and turn it into a clickable link.
How to create a hyperlink in Excel
in Microsoft Excel, the same task can often be accomplished in a few different ways, and it is also true for creating hyperlinks. To insert a hyperlink in Excel, you can use any of the following:
How to insert a hyperlink using the Excel Hyperlink feature
The easiest way to add a hyperlink to a cell in Excel is by using the Insert Hyperlink dialog. You can open it in 3 different ways. First, select the cell where you want the link to go, then do one of these:
- Go to the Insert tab, and in the Links group, click the Hyperlink or Link button (depending on your version of Excel).
- Right click the cell, and select Hyperlink… (Link in recent versions) from the context menu.
- Press the Ctrl + K shortcut.
Create hyperlink to another document
To insert a hyperlink to another document, like a different Excel file, Word document, or PowerPoint presentation, follow these steps using the Insert Hyperlink dialog:
- On the left-hand side, under Link to, click Existing File or Web Page.
- In the Look in list, browse to the location of the file you want to link to and select it.
- In the Text to display box, type the text you want to show in the cell (for example, “Book3”).
- Optionally, you can click the ScreenTip button in the top-right corner and type a message that will appear when someone hovers over the hyperlink (for example, “Go to Book3 in My Documents”).
- Click OK.
The hyperlink is inserted in the selected cell and looks exactly as you’ve configured it:
To link to a specific sheet or cell, click the Bookmark… button in the right-hand part of the Insert Hyperlink dialog box, select the sheet and type the target cell address in the Type in the cell reference box, and click OK.
To link to a named range, select it under Defined names like shown below:
Add a hyperlink to a web address (URL)
To create a link to a web page in Excel, use the Insert Hyperlink dialog and follow these steps:
- Under Link to, select Existing File or Web Page.
- Click the Browse the Web button, open the web page you want to link to, and then switch back to Excel without closing your web browser.
- Excel will automatically add the website’s address and fill in the text to display. You can change the display text if you want, add a screen tip if needed, and then click OK to insert the hyperlink.
Alternatively, you can copy the web page URL before opening the Insert Hyperlink dialog, and then simply paste the URL in the Address box.
Hyperlink to a sheet or cell in the current workbook
To make a link to a specific sheet in your current workbook, click the ‘Place in this Document’ button. Then, under ‘Cell Reference,’ choose the worksheet you want to link to, and click OK.
To create an Excel hyperlink to cell, type the cell reference in the Type in the cell reference box.
To link to a named range, select it under the Defined Names node.
Insert a hyperlink to open a new Excel workbook
You can also create a link to a new Excel file. Here’s how:
- Under ‘Link to,’ click the ‘Create New Document’ button.
- In the ‘Text to display’ box, type the text you want to show in the cell.
- In the ‘Name of new document’ box, enter the name for the new workbook.
- Under ‘Full path,’ check where the new file will be saved. If you want to change this location, click the ‘Change’ button.
- Under ‘When to edit,’ choose when you want to edit the file.
- Click OK
A hyperlink to create an email message
In addition to linking to different documents, the Excel Hyperlink feature lets you send an email directly from your worksheet. Here’s how to do it:
- Under ‘Link to,’ click the ‘E-mail Address’ button.
- In the ‘E-mail address’ box, type the email address of the person you want to send to. You can also add more addresses, separated by semicolons.
- If you want, enter a subject for the email in the ‘Subject’ box. Keep in mind that some email programs may not show this subject.
- In the ‘Text to display’ box, type the text you want to appear as the link.
- If you want, click the ‘ScreenTip…’ button and enter a message. This message will show when you hover over the link with your mouse.
- Click OK.
Tip. The fastest way to make a hyperlink to a specific e-mail address it to type the address directly in a cell. As soon as you hit the Enter key, Excel will automatically convert it into a clickable hyperlink.
How to create links by using HYPERLINK function
If you’re an Excel expert who uses formulas for many tasks, you can use the HYPERLINK function to add hyperlinks in Excel. This function is especially helpful when you want to create, edit, or remove several links at once.
Here’s how the HYPERLINK function looks
HYPERLINK(link_location, [friendly_name])
How to insert hyperlink in Excel by using VBA
To automate the creation of hyperlink in your worksheets, you can use this simple VBA code:
Public Sub
AddHyperlink() Sheets(“Sheet1”).Hyperlinks.Add Anchor:=Sheets(“Sheet1”).Range(“A1″), Address:=””, SubAddress:=”Sheet3!B5″, TextToDisplay:=”My hyperlink”
End Sub
Where:
- Sheets– the name of a sheet on which the link should be inserted (Sheet 1 in this example).
- Range– a cell where the link should be inserted (A1 in this example).
- Sub Address– link destination, i.e. where the hyperlink should point to (Sheet3!B5 in this example).
- Text To Display-text to be displayed in a cell (“My hyperlink” in this example).
Given the above, our macro will insert a hyperlink titled “My hyperlink” in cell A1 on Sheet1 in the active workbook. Clicking the link will take you to cell B5 on Sheet3 in the same workbook.
How to change hyperlink in Excel
If you made a hyperlink using the Insert Hyperlink dialog, you can change it using a similar dialog. To do this, right-click the cell with the link and select ‘Edit Hyperlink…’ from the menu. You can also press Ctrl+K or click the Hyperlink button on the ribbon.
Whichever method you choose, the Edit Hyperlink dialog box will appear. You can change the link text, the link location, or both, and then click OK.
To change a hyperlink created with a formula, select the cell with the Hyperlink formula and change the formula’s details. The following tip shows how to select a cell without going to the hyperlink location.
If you want to change several Hyperlink formulas at once, use Excel’s ‘Replace All’ feature, as explained in this tip
How to change a hyperlink appearance
By default, Excel hyperlinks are blue and underlined. To change how hyperlink text looks, follow these steps:
- Go to the Home tab and find the Styles group or You can either:
- Right-click on ‘Hyperlink’ and click ‘Modify…’ to change the look of links that haven’t been clicked yet.
- Right-click on ‘Followed Hyperlink’ and click ‘Modify…’ to change the look of links that have been clicked.
2.In the Style dialog box that appears, click Format…
3. In the Format Cells dialog, switch to the Font and/or Fill tab, apply the options of your choosing, and click OK. For example, you can change the font style and font color like shown in the screenshot below:
4. The changes will be immediately reflected in the Style If upon a second thought, you decide not to apply certain modifications, clear the check boxes for those options.
5. Click OK to save the changes.
Note. All changes made to the hyperlink style will apply to all hyperlinks in the current workbook. It is not possible to modify formatting of individual hyperlinks.
How to remove hyperlink in Excel
Removing hyperlinks in Excel is a two-click process. You simply right-click a link, and select Remove Hyperlink from the context menu.
This will remove a clickable hyperlink, but keep the link text in a cell. To delete the link text too, right-click the cell, and then click Clear Contents.
Tips for using hyperlinks in Excel
Now that you know how to create, change and remove hyperlinks in Excel, you may want to learn a couple of useful tips to work with links most efficiently.
How to select a cell containing a hyperlink
Normally, clicking a cell with a hyperlink takes you to the link’s destination, like a document or webpage. To select the cell without going to the link, click and hold the mouse button until the cursor turns into a cross (Excel’s selection cursor), then release the button.
If the hyperlink is only part of the cell (because the cell is wider than the link text), move your mouse over the empty space in the cell. When the cursor changes from a pointing hand to a cross, click the cell.
One more way to select a cell without opening a hyperlink is to select a neighboring cell, and use the arrow keys to get to the link cell.
How to extract a web address (URL) from Excel hyperlink
There are two ways to extract a URL from a hyperlink in Excel: manually and programmatically.
Extract a URL from a hyperlink manually
If you have just a few hyperlinks, you can easily get their web addresses by following these steps:
- Select the cell with the hyperlink.
- Open the Edit Hyperlink dialog by pressing Ctrl + K, or right-click the hyperlink and choose ‘Edit hyperlink…’.
- In the ‘Address’ field, select the URL and press Ctrl + C to copy it.
4. Press Esc or click OK to close the Edit Hyperlink dialog box.
5. Paste the copied URL into any empty cell. Done!
Extract multiple URLs by using VBA
If you have a great lot of hyperlinks in your Excel worksheets, extracting each URL manually would be a waste of time. The following macro can speed up the process by extracting addresses from all hyperlinks on the current sheet automatically:
Sub ExtractHL()
Dim HL As Hyperlink
Dim OverwriteAll As Boolean
OverwriteAll = False
For Each HL In ActiveSheet.Hyperlinks
If Not OverwriteAll Then
If HL.Range.Offset(0, 1).Value <> “” Then
If MsgBox(“One or more of the target cells is not empty. Do you want to overwrite all cells?”, vbOKCancel, “Target cells are not empty”) = vbCancel
Then Exit For
Else
OverwriteAll = True
End If
End If
End If
HL.Range.Offset(0, 1).Value = HL.Address
Next
End Sub
As shown in the screenshot below, the VBA code gets URLs from a column of hyperlinks, and puts the results in the neighboring cells.
If one or more cells in the adjacent column contains data, the code will display a warning dialog asking the user if they want to overwrite the current data.
Convert worksheet objects into clickable hyperlinks
In addition to text in a cell, many objects in a worksheet, like charts, pictures, text boxes, and shapes, can be turned into clickable links. To do this, just right-click the object (like the WordArt object in the picture below), click ‘Hyperlink…’, and set up the link as explained in the ‘How to create a hyperlink in Excel’ section.
Tip. The right-click menu of charts does not have the Hyperlink option. To convert an Excel chart into a hyperlink, select the chart, and press Ctrl + K.
Excel hyperlinks not working - reasons and solutions
If hyperlinks are not working properly in your worksheets, the following troubleshooting steps will help you pin down the source of the problem and fix it.
Reference isn't valid
Symptoms: Clicking a hyperlink in Excel does not take the user to the link destination, but throws the “Reference isn’t valid” error.
Solution: When you create a hyperlink to another sheet, the sheet’s name is used as the link’s target. If you rename the sheet later, the link will break because Excel won’t find the target. To fix this, either change the sheet’s name back to the original name or update the hyperlink to point to the new name.
If you linked to a file and moved that file to a new location, you’ll need to update the link with the new file path.
Hyperlink appears as a regular text string
Symptoms: Web addresses (URLs) typed, copied, or imported into your worksheet are not turning into clickable links automatically, and they aren’t in the usual blue, underlined format. Or, the links look correct, but nothing happens when you click them.
Solution: Double-click the cell or press F2 to edit it, go to the end of the URL, and press the Space key. This will turn the text into a clickable link. If there are many links, check the cell format. Sometimes links don’t work in cells set to ‘General’ format. In that case, try changing the format to ‘Text.
Hyperlinks stopped working after reopening a workbook
Symptoms: Your Excel hyperlinks were working fine before, but now, after saving and reopening the workbook, they are all grey and don’t work anymore.
Solution: First, check if the link’s destination hasn’t changed, like if the file was renamed or moved. If that’s not the issue, you can turn off an option that makes Excel check links every time the workbook is saved. Sometimes, Excel disables working links, especially to files on a local network, because of temporary server issues. To turn off this option, follow these steps:
- In Excel 2010, 2013, or 2016, click File > Options. In Excel 2007, click the Office button > Excel Options.
- On the left panel, select Advanced.
- Scroll down to the General section and click Web Options…
- In the Web Options dialog, go to the Files tab, uncheck the ‘Update links on save’ box, and click OK.
Formula-based hyperlinks do not work
Symptoms: A link made using the HYPERLINK function doesn’t open or shows an error in the cell.
Solution: Most problems with formula-based links happen because the path in the link_location argument is wrong or doesn’t exist. The examples below show how to create a correct Hyperlink formula. For more troubleshooting steps, see ‘Excel HYPERLINK function not working.’
That’s how you create, edit, and remove hyperlinks in Excel. Thank you for reading, and I hope to see you on our blog next week!
