by Svetlana Cheusheva, updated on
The tutorial explains how to hyperlink in Excel by using 3 different methods. You will learn how to insert, change and remove hyperlinks in your worksheets and now to fix non-working links.
Hyperlinks are widely used on the Internet to navigate between web-sites. In your Excel worksheets, you can easily create such links too. In addition, you can insert a hyperlink to go to another cell, sheet or workbook, to open a new Excel file or create an email message. This tutorial provides the detailed guidance on how to do this in Excel 2016, 2013, 2010 and earlier versions.
An Excel hyperlink is a reference to a specific location, document or web-page that the user can jump to by clicking the link.
Microsoft Excel enables you to create hyperlinks for many different purposes including:
Hyperlinks in Excel are easily recognizable - generally this is text highlighted in underlined blue like shown in the screenshot below.
Microsoft Excel supports two types of links: absolute and relative, depending on whether you specify a full or partial address.
An absolute hyperlink contains a full address, including the protocol and domain name for URLs, and the entire path and file name for documents. For example:
Absolute URL: https://www.ablebits.com/excel-lookup-tables/index.php
Absolute link to an Excel file: C:\Excel files\Source Data\Book1.xlsx
A relative hyperlink contains a partial address. For example:
Relative URL: excel-lookup-tables/index.php
Relative link to an Excel file: Source data\Book3.xlsx
On the web, it's a common practice to use relative URLs. In your Excel hyperlinks, you should always supply full URLs for web-pages. Though, Microsoft Excel can understand URLs without a protocol. For example, if you type "www.ablebits.com" in a cell, Excel will automatically add the default "http" protocol and convert it into a hyperlink you can follow.
When creating links to Excel files or other documents stored on your computer, you can use either absolute or relative addresses. In a relative hyperlink, a missing part of the file path is relative to the location of the active workbook. The main advantage of this approach is that you don't have to edit the link address when the files are moved to another location. For example, if your active workbook and target workbook reside on drive C, and then you move them to drive D, relative hyperlinks will continue working as long as the relative path to the target file remains unchanged. In case of an absolute hyperlink, the path should be updated every time the file is moved to another place.
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:
The most common way to put a hyperlink directly into a cell is by using the Insert Hyperlink dialog, which can be accessed in 3 different ways. Just select the cell where you want to insert a link and do one of the following:
And now, depending on what sort of link you want to create, proceed with one of the following examples:
To insert a hyperlink to another document such as a different Excel file, Word document or PowerPoint presentation, open the Insert Hyperlink dialog, and perform the steps below:
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:
To create a link to a web page, open the Insert Hyperlink dialog, and proceed with the following steps:
Excel will insert the web site Address and Text to display for you automatically. You can change the text to display the way you want, enter a screen tip if needed, and click OK to add 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.
To create a hyperlink to a specific sheet in the active workbook, click the Place in this Document icon. Under Cell Reference, select the target worksheet, 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.
Besides linking to existing files, you can create a hyperlink to a new Excel file. Here's how:
Apart from linking to various documents, the Excel Hyperlink feature allows you to send an email message directly from your worksheet. To have it done, follow these steps:
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.
If you are one of those Excel pros that employ formulas to tackle most of the tasks, you can use the HYPERLINK function, which is specially designed to inset hyperlinks in Excel. It is particularly useful when you intend to create, edit or remove multiple links at a time.
The syntax of the HYPERLINK function is as follows:
Where:
For example, to create a hyperlink titled "Source data" that opens Sheet2 in the workbook named "Source data" stored in the "Excel files" folder on drive D, use this formula:
=HYPERLINK("[D:\Excel files\Source data.xlsx]Sheet2!A1", "Source data")
For the detailed explanation of the HYPERLINK function arguments and formula examples to create various types of links, please see How to use Hyperlink function in Excel.
To automate the creation of hyperlink in your worksheets, you can use this simple VBA code:
Where:
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.
If you have little experience with Excel macros, you may find the following instructions helpful: How to insert and run VBA code in Excel
If you created a hyperlink by using the Insert Hyperlink dialog, then use a similar dialog to change it. For this, right-click a cell holding the link, and select Edit Hyperlink… from the context menu or press the Crtl+K shortcut or click the Hyperlink button on the ribbon.
Whichever you do, the Edit Hyperlink dialog box will show up. You make the desired changes to the link text or link location or both, and click OK.
To change a formula-driven hyperlink, select the cell containing the Hyperlink formula and modify the formula's arguments. The following tip explains how to select a cell without navigating to the hyperlink location.
To change multiple Hyperlink formulas, use Excel's Replace All feature as shown in this tip.
By default, Excel hyperlinks have a traditional underlined blue formatting. To change the default appearance of a hyperlink text, perform the following steps:
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.
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.
Tip. To remove all or selected hyperlinks at a time, use the Paste Special feature as demonstrated in How remove multiple 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.
By default, clicking a cell that contains a hyperlink takes you to the link destination, i.e. a target document or web-page. To select a cell without jumping to the link location, click the cell and hold the mouse button until the pointer turns into a cross (Excel selection cursor) , and then release the button.
If a hyperlink occupies just part of a cell (i.e. if your cell is wider than the link's text), move the mouse pointer over the whitespace, and as soon as it 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.
There are two ways to extract a URL from a hyperlink in Excel: manually and programmatically.
If you have just a couple of hyperlinks, you can quickly extract their destinations by following these simple steps:
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:
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.
Apart from text in a cell, many worksheet objects including charts, pictures, text boxes and shapes can be turned into clickable hyperlinks. To have it done, you simply right-click an object (a WordArt object in the screenshot below), click Hyperlink…, and configure the link as described in How to create hyperlink in Excel.
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.
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.
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 becomes the link target. If you rename the worksheet later, Excel won't be able to locate the target, and the hyperlink will stop working. To fix this, you need to either change the sheet's name back to the original name, or edit the hyperlink so that it points to the renamed sheet.
If you created a hyperlink to another file, and later moved that file to another location, then you will need to specify the new path to the file.
Symptoms: Web-addressed (URLs) typed, copied or imported to your worksheet are not converted into clickable hyperlinks automatically, nor are they highlighted with a traditional underlined blue formatting. Or, links look fine but nothing happens when you click on them.
Solution: Double-click the cell or press F2 to enter the edit mode, go to the end of the URL and press the Space key. Excel will convert a text string into a clickable hyperlink. If there are many such links, check the format of your cells. Sometimes there are issues with links placed in cells formatted with the General format. In this case, try changing the cell format to Text.
Symptoms: Your Excel hyperlinks worked just fine until you saved and reopened the workbook. Now, they are all grey and no longer work.
Solution: First off, check if the link destination has not been changed, i.e. the target document was neither renamed nor moved. If it's not the case, you may consider turning off an option that forces Excel to check hyperlinks every time the workbook is saved. There have been reports that Excel sometimes disables valid hyperlinks (for example, links to files stored in your local network may be disabled because of some temporary problems with your server.) To turn off the option, follow these steps:
Symptoms: A link created by using the HYPERLINK function does not open or displays an error value in a cell.
Solution: Most problems with formula-driven hyperlinks are caused by a non-existent or incorrect path supplied in the link_location argument. The following examples demonstrate how to create a Hyperlink formula properly. For more troubleshooting steps, please see Excel HYPERLINK function not working.
This is how you create, edit and remove a hyperlink in Excel. I thank you for reading and hope to see you on our blog next week!
Table of contents