Learn to Use your Computer

If you know that the users of your Web pages containing Excel data will be using Internet Explorer (Version 4.0 or later) to view them, you can make it possible for them to manipulate the data and make modest modifications to the worksheet data when viewing the pages in their Web browser. All you need to do to make this happen is to select the Add Interactivity check box in the Save As dialog box at the time you save the worksheet as a Web page.

The types of manipulations and changes that users can make to the spreadsheet data of an interactive Web page in Internet Explorer depend upon the type of data that the page contains:

• Worksheet data tables: Users can edit the cell entries and have the table's formulas updated either automatically or manually as well as modify the formatting of the cells in the table.

• Data lists: Users can sort and filter the data in the list as well as modify field entries and make formatting changes to the list.

• Pivot tables: Users can pivot the fields in a table as well as add new fields. They can also refresh the table data from the external data source (assuming that this source is accessible from the Web page), show details for any of the summarized data in the table, add calculated fields to the table, and page through the summaries by using different items in the Page Fields.

• Charts: Users can edit supporting data (shown beneath the chart as an attached data table) and have the chart automatically updated on the page.

Figure 1 shows you how a typical interactive data table appears on a new Web page after opening it in Internet Explorer 6. Notice that the interactive table is self-contained with a toolbar at the top, a facsimile of the worksheet row and column header at the top, and vertical and horizontal scroll bars on the right and the bottom. Notice also that this table uses gridlines to demarcate the cells and sports a sheet tab at the bottom, just like a regular Excel workbook window.

Figure 1: Interactive HTML worksheet opened in Internet Explorer.

The Office Web Components add horizontal and vertical scroll bars to the interactive table because you have no way to resize the table. You must use the scroll buttons to bring new parts of the data table into view on the Web page. Likewise, the row and column headers are automatically displayed to give you a way to widen or narrow the columns and heighten or shorten the rows by dragging the appropriate border of a column letter or row number.

Despite the obvious similarities to the Excel worksheet window, you can see some noticeable differences as well. The most significant difference is that the interactive spreadsheet table has no Formula bar or menu bar.

Without a Formula bar, you can't tell which values in the table are calculated by formulas and which are input as constants. Also, the only way to edit a table cell is by double-clicking the cell and then editing the entry there (at which time, you can immediately tell whether it's a value or a formula that you're editing).

Without a menu bar, you must pretty much rely upon the buttons on the toolbar to make changes that affect the entire table. The only other way to access commands that affect the table is by right-clicking one of the table cells to display its shortcut menu. The items on this shortcut menu duplicate the functions of the buttons at the top of the table, with the exception of the Insert and Delete items. These menu items both lead to the Rows and Columns submenu options that enable you to either insert or delete the columns or rows that are currently selected.

To make up for the lack of a menu bar, the toolbar above the interactive worksheet contains a Commands and Options button. When you click this button, a Commands and Options dialog box with four tabs — Format, Formula, Sheet, and Workbook — appears, as shown in Figure 2.

Figure 2: Use the controls in this dialog box to make changes to an interactive worksheet table.

These four tabs enable you to make the following types of changes:

• The Format tab contains buttons and boxes for changing the font, size, alignment, border, and cell and text color of any cells that you've selected.

• The Formula tab contains boxes that enable you to see the contents (very helpful when dealing with long formulas) and value of the active cell, along with all the range names defined in the table. You can even use its Define button to define a new range name for the current cell selection.

• The Sheet tab is divided into Find What and Show/Hide sections. To find some text or values in the table, enter the search text in the Find What text box and then click the Find Next button (using the Match Case and Entire Cell Only check boxes if you need to refine the search). Select or deselect the Show/Hide check box options (Row Headers, Column Headers, Gridlines, and Display Right to Left) to control which interior table elements to display or hide.

• The Workbook tab is divided into Calculation, Show/Hide, and Worksheets sections. Use the Manual and Automatic option buttons to switch from automatic recalculation (the default) to manual recalculation (in which case, you click the Calculate button to update table formulas).

Use the Show/Hide check box options (Horizontal Scrollbar, Vertical Scrollbar, Sheet Selector, or Toolbar) to control which overall table elements to display or hide. (Note that you can't remove the scroll bars when the table is too large to show all the data in the table.)

Use the Sheet Name text box to rename the table sheet that's selected in the list box below, the Insert button to insert a new sheet into the table, the Delete button to remove the current sheet, and the Hide button to hide the current sheet in the table.

Use the Order buttons to move the current sheet ahead of (with the button with the upward-pointing arrow) or behind (with the button with the downward-pointing arrow) the other sheets in the table.

Unfortunately, you can't save any of the changes that you make to an interactive Web page in Internet Explorer. The only way to save any formatting or editing changes you make to an interactive data table, data list, or pivot table is to export the page back to Excel as an XML (Extensible Markup Language) file and then save the changes there.