Excel PivotTable List Feature

1. Excel PivotTable List Feature Creating a PivotTable List Web Page Using Excel

2. Objectives • Save a worksheet list as a PivotTable list • Manipulate the view of a PivotTable list using a Web browser • Sort and filter data in a PivotTable list • Remove fields and add fields to a PivotTable list

3. Saving a Worksheet List as a PivotTable List • Insert the Data Disk in drive A. See the inside back cover of this book for instructions for downloading the Data Disk or see your instructor for information on accessing the files required in this book • Click the Start button on the Windows taskbar, point to All Programs on the Start menu, and then click Open Office Document on the All Programs submenu • When the Open Office Document dialog box is displayed, click the Look in box arrow and then click 3½ Floppy (A:) in the Look in list. Double-click the file name, Miles-To-Go Cabs • If the Excel window is not maximized, double-click its title bar to maximize the window • If the Language bar appears, right-click it and then click Close the Language bar on the shortcut menu

4. Saving a Worksheet List as a PivotTable List • If the Standard and Formatting toolbars are positioned on the same row, click the Toolbar Options button and then click Show Buttons on Two Rows • Click File on the menu bar • Click Save as Web Page on the File menu • When Excel displays the Save As dialog box, click the Save as type box arrow and then click Single File Web Page in the Save as type list • If necessary, click the Save in box arrow and click 3½ Floppy (A:) in the Save in list

5. Saving a Worksheet List as a PivotTable List • Type Miles-To-Go Cabs PivotTable in the File name text box • Click the Publish button • When Excel displays the Publish as Web Page dialog box, click the Choose box arrow and then select Range of cells in the Choose list • If necessary, type A:\Miles-To-Go Cabs PivotTable.mht in the File name text box • Click the Collapse Dialog button

6. Saving a Worksheet List as a PivotTable List • When the Publish as Web Page dialog box collapses, select the range B4:K18 on the Inspection Information worksheet • Click the Expand Dialog button • Click Add interactivity with in the Viewing options area • Click the Add interactivity with box arrow and then click PivotTable functionality in the list • Click the Change button

7. Saving a Worksheet List as a PivotTable List • When Excel displays the Set Title dialog box, type Miles-To-Go Cabs Inspection Information in the Title text box • Click the OK button • Click the Publish button in the Publish as Web Page dialog box

8. Saving a Worksheet List as a PivotTable List • If the browser opens, click the Close button on the right side of the browser title bar • Click the Close button on the right side of the Excel title bar • If Excel displays a Microsoft Office Excel dialog box, click the No button

9. Saving a Worksheet List as a PivotTable List

10. Viewing a PivotTable List Using a Web Browser • Click the Start button on the Windows taskbar, point to All Programs on the Start menu, and then click Internet Explorer on the All Programs submenu • When the Internet Explorer window appears, type a:\Miles-To-Go Cabs PivotTable.mht in the Address box and then press the ENTER key • If a new Internet Explorer window opens, right-click the first browser button on the Windows taskbar and then click Close on the shortcut menu

11. Viewing a PivotTable List Using a Web Browser

12. Using Field Arrows to Show Records that Pass a Test • Click the Van field arrow and then click N in the Vans list to clear the check box • Click the OK button • Click the AutoFilter button on the toolbar

13. Using Field Arrows to Show Records that Pass a Test

14. Adding a Summary Total to a Column of Data • Click the 2004 Fares data field to select the entire column • Click the AutoCalc button on the PivotTable list toolbar • Click Sum • If necessary, use the scroll bar on the browser window to view the entire PivotTable list

15. Adding a Summary Total to a Column of Data

16. Sorting Columns of Data • Click the Vehicle Age data field and then click the Sort Ascending button on the PivotTable toolbar

17. Dragging the Data Fields to the Drop Row Fields Here Area • Drag the Van data field to the Drop Row Fields Here area • Drag the City data field to the Drop Row Fields Here area and to the right of the Van data field • After viewing the PivotTable list, drag the Van and City data fields to their original locations in the data area, as shown in Figure 14 on page EX 738

18. Dragging the Data Fields to the Drop Row Fields Here Area

19. Dragging the Data Fields to the Drop Filter Fields Here Area and Applying a Filter • Drag the City data field to the Drop Filter Fields Here area • Drag the Garage data field to the right of the City data field • Drag the Route data field to the right of the Garage data field • Click the City data field arrow and then click Knowles, Minteer, and Westerly in the City list • Click the OK button

20. Dragging the Data Fields to the Drop Filter Fields Here Area and Applying a Filter • Click the Garage data field arrow and then click North in the Garage list • Click the OK button • Click the AutoFilter button on the PivotTable toolbar to remove the filter • One at a time, drag the City, Garage, and Route fields back to their original positions in the data area

21. Dragging the Data Fields to the Drop Filter Fields Here Area and Applying a Filter

22. Removing Fields from an Add Fields to a PivotTable List • Click the Field List button on the PivotTable list toolbar • Right-click the Cab No data field in the data area to select the entire column • Click Remove Field on the shortcut menu • Drag the Cab No data field from the PivotTable Field List window to its original location between the Route and Lname data fields

23. Removing Fields from an Add Fields to a PivotTable List

24. Closing the Browser • Click the Close button on the right side of the browser title bar to close the browser

25. Summary • Save a worksheet list as a PivotTable list • Manipulate the view of a PivotTable list using a Web browser • Sort and filter data in a PivotTable list • Remove fields and add fields to a PivotTable list