Using hyperlinks

One of the strengths of modern computers is your ability to connect to outside resources using what are called hyperlinks. You probably think of the World Wide Web when you think of hyperlinks, but you can create these links within an Excel workbook to provide access to web sites, other files, or to send e-mail messages to your colleagues. In this movie, I'll show you how to create and manage hyperlinks in Excel workbooks. The first type of hyperlink I'll show you how to create is a hyperlink to a web page. To do that, I click the cell where I want the hyperlink to appear, and then there are two ways to get to the Hyperlink dialog box.

The first way is to click the Insert menu head, and then all the way at the bottom, click Hyperlink. When you do, the Insert Hyperlink dialog box appears. So now in the Link to box, I can type in the web address that I want to connect to, and in this case it is www.twotreesoliveoil.com. And you'll notice that Excel added the http:// at the start of the address for me. And this text here is the text that will be displayed within the cell that contains the hyperlink.

When I opened this dialog box, there was already text there: Two Trees Olive Oil, and I'll keep it, but I could edit it if I wanted to. I am creating a web page. I don't need to worry about the anchor. That's a specific location within a page. If you don't know what it is, don't worry about it. It's not used all that often. Everything looks good. I can click OK, and Excel creates my hyperlink. And you can tell it's a hyperlink because it's in blue, and it's underlined. If I move the mouse pointer over the top of it, of the cell that contains the hyperlink, my mouse pointer changes to a pointing hand, and that means that when I click, Excel will open my web browser and go to the address, which you see here: twotreesoliveoil.com.

So that hyperlink works. Now I'll just close Firefox and go back to Excel. I don't want to save anything, and we're back. Next, I'm going to show you how to create a link to a document that's on your computer, or that is reachable over your network. To do that, I click the cell where I want to create the hyperlink. And then, instead of clicking Insert Hyperlink on the menu system, I can use the keyboard shortcut, which is Command+K. When I click that, I get the Insert Hyperlink dialog box, and again, it's on the web page page.

I want to create a link to a document, so I will click Document. And now I can select the file to link to. Or if I knew the directory, and the path, and the name, I could type it in longhand, but it's much easier to select it. I will click Select, and I see my exercise files for Chapter07, and the file I want to link to is PivotReport. So I click it, see my Preview, click Open, and now I'm back in the Insert Hyperlink box. I have the name of the file here to link and everything looks good.

I can click OK, and we get the link to the workbook. Now if I click it, the file opens. I don't want to do anything in it right now; I just wanted to show you that the hyperlink did in fact work. So I'll close this file, and go back to the Hyperlinks workbook. Now, you'll notice that links that have been followed are in purple, as opposed to in blue, whereas links that have been followed have blue text. The final type of hyperlink I'm going to show you how to create starts a new e-mail message when you click it, and here I have the e-mail address for [email protected] And once again, I will press Command+K to open the Insert Hyperlink dialog box. And this time I want to click E-mail Address to display that page of the dialog.

So in this case, I want to type the e-mail address in Link to that is the resource to which we're linking, so that will be Olivia, and Excel added mailto to the hyperlink. So I'll just go over here and start editing again. It's [email protected] I also could have copied and pasted the text from here into the Link to box and done it all at one time, and then Excel would have added the mailto and a colon in front of it, but the way I did it works; it just took a little bit longer.

So I have the To here, and it's to the proper address. They just grab that value from here. And for the Subject, I'll just call this Workbook Link Message. So everything looks good. I'll click OK, and Excel creates the hyperlink. If I were to click it, and I don't have an e-mail program configured on this computer, but if I were to click it, it would run my default e-mail program and start a message that was addressed to the e-mail address [email protected]

If I want to edit a hyperlink, I can Ctrl+Click the cell that contains the link, and then in the menu that appears, click Edit Hyperlink, and Excel displays the Edit Hyperlink dialog box. It's exactly the same as the Insert Hyperlink dialog box. It just allows you to change things. So in this case, I won't actually make any edits, but if I wanted to, I could go to the Document page and select a new file, change the display text, anything like that. Actually, I will. I'll call it PivotTable Report. So when I click OK, the link changed, so we've gone back to blue, but it's still linked to the same file. Now let's say that I want to delete a hyperlink.

To do that, once again, you Ctrl+Click a cell that contains the hyperlink, and then from the menu that appears, click Remove Hyperlink. The hyperlink is gone, so if I click on the cell, instead of opening the file, because there is no link, it just selects the cell, and you can edit the contents, copy it, anything you like. Hyperlinks let you create connections to resources outside your active workbook. Clicking a hyperlink opens that other file or web site, enabling you to use that information immediately. Or, if you like, you can create an e-mail response form with a hyperlink inside an Excel workbook.