Transcript Class 09 Excel CA202.ppt

CA202
Spreadsheet Application
Creating Dynamic Lists
with PivotTables
Lecture # 9
1
Objectives
✔ Create dynamic data lists with
PivotTables.
✔ Edit PivotTables.
✔ Create PivotTables from external data.
2
Pivot Table
• An important consideration when you create your
Microsoft Excel worksheets is how you want the data to
appear when you show it to your colleagues.
• One limitation of the standard Excel worksheet is that
you can’t change how the data is organized on the page.
– For example, in a worksheet in which each column represents
an hour in the day, each row represents a day in a month, and
the body of the worksheet contains the total sales for every
hourly period of the month.
– You can’t easily change the worksheet so that it displays only
sales on Tuesdays during the afternoon.
• An Excel tool lets you create worksheets that can be
sorted, filtered, and rearranged dynamically to
emphasize different aspects of your data. That tool is the
PivotTable.
3
Pivot Table
• In this chapter, you’ll learn
– How to create and edit PivotTables from an
existing worksheet, and
– How to create a PivotTable with data imported
from a text file.
4
Creating Dynamic Lists with
PivotTables
• Excel worksheets let you gather and present important
data, but the standard worksheet can’t be changed from
its original configuration easily.
• Such a neutral presentation of your data is versatile, but
it has limitations. For example, you can’t reorganize the
contents of your worksheet so that the hours are
assigned to the rows and the days to the columns.
5
Creating Dynamic Lists with
PivotTables
• To create a PivotTable, you must have your data
collected in a list in which every row represents a cell in
the body of the finished PivotTable.
• The following datasheet shows the first few lines of the
list used to create the PivotTable just shown.
– Notice that every line of the list holds the Month, Week,
Weekday, Day, Hour, and Sales for every hour in the month.
Excel needs that data when it creates the PivotTable
6
Creating Dynamic Lists with
PivotTables
• Tip
– You can also move a field
head to an area of the
PivotTable by clicking the
field head, clicking the Add
To down arrow in the
Pivot Table Field List
dialog box, clicking the
area to which you want to
move the field, and then
clicking the Add To button
in the Pivot Table Field List
dialog box.
7
Creating Dynamic Lists with
PivotTables
• Tip
– The Drop Page Fields
Here box is used to
filter the contents of
the worksheet based
on the values in a
column from the
original data list.
8
Creating Dynamic Lists with
PivotTables
• Tip
– To return a
PivotTable to its
default formatting,
choose the
PivotTable Classic
Auto-Format, which
can be found at the
bottom of the list in
the AutoFormat
dialog box.
9
Editing PivotTables
• After you have created a PivotTable, you can
edit it to control how your data is displayed.
• Limit the data to one weekday i.e. Wednesday
10
Editing PivotTables
• Open a Pivot Table
– Click Show Field List button on the PivotTable toolbar.
– You can drag any field name from to the active PivotTable
– Dragging a field name to the Drop Page Fields here box doesn’t
change the data in PivotTable, but it does let you filter your
PivotTable based on the contents of the field.
– To remove a filter from a PivotTable, click the down arrow of the
field head used to filter the PivotTable, click (All), and then click
OK.
– Another way to modify is by changing the arrangement of field
heads while the PivotTable is open.
11
Creating PivotTables from External
Data
• While most of the time you will create
PivotTables from data stored in Excel
worksheets, you can also bring data from
outside sources into Excel.
• you can transfer worksheets from one program
to another by exporting the data from the original
program into a text file, which Excel then
translates into a worksheet.
• The text file is known as .CSV file (comma
separated variable)
12
Creating PivotTables from External
Data
• To import data from a text file,
you open the Data menu, point
to Import External Data, and
then click Import Data to open
the Select Data Source dialog
box.
• From within the Select Data
Source dialog box, you
navigate to the directory with
the text file you want to import.
Double-clicking the file
launches the Text Import
Wizard.
13
Creating PivotTables from External
Data
• The first page of the Text
Import Wizard lets you
indicate whether the data
file you are importing is
delimited or fixed-width;
fixed-width means that
each cell value will fall
within a specific position
in the file.
• Clicking Next to accept
the default choice
14
Creating PivotTables from External
Data
• This screen lets you
choose the delimiter
for the file, (Select
Comma) and gives
you a preview of what
the text file will look
like when imported.
15
Creating PivotTables from External
Data
• This screen lets you
change the data type
and formatting of the
columns in your data
list.
• Click Finish to import
the data into your
worksheet.
• Once the data is in
Excel, you can work
with it normally.
16
Creating PivotTables from External
Data
• The Import Data
dialog box appears
with the Existing
worksheet option
button selected and
=$A$7 in the Existing
worksheet box.
17
Chapter 9 Key Points
•
•
•
•
•
•
A PivotTable is a versatile tool you can use to rearrange
your data dynamically, letting you emphasize different
aspects of your data without creating new worksheets.
PivotTable data must be formatted as a list.
There are several AutoFormats available for PivotTables;
you’ll probably find one you like.
The PivotTable wizard walks you through the creation
process, but be sure you add the field that will provide the
data for the body of your PivotTable last.
Just as you can limit the data shown in a static worksheet,
you can use filters to limit the data shown in a PivotTable.
If you have data in a compatible format, such as a text file,
you can import that data into Excel and create a
PivotTable from it.
18