Excel PivotTable List Feature - PowerPoint PPT Presentation

Excel PivotTable List Feature

Creating a PivotTable List Web Page Using Excel

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Click the Vehicle Age data field and then click the Sort Ascending button on the PivotTable toolbar

• 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

• 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

• 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

• 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

• Click the Close button on the right side of the browser title bar to close the browser

• 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

Excel PivotTable List Feature Complete