Part IV: Using Advanced Excel Features

Working with Hyperlinks

A hyperlink is clickable text that provides a quick way to jump to other workbooks and files. You can set up hyperlinks to display files on your own computer, your network, and the Web. For example, you can create a series of hyperlinks to serve as a table of contents for a workbook. Or, you can insert a hyperlink that displays a Web page in the default Web browser.

Inserting a hyperlink

You can create hyperlinks from cell text or graphic objects, such as shapes and pictures. To create a text hyperlink in a cell, select the cell and choose Insert Links Hyperlink (or press Ctrl+K). Excel responds with the Insert Hyperlink dialog box, as shown in Figure 28.4.

FIGURE 28.4

Use the Insert Hyperlink dialog box to add hyperlinks to your Excel worksheets.

Select an icon in the Link To column that represents the type of hyperlink you want to create. You can create hyperlinks to a file on your hard drive, a Web page on the Internet, a new document, or a location in your current workbook. In addition, you can create a hyperlink that consists of an e-mail address. Then, specify the location of the file that you want to link to. The dialog box changes, depending upon the icon selected. If you like, click the ScreenTip button to provide some additional text that appears as a mouse-hover–activated ToolTip. Click OK, and Excel creates the hyperlink in the active cell.

Figure 28.5 shows a worksheet with hyperlinks that function as a table of contents for a workbook. Clicking a link activates a worksheet in the workbook. The example also shows an e-mail address that when clicked, activates the default e-mail program.

610

Chapter 28: Excel and the Internet

The appearance of hyperlinks in cells is controlled by two styles in the Style Gallery. The Hyperlink style controls the appearance of hyperlinks that haven’t been clicked, and the Followed Hyperlink style controls the appearance of “visited” hyperlinks. To change the appearance of your hyperlinks, modify either or both of those styles. See Chapter 6 for information about using and modifying document styles.

To add a hyperlink to a Shape, select the Shape and then choose Insert Links Hyperlink (or, press Ctrl+K). Specify the required information in the Insert Hyperlink dialog box, as outlined earlier in this section. (Read more about Shapes in Chapter 22.)

FIGURE 28.5

Hyperlinks in a workbook.

Using hyperlinks

When you hover your mouse pointer over a cell that contains a hyperlink, the mouse pointer turns into a hand. Click the hyperlink, and you’re taken to the hyperlinked document.

Tip

To select a cell that has a hyperlink with your mouse (without following the hyperlink), position your mouse over the cell, click, and hold for a second or two before you release the mouse button. Or just activate a nearby cell and use the navigation keys to select the cell that contains the hyperlink. n

When you hover your pointer over a Shape that contains a hyperlink, the mouse pointer turns into a hand. To follow a hyperlink from a Shape, just point to the Shape and click.

If the hyperlink contains an e-mail address, your default e-mail program launches so that you can send an e-mail to the address specified when you created the hyperlink.

611

Part IV: Using Advanced Excel Features

Using Web Queries

Excel enables you to pull in data contained in an HTML file by performing a Web query. The data is transferred to a worksheet, where you can manipulate it any way you like. Web queries are especially useful for data that is frequently updated, such as stock market quotes.

The term “Web Query” is a bit misleading. You can perform a Web Query on a local HTML file, a file stored on a network server, or a file stored on a Web server on the Internet. To retrieve information from a Web server, you must be connected to the Internet. After the information is retrieved, an Internet connection is not required to work with the information (unless you need to refresh the query).

Note

Performing a Web query doesn’t actually open the HTML file in Excel. Rather, it copies the information from the HTML file. n

The best part about a Web query is that Excel remembers where the data came from. Therefore, after you create a Web query, you can refresh the query to pull in the most recent data.

To create a Web query

1.Choose Data Get External Data From Web. Excel displays the New Web Query dialog box, shown in Figure 28.6. This dialog box is actually a resizable dialog box that functions as a Web browser.

2.Click links to navigate, or just type the URL of the HTML file in the Address field.

The HTML file can be on the Internet, on a corporate intranet, or on a local or network drive. Each table in the document is indicated by an arrow in a yellow box.

3.Click an arrow to select the table or tables you want to import.

4.You can also control how the imported data is formatted. In the New Web Query dialog box, click Options (upper right) to display the Web Query Options dialog box. Select the desired formatting and then click OK to return to the New Web Query dialog box.

5.When you’re ready to retrieve the information, click Import.

6.In the Import Data dialog box that opens, choose where you want to place the data.

The information on the Web page is retrieved and placed on your worksheet.

After you create your Web query, you have some options. Right-click any cell in the data range and choose Data Range Properties from the shortcut menu. Excel displays the External Data Range Properties dialog box, shown in Figure 28.7. These settings control when the data is refreshed, how it is formatted, and what happens if the amount of data changes when the query is refreshed.

To force a refresh at any time, right-click any cell in the data range and choose Refresh. The data in your worksheet is replaced by the latest of content of the Web page.

612

Chapter 28: Excel and the Internet

FIGURE 28.6

Use the New Web Query dialog box to specify the source of the data.

FIGURE 28.7

Use the External Data Range Properties dialog box to specify how Excel handles the imported data.

613