Section 26.3. Using Hyperlinks

26.3. Using Hyperlinks

Web browsers aren't the only programs that use hyperlinks the underlined pieces of text that let you easily travel around the Web. In fact browsers weren't even the first. (Most believe that honor goes to the ambitious, 1960s-era cataloging project called Xanadu, which was never finished.) You may be surprised to find out that hyperlinks are quite useful in Excel, letting you link together different types of content and even navigate large spreadsheets. Here are three common examples:

  • You can create a hyperlink to a Web page . In this case, Excel opens your Web browser in a new window and points it to the appropriate page.

  • You can create a hyperlink to a different type of file . You can link to a Word document or a PowerPoint presentation, among other things. In this case, Excel opens whatever program is registered on your computer to handle this type of file. If you have a link to a .doc or .docx file and you have Word installed, Excel opens a new Word window to display the document.

  • You can create a hyperlink to another worksheet or another part of the current worksheet . This technique is helpful if you have a large amount of data, and you want the people using your workbook to be able to quickly jump to the important places.

In Excel, you can place a maximum of one hyperlink in each cell .

Note: Excel can create Web page hyperlinks automatically. If you type some text that clearly corresponds to a Web address (like text that starts with "http://" or "www."), Excel converts it to a hyperlink. When you're done typing, a smart tag appears, which you can click to undo this automatic adjustment and convert the cell back to ordinary text.

26.3.1. Adding a Hyperlink to a Web Page or Document

