Transcript Microsoft Office 2003 Post-Advanced Concepts and Techniques Excel PivotTable List Feature Creating a PivotTable List Web Page Using Excel.

Microsoft
Office 2003
Post-Advanced Concepts
and Techniques
Excel PivotTable List
Feature
Creating a PivotTable List
Web Page Using Excel
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
2
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
3
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
4
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
5
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
6
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
7
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
8
Saving a Worksheet List
as a PivotTable List
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
9
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, rightclick the first browser button on the Windows
taskbar and then click Close on the shortcut
menu
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
10
Viewing a PivotTable List
Using a Web Browser
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
11
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
12
Using Field Arrows to Show
Records that Pass a Test
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
13
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
14
Adding a Summary Total
to a Column of Data
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
15
Sorting Columns of Data
• Click the Vehicle Age
data field and then
click the Sort
Ascending button on
the PivotTable toolbar
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
16
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
17
Dragging the Data Fields to the
Drop Row Fields Here Area
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
18
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
19
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
20
Dragging the Data Fields to the Drop Filter
Fields Here Area and Applying a Filter
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
21
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
22
Removing Fields from an Add
Fields to a PivotTable List
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
23
Closing the Browser
• Click the Close button on the right side of
the browser title bar to close the browser
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
24
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
Excel PivotTable List Feature: Creating a PivotTable List Web Page
Using Excel
25