Transcript Excel Tutorial 5 PowerPoint

XP
Microsoft Excel 2002
Tutorial 5 – Working With Excel Lists
New Perspectives on Microsoft
Excel 2002 Tutorial 5
1
XP
Identify the elements of an Excel list
• Excel provides features that allow you to maintain
lists of information such as customer lists,
telephone lists, inventory lists, and so on.
• An Excel list is a collection of rows and columns
that contain similar data.
• In a worksheet, each column represents a field of
data and each row represents a record of data.
• The first row of the list always contains the name
of the fields and is called a field header row.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
2
XP
An example of an Excel list
This figure shows a
portion of a list of
vehicles. Notice that each
column in row 1 in the
list contains the name of
a field and that each row
represents a record.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
3
XP
Freeze rows and columns
• When you scroll through large amounts of data in a
worksheet, you can move data off the screen.
• If you prefer to have portions of data remain on the screen
at all times, such as the column and/or row headings, you
can freeze a portion of the list so that it remains while the
rest of the data scrolls.
• To freeze rows and columns:
– Click in a cell to select it
– Click Window on the menu bar, and then click Freeze Panes to
freeze the rows above the selected cell, and the columns to the left
of the selected cell
– Excel will display dark vertical and horizontal lines to indicate the
rows and columns that are frozen
New Perspectives on Microsoft
Excel 2002 Tutorial 5
4
XP
A frozen datasheet
In this figure, the window has been frozen below row 1 and to the right of column A.
When the user scrolls the
worksheet, the field names
and the ID # will remain on
the screen.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
5
Change zoom settings for
displaying a worksheet
XP
• Sometimes, it will be necessary for you to see
more or less data on a screen at a time.
• You can alter what appears within the screen by
changing the zoom setting.
– By default, the worksheet is set to display at 100%
• To zoom out (to see more), decrease the zoom
setting
• To zoom in (to see less), increase the zoom setting
New Perspectives on Microsoft
Excel 2002 Tutorial 5
6
XP
The Zoom dialog box
This figure shows the Zoom dialog
box. In this box you can choose the
zoom magnification you desire.
You can choose one of the
percentages in the list or you
can enter an amount you want
in the Custom zoom box.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
7
XP
A worksheet at 50% zoom
In this figure, the worksheet
has been zoomed to 50%.
Notice that the data is quite
small; however, you can see
a large amount of the data
within one view window.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
8
XP
Find and replace values in a worksheet
• The Find command allows you to search through
the data in a worksheet for a particular character
string.
• Optionally, you can choose to replace the
character string with another string.
• This procedure is called Find and Replace.
– For example, you might want to find every occurrence
of ACCT and replace it with Accounting
New Perspectives on Microsoft
Excel 2002 Tutorial 5
9
XP
The Find and Replace dialog box
This figure shows the Find and Replace dialog box. In the “Find what” text box you
would enter what you want to look for, such as ACCT. In the “Replace with” text
box, you would enter what you want to replace the value with, such as Accounting.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
10
XP
Sort data in a list
• Excel makes it easy to sort a list in ascending or
descending order based on any field(s) in the list.
• The field(s) selected on which to sort are called
the sort fields or the sort keys.
• You may choose to sort the data on a single field
or on a collection of fields.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
11
XP
Sort using a single sort key
The figure below shows a list of vehicles sorted on the Department Assigned
field. Since only one field is used in the sort, the Sort Ascending button was
pressed while the cursor was anywhere in the Department Assigned column.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
12
XP
Sort using multiple keys
If you want to sort on more than one field you will need to use the Sort dialog box, as
shown in the following figure. In this dialog box, you can select up to three fields to use
as sort keys.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
13
XP
Use a data form to enter, search for,
edit, and delete records
• Sometimes it is easier to view the data in a list
through a data form.
• A data form is a dialog box that you can use to
arrange data to view one record at a time.
• You can use the data form to display records, to
search for records, to modify records, and to delete
records from the Excel list.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
14
XP
An Excel data form
This figure shows a data form created for the Vehicles list. Notice that one record
only is displayed in the form. Also notice that there are buttons provided for creating
a new record, deleting a record, and for filtering records (i.e., the “Criteria” button).
New Perspectives on Microsoft
Excel 2002 Tutorial 5
15
XP
Filter data in a list using AutoFilters
• Sometimes you will want to see a portion of the records
instead of all of them.
• The processing of displaying only those records that meet
some criteria is called Filtering.
• When data in the list is filtered, records that do not meet
your criteria are hidden.
– These records are not removed from the list and, therefore, can be
redisplayed by removing the filter
• Simple filters can be specified by clicking the list arrow on
any field name cell.
• More complex filters must be created using the Custom
AutoFilters option.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
16
XP
AutoFilter options
In the figure below, you see the vehicles list with the AutoFilter option invoked.
Notice each field name has an arrow beside it. In the figure, you see that the list
arrow for the Make field has been clicked, revealing the filtering options.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
17
XP
Apply conditional formatting
to a range
• There are times when you will want data to have a
different appearance if it meets some criteria.
– For example, you might want data to appear in red, if the data is
more than six months old
– Or, you might want a value to be black if it is positive and red if it
is negative
• This kind of formatting is called conditional formatting.
• You specify the condition under which you want the
formatting to take place and what the formatting should be.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
18
The Conditional Formatting
dialog box
XP
In the figure below, you see the Conditional Formatting dialog box. In this
dialog box you would specify the condition(s) under which you want
formatting to apply. Notice the Add button on the dialog box. If you click
the Add button, you can specify additional conditions and formatting.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
19
XP
Insert subtotals into a list
• The data in a list can be summarized by adding subtotals to
the list.
• You can include summary information such as a count, a
sum, an average, a minimum value, and or a maximum
value.
• When the Subtotals command is applied to the list, a
subtotal row is automatically added to the list.
• You can specify that you want the subtotal(s) to apply to
the worksheet and/or groups within the worksheet.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
20
XP
The Subtotal dialog box
The figure below contains an
example of the Subtotal dialog box.
In this dialog box you can specify
where to add the subtotals, what
function to use in the subtotal, and
various other selections related to
subtotals.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
21
XP
An Excel datasheet with totals added
In the figure below, subtotals have been added for the Purchase Price field, which
are inserted whenever the value of the Department Assigned field changes value.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
22
XP
Use the subtotals outline view
• Previously you learned about creating subtotal
lines within a worksheet.
• Sometimes, it might be more beneficial to view
the summary information only.
• You can do this by displaying the data in Subtotals
Outline View.
• You may choose from Level 1, Level 2, and Level
3 outline view.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
23
XP
Totals displayed in Outline view
In the figure below, the list of vehicles has been shorted to a Level 2 outline. Notice
that only the subtotals and the Grand Total are displayed. Keep in mind that all the
data is still in the worksheet, it is just not displayed when the outline view has been
selected.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
24
XP
Summarize a list using a PivotTable
and a PivotChart
• Often, there is so much data in a worksheet that it is
difficult to analyze the data.
• A PivotTable report provides a meaningful summary of the
data in an organized manner.
• In the PivotTable, data is summarized into categories of
data.
• A PivotChart is associated with a PivotTable report.
• To create the PivotChart you must specify the data fields to
be included in the chart as well as the category fields.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
25
XP
An example of a Pivot Table
The figure below contains a sample PivotTable. Notice that the data is summarized
for each make of vehicle. The data includes summary data for the type of car within
each make. An additional column shows the Grand Total for each Make of vehicle.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
26
XP
Plan a Pivot Table
• Creating a PivotTable requires pre-planning of the
data you would like to see summarized in the
PivotTable.
• It is often a good idea to create a plan and even
sketch the desired table.
• This will help you decide which fields should be
placed in the row, column, and data placeholders
when running the wizard.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
27
XP
A Pivot Table plan
This figure shows a
sample plan for a
Pivot Table. It states
an overall goal for the
table, the results the
designer wants to see,
the information
needed to produce
those results, and the
method required to
obtain the desired
results.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
28
XP
Sketch your Pivot Table
Drawing a sketch of the Pivot Table can make the creation of the table much easier.
You get a visual image of the data you need and the results you want to display.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
29
XP
Modify a Pivot Table
• After the PivotTable is created you can change the
layout of the table.
• You may want to make changes to the formatting,
sort the data, add a field, or add a Page View.
• You can also easily turn the PivotTable into a chart
by clicking the Chart button while the cursor is
anywhere in the PivotTable.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
30
XP
An example of a Pivot Chart
This figure illustrates a
PivotTable that is
displayed as PivotChart.
New Perspectives on Microsoft
Excel 2002 Tutorial 5
31