To insert a hyperlink into a worksheet, follow these steps:

  1. Move to the cell where you want to place the hyperlink .

  2. Choose Insert Links Hyperlink (or press the shortcut key Ctrl+K) .

    The Insert Hyperlink dialog box appears, as shown in Figure 26-7.

    Figure 26-7. In this example, someone's about to create a new hyperlink. It'll appear in the worksheet with the text "Click here for company information" (which, of course, you can edit to say anything you want) and will take the clicker to the Web site

    Tip: You can also create a hyperlink on a picture object, so that the Web page opens when you click the image. To do so, right-click the picture box, choose Hyperlink, and then continue with step 3.
  3. Click the Existing File or Web Page option on the left side of the dialog box .

    You can also use the Create New Document option to create and link to a new file in one step. The trick is that you need to remember to add the correct file extension. If you want to create a new Word document, you need to make sure you add .docx to the end of the file name so Windows knows what program to use for viewing and editing the file. If you want, select the "Edit this document now" option to open the file immediately in the appropriate program.

  4. At the top of the dialog box, in the "Text to display" text box, enter whatever you would like the link to say .

    Common choices for the text include the actual Web address (like or a descriptive message (like "Click here to go to my company's Web site"). If the current cell already contains text, that text appears in the "Text to display" text box. If you change it, the new text replaces the current cell contents.

  5. If you want to set a custom tooltip for this hyperlink, click the ScreenTip button. Type in your message and click OK (see Figure 26-8) .

    A custom tooltip is a little yellow message- bearing window that opens above a hyperlink when your mouse pointer hovers over the link. If you don't specify a custom tooltip, Excel shows the full path or URL.

    Figure 26-8. A tooltip appears telling you the target Web address, file path, or worksheet location. Optionally, you can replace this tooltip with a custom message when you create or edit the hyperlink.

  6. If you want to add a link to a document, then browse to the appropriate file and select it. If you want to add a link to a Web page, type the URL address into the Address text box .

    If you're adding a link to a document, Excel sets the address to the full file path, as in C:\MyDocuments\Resume.doc . You can type this path in manually, and if your network supports it, you can use UNC (Universal Naming Convention) paths that point to a file on another computer using the name of the computer, as in \\SalesComputer\Documents\CompanyPolicy.doc .

    Note: You're free to use files on your computer or those that are stored on network drives . Just remember that when you click the link, Excel looks in the exact location you've specified. That means if you move the target file to a new location, or you open the worksheet on another computer, Excel won't be able to find it.
  7. Click OK to insert the hyperlink .

    When you insert a hyperlink, Excel formats the cell with blue lettering and adds an underline, so it looks like a Web browser hyperlink. However, you can reformat the cell to change its appearance by selecting Home Font section of the ribbon.

To use a hyperlink, just click it. You'll notice that the mouse pointer changes to a pointing hand as soon as you move over the hyperlink. If you want to move to a cell that contains a hyperlink without activating it, you can use the arrow keys, or you can click and hold the cell for about one second.

26.3.2. Adding a Hyperlink to a Worksheet Location

Hyperlinks also make for helpful navigational aids. If you have a worksheet with multiple tables of data, you can use a hyperlink to jump to a specific cell. You can also use a hyperlink to jump from worksheet to worksheet.

To create a hyperlink that uses a worksheet location as its target, follow these steps:

  1. Make note of the location you want to use for your target .

    A worksheet hyperlink always uses a cell reference that points to a single cell. If you want to move to a new worksheet, you can use a reference like Sheet2!A1 (which moves to cell A1 in Sheet2). You can also link to named cell references, so you may want to define a name for the appropriate section of your worksheet before you continue. (For more information about named references, see Section 13.2.)

  2. Move to the cell where you want to place the hyperlink .

  3. Choose Insert Links Hyperlink (or press the shortcut key Ctrl+K) .

    The Insert Hyperlink dialog box appears.

  4. Click the "Place in This Document" option on the left side of the dialog box .

    Excel displays a tree that represents the layout of the current workbook. When you link to another location in the workbook, you need to supply a cell reference (see Figure 26-9).

  5. If you want to jump to another worksheet, select the worksheet from the list .

    All the worksheet names in your workbook appear under the Cell Reference heading.

  6. Type in the cell reference in the "Type the cell reference" text box .

    Excel jumps to this location when somebody clicks the link.

  7. At the top of the dialog box, in the "Text to display" text box, enter whatever you would like the link to say .

    Dealer's choice here.

    Figure 26-9. Excel gives you a convenient tree that represents your workbook. You can choose any of the worksheets in your workbook, or you can choose a predefined named range (Section 13.2).

  8. If you want to set a custom tooltip for this hyperlink, then click the ScreenTip button. Type in your message and click OK .

  9. Click OK to insert the hyperlink .

    Admire your handiwork.

Tip: To edit a hyperlink, just move to the cell and choose Insert Links Hyperlink again (or press Ctrl+K). The same window appears, although now it has the title Edit Hyperlink.
26.3.3. The HYPERLINK() Function: Creating a Link on Your Own

Excel has one other hyperlink trick you can call on. Instead of using the Insert Hyperlink command, you can actually create a hyperlink on your own with the help of the built-in HYPERLINK() function. You just supply the function arguments that identify the target (like a Web page, the path to a file, or a cell reference) and the text that you want to appear in the cell. Heres what the function looks like:

 HYPERLINK(link_location, [cell_text]) 

Both the link_location and the cell_text arguments are text-based, so you need to use double quotes. Here's a formula that creates a hyperlink to a Web page:

 =HYPERLINK("", "Click to see company site") 

All this raises a reasonable questionwhy would you use a function to accomplish what you can polish off just as easily using Excel's ribbon? In most cases, the HYPERLINK() function doesn't add any benefit. However, the HYPERLINK() function really makes sense when you need to create dynamic links whose contents vary depending on what's in other cells .

Consider this formula:

 =HYPERLINK(A20, "Click to see company site") 

When you click this cell, the hyperlink sends you to whatever Web address you've entered in cell A20. Imagine you repeat this formula in several different places. All you need to do is change one cellA20to update all the links.

The HYPERLINK() function also gives you the chance to create some more complex conditional formulas (for more on conditional formulas, see Section 13.1). You may want to send the clicker to a different place depending on the content in other cells. Here's an example:

 =IF(B2="Acme Company", HYPERLINK("", "Click to see company site"), "") 

This formula actually generates a quirkybut potentially usefuldisappearing link trick. If cell B2 contains the text "Acme Company," the IF() function calls the HYPERLINK() function and inserts a new link to the company Web site. But if B2 contains something elseperhaps representing an invoice for another company Web sitea blank value appears in its place. It's not too often that you'll want to combine hyperlinks with these types of special effects, but when you do, the HYPERLINK() function is ready and waiting.

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: