Sometimes it can be useful to create links in your worksheets that users can follow with just a click.
This worksheet contains three hyperlinks: one to another sheet in the same workbook, one to an e-mail address, and one to a web page. In this lesson, you will learn what hyperlinks are and how to insert them into a worksheet. You will also learn how to modify hyperlinks in a
worksheet and how to browse hyperlinks in Excel.
What is a Hyperlink?
A hyperlink is an object that links to another location in the same file, or to another file altogether. Word processing documents, workbooks, and web pages are all types of files that can contain hyperlinks. When you click on a hyperlink, the associated file or location will open.
The files connected by hyperlinks do not have to be in the same directory, or even on the same computer. In fact, you can have a hyperlink in one file that links to a file thousands of miles away. The World Wide Web makes extensive use of hyperlinks to link web pages together. This is, in fact, where most computer users first become familiar with hyperlinks.
Quite often, hyperlinks come in the form of an underlined word or phrase in a bright blue font.
This does not have to be the case, though, as hyperlinks can be a word, a phrase, a symbol, or even a picture! When you click on a hyperlink, an associated URL is used to locate and retrieve the object or file that the hyperlink points to. In the case of the World Wide Web, hyperlinks
to a web page, to a file on a remote server, to a location in the same workbook, or to a specific e-mail recipient.
Inserting Hyperlinks
There are times when you might want to insert a link in your Excel worksheet that takes you to another location in the same workbook, or perhaps to another Excel workbook located on the same computer. You might also want to insert a link to a Word document or to a web page that contains helpful information. Fortunately, in Excel 2010 it is fairly easy to insert hyperlinks into your worksheets.
To start, right-click on the cell that will contain the link, and then click Hyperlink:
Another way to display the dialog is to first select the cell that will contain your hyperlink, and then choose Insert → Hyperlink:
Either action will open the Insert Hyperlink dialog:
Take note of the four buttons running down the left side of the dialog box under the “Link to:”
heading. You can use these buttons to create a link to an “Existing File or Web Page,” a “Place in This Document,” “Create New Document,” or to an “E-mail Address.”
At the top of the dialog, you can see a box named “Text to Display.” The text entered here will appear as a blue underlined hyperlink in the cell that you originally selected. This is what the user will see and click on.
If you choose the Existing File or Web Page button, you will see a drop list labeled “Look in” just below the “Text to display” field. You can use this list to browse through the various folders on your computer to find the file that you want to create a link to.
When you select a folder or drive from the drop list, a list of available files or folders corresponding to the selection will appear in the large center area of the dialog box.
You will also notice three buttons just to the left of the navigation area: Current Folder, Browsed Pages, and Recent Files.
If you click Current Folder, the drives, files, or subfolders of the current item displayed in the drop list will be displayed. In the preceding image, you can see that “Computer” is entered in the drop box, and the drives on “Computer” are displayed. You can use these navigation tools to navigate and select pretty much any file on your system.
If you click the “Browsed Pages” button, a list of web pages that you recently visited will be displayed, like this:
If you click an item in the list to select it, information for the selected item (a URL) will be entered in the “Address” field and in the “Text to display” field (the text that forms the actual hyperlink).
If you click on the Recent Files button, you will see a list of recently accessed files.
If you make a selection from an item on one of these lists, the location information (URL or directory path) for the item will be entered in the Address field at the bottom of the dialog box (much like the preceding example for “Browsed Pages”).
You can also take the old fashioned route and type a URL for a web page or remote file, or the path to a local file, directly into the “Address” field. In any case, the file or object referred to in the Address field will be the target of the hyperlink.
You can also click the ScreenTip button in the upper right of the dialog box to add a brief comment that will appear when the mouse pointer hovers over the hyperlink.
Here is an example of a screen tip in action:
You can also the use the Bookmark button to link to a very specific location (like a cell range) inside another Excel workbook. First, select the Excel file from the list in the large center area, and then click the Bookmark button.
You will then see the Select Place in Document dialog:
At this point you can click to select the specific worksheet that you want to link to and type in a specific cell reference.
When you have finished making the necessary entries in the dialog box, click OK to return to the Insert Hyperlink dialog. For whatever type of hyperlink you specify, once you have chosen the correct settings, click OK to create your link.
Editing Hyperlinks
To change the displayed text of an existing hyperlink, you can select the cell that contains the link, and then edit the link text in the formula bar:
You can also right-click the hyperlink and click Edit Hyperlink:
This action will display the Edit Hyperlink dialog, which is almost identical to the Insert Hyperlink dialog.
You can now change the target for the hyperlink, the ScreenTip, or the text to display. You can also remove the hyperlink from the worksheet by clicking Remove Link. Once you are ready to commit to your modifications, click OK.
Formatting Hyperlinks
Hyperlinks will generally appear in bright blue before they have been clicked and purple after they have been clicked. You can change the font color and size for an individual hyperlink by right-clicking it and choosing Format Cells:
This will display the Format Cells dialog box, which you can use to modify the font characteristics, border, shading, and alignment of the link.
The method just described will allow you to format individual hyperlinks; but what about modifying the default color and font size for all hyperlinks?
To start, click the Cell styles button in the Style group on the Home tab. From the options that appear, find the Hyperlink style, right-click it, and choose Modify:
The Style dialog will then open:
Make sure that the word Hyperlink is visible in the “Style name” text box at the top of the dialog. You should also see a check mark next to the word “Font” in the column of checkboxes on the left. This means that the style for hyperlinks currently includes only font settings.
Next, click the Format button in the upper right of the dialog to open the Format Cells dialog.
Here, you can make changes to the appearance of all hyperlinks:
When you are finished modifying the hyperlink style, click OK in the Format Cells dialog.
You will now see the Style dialog again, with checkmarks corresponding to the style changes you made in the Format Cells dialog. If you are finished, click OK in the Style dialog to
implement the changes.
Now every new hyperlink you create, along with the existing ones, will have this new style by default.
Using Hyperlinks in Excel
To follow a hyperlink in Excel 2010, let your mouse pointer hover over it until a pointing hand appears. When you see the hand, left click the mouse and the target for the link will be opened with a program that is suited to do so. For example, if you click a link to a Word document, the document will open in Microsoft Word. If you click a link to an Access database, the database will open with Microsoft Access. If you click a link to a web page, the page will open with your default Internet browser.
The following worksheet contains two hyperlinks.
The first link is to a Word document and the second is to an Access database. The two linked documents are in separate folders saved on the same computer as the workbook that contains the links. If you click the first link (the Word document) the target document will open in Microsoft Word. Depending on your security settings, you might see a dialog like this:
This warning is reminding you that hyperlinks linking to unknown files can be a potential source of trouble, since a hyperlink could link to a file that contains malicious data. If you trust the source of the workbook or the linked file, click Yes. Then, the destination file will open:
You can see that the first link (in cell B1) is now purple, meaning that it has been followed. You should also notice that the linked document has opened in Microsoft Word.
If we click the second link (in cell B11) another file will open, but because the target file is a database file, it will open in Access:
To remove a hyperlink from your spreadsheet, right-click the cell containing it and click Delete:
This action will remove the hyperlink from the worksheet and the cell in question will be left empty.
You can also right-click on a cell with a hyperlink and click Remove Hyperlink:
This action will remove the target information from the link, but the text of the link will remain in the cell.
You can also insert a hyperlink function by clicking the Insert Function button by the formula bar:
This action will display the Insert Function dialog, from which you can select the Hyperlink function:
If you select HYPERLINK and click OK in the dialog, the Function Arguments dialog box will appear.
In the “Link_location” text box, you can specify the target of the link. This can be a web page URL, a local file, or a file on a network server. In the “Friendly_name” box, you can specify the particular text or number that will be visible in the spreadsheet. When you click OK, the link will be inserted into the spreadsheet.
You can also specify an image or other graphic as a hyperlink in your spreadsheet. To do this, just click the image and click Insert → Hyperlink:
This action will display the same Insert Hyperlink dialog that we saw earlier. Use the Insert Hyperlink dialog to specify the target of the hyperlink, and then click OK to associate the linked location to the image. Once you have created the link, if you click on the image the link will be followed to its target, just like a regular hyperlink. You can edit and remove it in the same way.