IN THIS CHAPTER
Most people who use a computer are connected to the Internet. The web has become an important way to share, collaborate, 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 to gather and process data from the Web. This chapter covers topics related to Excel and the Internet.
Excel 2016 makes it easy to save your work to your OneDrive account or to a SharePoint site. Doing so lets you access the workbook from any computer that has Internet access, no matter where you are. And the computer doesn't have to have Excel installed. Sign in to your OneDrive or SharePoint account, and you can download the file and work on it locally or view it (and perhaps do minor editing) directly in your web browser using Microsoft Office Online.
To save a file to the Internet, choose File Save As, and then select OneDrive or SharePoint in the left section of the Save As screen. Click Browse, and choose a directory for the file. If you want, you can share the workbook with others, but only one person at a time can edit the file. You must be signed in to your SkyDrive or SharePoint account to save a file to one of these locations.
Figure 29.1 shows an Excel workbook that was saved to a OneDrive account. It's displayed in a browser using Excel Online. The file is a three-sheet workbook, with sheet tabs at the bottom. As you can see, the Excel Online includes a modified Ribbon, and it works much like the standard desktop version of Excel. Note the Open in Excel option, which opens the workbook in your desktop version of Excel.
Compared to the desktop version, Excel Online has quite a few limitations. For example, formatting options are limited, it can't run VBA macros, and it can't create pivot tables —but you can view and manipulate them. There's also a size limitation. If your file is too large, you can't open it with Excel Online. But overall, it's impressive software and can be useful if you tend to work from multiple computers.
Hypertext Markup Language (HTML) is the language of the World Wide Web. When you browse the Web, most documents that your browser retrieves and displays are 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.
You can save an Excel workbook so that it's viewable in a web browser. When you save an Excel workbook for viewing on the Web, you have two options:
*.mht
; *.mhtml
). Not all browsers can open these files.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 29.2 shows one of these worksheets.
Keep in mind that these files are intended to be displayed only by a web browser. They are not interactive files. In other words, the user can't make changes while viewing the file.
To save a workbook as an HTML file, follow these steps:
Figure 29.3 shows how one of the worksheets in the example file looks in a browser — Google Chrome, in this case. 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 webpage.htm
file, Excel created a folder named webpage_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, I discussed how creating an HTML file with Excel also creates a folder of additional files. The procedure for creating a web page that uses a single file is the same, except for step 4. In step 4, select Single File Web Page (*.mht; *.mhtml) from the Save as Type drop-down list.
Figure 29.4 shows the example file displayed in Internet Explorer. Other browsers may not support this file format or may require an extension.
Excel can open most HTML files, which can be stored on your local drive or on a web server. Choose File Open and locate the HTML file. If the file is on a web server, you'll need to copy the URL and paste it into the File Name field in the Open dialog box. Files opened directly from a web server are opened in read-only mode. The way 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 Cascading Style Sheets (CSS) for layout.
After opening an HTML file, you can work with the information using the normal Excel tools.
Another way to open an HTML file from a web server is to use Get & Transform, which is discussed in Chapter 38, “Working with Get & Transform.”
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 stored 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 your default web browser.
You can create hyperlinks from cell text or graphics 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). The Insert Hyperlink dialog box, shown in Figure 29.5, appears.
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 on 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 29.6 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.
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.
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.
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.
Excel makes it easy to e-mail your work to others. You can access the e-mail features from the Share tab of Backstage view. (Choose File Share.)
You can send the active workbook to one or more recipients via e-mail. The file can be the actual workbook, a PDF file, or an XPS file. If the workbook is saved to a shared location, you can send a link to the file (rather than the actual file). An additional option lets you fax the workbook (assuming that your system has a fax service provider).
The final topic in this chapter deals with Office Add-ins. An Office Add-in is an embeddable object that resides on a worksheet's draw layer. Some Office Add-ins take the form of a task pane. Office Add-ins are download from the Office Store, and most of them are free. To access Office Add-ins, use the tools in the Insert Add-ins group.
Note that these Office Add-ins have nothing at all in common with traditional Excel add-ins that are created in VBA. For example, see Chapter 45, “Creating Custom Excel Add-Ins.” In Excel 2013, this feature was referred to as Office Apps. I don't know why Microsoft used the same name for two entirely different features.
A wide variety of Office Add-ins are available, and some of them are useful. Note that the quality varies, and there is little standardization in terms of user interface.
Figure 29.7 shows an Office Add-in called Geographic Heat Map, which displays a U.S. map, with colors to indicate values.