Advanced Training for Microsoft Excel 2002

Publishing Data to the Web

Saving a Workbook as a Web Page

1 To save a workbook as a Web page, on the File menu, click Save as Web Page.
2 Notice that the Save as type box indicates Web Page. In the Save section, the default selection, Entire Workbook, is fine, so click Save.
3 To close Excel and then view the newly created Web page, first click the Close button at the top right corner of the Excel window (red button with a white X).
4 To launch Internet Explorer, on the Windows taskbar, click the Start button, then click Internet.
5 To locate the Web page, click the File menu, and click Open.
6 In the Open dialog box, click Browse.
7 Click My Documents on the left side of the window, double-click the Garden Company folder, and double-click DailySalesbyHour.
8 To open the file, click OK.
9 Since you saved the entire workbook, all the worksheets are present on the Web page. To view the March sheet, click the March sheet tab.

Publishing Worksheets as Web Pages

1 To begin publishing a worksheet as a Web page, click the File menu, then click Save as Web Page.
2 In the Save section of the Save As dialog box, click Selection: Sheet to select it, click the Add interactivity check box, and then click Publish.
3 To give the Web page a title, in the Publish as section of the Publish as Web Page dialog box, click the Change button.
4 In the Title box, type January Sales Data, and click OK.
5 To update this Web page every time you save the original Excel worksheet and then view the page in Internet Explorer, click the AutoRepublish every time this workbook is saved check box, click the Open published web page in browser check box, and then click Publish.
6 To reformat cell B5 directly in the Web page, click cell B5, then on the Interactivity toolbar (which is below the title January Sales Data), click the Commands and Options button, (second button from the right).
7 To align the contents of cell B5 to the left edge of the cell, in the Text format section, click the Align Left button (fourth button from the left).
8 To apply the format and return to the Web page, click the Close button at the top right corner of the Commands and Options dialog box.

Publishing a PivotTable as a Web Page

1 To begin publishing a PivotTable as a web page, click the File menu, then click Save as Web Page....
2 In the Save As dialog box, in the Save section, click Selection: Sheet.
3 To allow users to reorganize and filter the PivotTable's contents from within a Web browser, click to insert a check mark in the Add interactivity check box, and then click Publish.
4 In the Publish as Web Page dialog box, notice that the Choose box contains Items on Pivot, that is, the entire Pivot worksheet. To publish only the PivotTable, not the worksheet that contains it, in the list below the Choose box, click the item beginning with PivotTable.
5 Notice that in the Viewing options section of the dialog box, the value in the Add interactivity with box has changed to PivotTable functionality. To update this Web page every time you save the original PivotTable, click the AutoRepublish every time this workbook is saved check box.
6 To finish saving the Web page and have it immediately open in Internet Explorer, click the Open published web page in browser check box, and then click Publish.
7 To expand the PivotTable to show every cell, on the Interactivity toolbar (above the blue title bar of the PivotTable), click the Show Details button (6th button from the right).
8 To use the PivotTable's weekday filter, click the Weekday down arrow, then click the (All) check boxto deselect all the check boxes.
9 To display sales for only Saturdays and Sundays, click the check boxes next to Sat and Sun, and then click OK.
10 To view data from all weekdays again, click the Weekday down arrow, click the (All) check box, and then click OK.
11 To change data in the Excel PivotTable and check whether you see the change reflected in the corresponding Web page, on the Windows taskbar, click the Microsoft Excel taskbar button, and then, in the SalesRecord workbook, click the AllData sheet tab.
12 In the AllData worksheet, click cell F2, type 25000, and then press ENTER.
13 To update the data in cell D5 of the PivotTable, click the Pivot sheet tab, and then on the PivotTable toolbar, click the Refresh Data button (the Exclamation mark).
14 To have Web page updated with this change, on the Standard toolbar, click the Save button.
15 In the dialog box warning that changes are set to be republished to Web pages, click Enable the AutoRepublish feature, and then click OK.
16 To view the PivotTable Web page, on the Windows taskbar, click the minimized Internet Explorer window.
17 To update the Web page with changes saved to the Excel workbook, on Internet Explorer's Standard toolbar, click the Refresh button (fourth from left).
18 To view the change made in Excel (25000) reflected in the first cell of the 9:00 Sales column, on the PivotTable toolbar, click the Show Details button (sixth from the right).

Working with Structured Data

1 To begin saving an Excel workbook as an XML document, click the File menu, and click Save As.
2 To change the file type to XML, click the Save as type down arrow, click XML Spreadsheet, and then click Save.
3 A message box appears, indicating that any Microsoft Visual Basic projects or header or footer image associated with the workbook will not be saved, which is fine, so click Yes.
4 To close this file so you can reopen it in a Web browser, click the Close button (black X in the top-right corner of the Excel window).
5 To open DailySalesbyHour.xml in a browser and see the XML code that it contains, on the Windows taskbar, click Start, and then on the Start menu, click Internet.
6 Click the File menu, and click Open.
7 To locate the file, in the Open dialog box, click Browse.
8 To view all the files in the Garden Company folder, click the Files of type down arrow, and then click All Files.
9 In the list of files, click the XML file DailySalesbyHour (the page icon with a blue sphere in the center), and click Open.
10 To accept the specified file path and open the file, in the Open dialog box, click OK.
11 Notice how the data in the workbook has been translated into XML code. To view another XML file, Pivot, in the browser, click the File menu, and click Open.
12 To locate the file, in the Open dialog box, click Browse.
13 To view all the files in the Garden Company folder, click the Files of type down arrow, and click All Files.
14 In the list of files, double-click the XML file Pivot.
15 To accept the specified file path and open the file, in the Open dialog box, click OK.
16 To view this file as a spreadsheet by opening it in Excel, on the Windows taskbar, click the Microsoft Excel button.
17 To import the XML file into Excel, on the Standard toolbar, click the Open button.
18 In the list of files, double-click Pivot.