Most people who use a computer are connected to the Internet. The Web has become an important way to share and gather information from myriad sources. To help you with these tasks, Excel has the capability to create files that you can use on the Internet and also to gather and process data from the Web. This chapter covers topics related to Excel and the Internet.
HTML, an acronym for HyperText Markup Language, is the language of the World Wide Web. When you browse the Web, the documents that your browser retrieves and displays are usually in HTML format. An HTML file consists of text information plus special tags that describe how the text is to be formatted. The browser interprets the tags, applies the formatting, and displays the information.
Caution
In previous Excel versions, you could use HTML as a native file format. In other words, you could save a workbook in HTML format and then re-open the HTML file in Excel with no loss of functionality. You can no longer do so in Excel 2007. For example, all your formulas are converted to values. Therefore, if you save a file in HTML format using Excel 2007, make sure that you also save the file in a standard Excel format.
When you save an Excel workbook for viewing on the Web, you have two options:
An HTML file: Produces a static Web page, plus a folder that contains support files. You can create the HTML file from the entire workbook or from a specific sheet.
A single file Web page: Produces a file in the Web archive format (
*.mht;*.mhtml). You can view these files only with Microsoft’s Internet Explorer browser.
Note
Previous versions of Excel included an additional option to create an interactive Web page, using the Microsoft ActiveX Spreadsheet Component. Users could perform standard Excel operations directly in the browser. This feature was removed from Excel 2007.
These options are described in the following sections. Both examples use a simple two-sheet workbook file. Each sheet has a table and a chart. Figure 28.1 shows one of these worksheets.
To save a workbook as an HTML file, choose Office 
Save As. In the Save As dialog box, select Web Page (*.htm; *.html) from the Save As Type drop-down list and specify the Entire Workbook option. Name this file webpage1.htm. Click Save to create the HTML file.
Figure 28.2 shows how Sheet1 of the file looks in a browser. Notice that the workbook’s sheet tabs appear along the bottom, and you can switch sheets just as you do in Excel.
In addition to the webpage1.htm file, Excel also created a folder named webpage1_files. This folder contains additional files that must be kept with the main HTML file. Therefore, if you post such a file on a Web server, don’t forget to also post the accompanying directory.
In the previous section, you saw that creating an HTML file with Excel also created a folder of additional files. To create a Web page that uses a single file, choose File Save As. In the Save As dialog box, select Single File Web Page (*.mht; *.mhtml) from the Save As Type drop-down list and specify the Entire Workbook option. Name this file Webpage2.mht. Click Save to create the file.
Figure 28.3 shows the file displayed in Microsoft Internet Explorer.
Excel can open most HTML files, which can be stored on your local drive or on a Web server. Choose Office Open and locate the HTML file. If the file is on a server, you’ll need to copy the URL and paste it into the File Name field in the Open dialog box.
How the HTML code renders in Excel varies considerably. Sometimes, the HTML file may look exactly as it does in a browser. Other times, it may bear little resemblance (especially if the HTML file uses CSS for layout).
Hyperlinks are shortcuts that provide a quick way to jump to other workbooks and files. You can set up hyperlinks to jump to 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.
You can create hyperlinks from cell text or graphic objects, such as shapes and pictures. To create a text hyperlink, choose Insert Links Hyperlink (or press Ctrl+K). Excel responds with the Insert Hyperlink dialog box, as shown in Figure 28.4.
Select an icon in the Link To column that represents the type of hyperlink you want to create. Then, specify the location for the file that you want to link to. The dialog box changes, depending on the icon selected. If you like, click the Screen Tip button to provide some additional text that appears as a tool tip. Click OK, and Excel creates the hyperlink in the active cell.
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.
Figure 28.5 shows a worksheet that contains hyperlinks.
Here’s another way to create a link to a cell or range (which can be in any workbook):
Select the cell or range.
Choose Home
Clipboard Copy (or press Ctrl+C).Activate the cell that will display the hyperlink.
Choose Home
Clipboard Paste As Hyperlink.
Excel creates a hyperlink to the cell or range that you copied in Step 3. This cell or range can be in any workbook or worksheet.
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.
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. Or just activate a nearby cell and use the arrow keys to select the cell that contains the hyperlink.
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 will be launched so that you can send an e-mail.
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.
Note
Performing a Web query doesn’t actually open the HTML file in Excel—rather, it copies the information from the HTML file.
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, 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 Web browser. You can click links to navigate or just type the URL of the HTML file in the Address box. The HTML file can be on the Internet, on a corporate intranet, or on a local or network drive. Each table is indicated by an arrow in a yellow box. Click an arrow to select the table or tables you want to import.
You can also control how the imported data is formatted. In the New Web Query dialog box, click Options to display the Web Query Options dialog box. Select the desired formatting and click OK to return to the New Web Query dialog box.
When you’re ready to retrieve the information, click Import, and you get the Import Data dialog box, asking 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 select 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.
