This lesson will cover:

1
Analyzing Worksheet Data
2
Analyzing Worksheet Data

• This lesson will cover
• Understand Excel list
• How to
• Use Data Form
• Filter and Sort Data in a List
• Summarize List DataPivot Tables
• Analyze data in a list using a pivot table

These topics are from Chapter 8 of the text...
3
What is a List?
A List is a series of rows that contain similar
dataweve been using lists in many of the sample
worksheets. Each row is like a record in a
database as the data in each row pertains to a
specific item. Therefore, rows in a list are
called records and each column is called a field,
just as with databases. The column labels are
called field names. The block of cells that
contains the list or part of the list you want to
analyze is called list range Lists are used as
simple databases in Excel and can be manipulated
in several ways such as by sorting, filtering
and summarizing (as with Pivot Tables). The next
slide shows some of the guidelines that Microsoft
suggests when using a List
4
Guidelines for Creating a List
Enter field names as the top row of the list in
adjacent columns in a list. No empty or other
decorative rows should separate the field names
from the first record in the list Field names
should be unique for each list, which means there
are no two columns with the same label. Each
field name must be in a single cell Enter each
record in a single tow with each field in the
column corresponding to the correct field
name. Leave at least one blank column and one
blank row between the list and other data on the
worksheet Do not include any blank tow within the
list range. Do not use more than one worksheet
for a single list range Do not include more than
one list in a worksheet
5
Understand a Data Form
When you enter information in a list, you will
spend a lot of time moving around the worksheet
using the tab and arrow key on the keyboard.
Excel 2003 provides a Data Form feature for you
to enter data for saving both time and hassle. A
Data Form is a dialog box that displays one
complete record at a time.
6
Add Records Using a Data Form

• As you add new records to the form, Excel
  updates the list range with the new rows and
  automatically charges the list range to include
  them

7
Sorting a List
When you sort a list, Excel rearranges rows
according to the contents of a column you choose.
The main types of sorting are Ascending sort -
To arrange a list alphanumerically using the data
in one column, you can specify an ascending sort
order (0 to 9, leading spaces, punctuation, then
A to Z). Descending sort - To sort a list in
reverse order, use descending (Z to A,
punctuation, leading spaces, then 9 to 0) sort
order. For example, to sort a list of sales in
order from the highest to the lowest value, you
would sort the Sales column in descending order.
8
Sorting a List Default sort orders
Excel uses specific sort orders to arrange data
according to the value, not the format, of the
data. In an ascending sort, Excel uses the
following order. (In a descending sort, this sort
order is reversed except for blank cells, which
are always placed last.) Numbers - Numbers are
sorted from the smallest negative number to the
largest positive number. Alphanumeric sort - When
you sort alphanumeric text, Excel sorts left to
right, character by character. For example, if a
cell contains the text "A100," Excel places the
cell after a cell that contains the entry "A1"
and before a cell that contains the entry
"A11." Text and text that includes numbers are
sorted in the following order 0 1 2 3 4 5 6 7 8
9 (space) ! " ( ) , . / ? _at_ \
_ lt gt A B C D E F G H I J K L M N O
P Q R S T U V W X Y Z Logical values - In
logical values, FALSE is placed before
TRUE. Error values - All error values are
equal. Blanks - Blanks are always placed last.
9
To sort a list, select the Sort command from the
Data menu
10
This brings up the Sort dialog box. Here you can
add the conditions to your list sortthis example
selects a list sort by Boat Size in Ascending
orderno other criteria were set
11
The Art of Filtering

• Filtering is a way to find and work with a subset
  of data in a list. A filtered list displays only
  the rows that meet the criteria you specify for a
  column. Unlike sorting, filtering does not
  rearrange a list. Excel provides two commands for
  filtering lists
• AutoFilter - is a filter by selection, for
  simple criteria. Only data that matches the
  criteria is displayed, the rest of the list is
  hidden.
• Advanced Filter - uses more complex criteria and
  can actually copy the results of the filtering
  process to another location.
• When Excel filters rows, you can edit, format,
  chart, and print your list subset without
  rearranging or moving it.

12
To use the AutoFilter feature select the Filter
command from the Data menu, and then the
AutoFilter command
13
The AutoFilter command places dropdown list boxes
next to all the column headers. These dropdown
lists are for selecting the filter criterion.
For the first demonstration well select 1981
from the Year Built column
14
Using the Custom AutoFilter dialog you can select
from all of the the comparison operators on the
left
15
Creating List Subtotals
Excel can automatically summarize data by
calculating subtotal and grand total values in a
list. To use automatic subtotals, your list must
contain labeled columns and the list must be
sorted on the columns for which you want
subtotals. Basically you need to have a
correctly designed list that is sorted according
to your desired sub categories. For example,
if you have a list of sales by salespeople that
have a region as a field in each record and, you
want the regions sales subtotals with a listing
of the salespeople you would sort by region
before applying any subtotals. This groups all of
the records by region and is necessary for Excel
to provide subtotals. When you insert automatic
subtotals, Excel outlines the list by grouping
detail rows with each associated subtotal row,
and grouping subtotal rows with the grand total
row.
16
In the Data menu, you can find Subtotal, and
click it, the Subtotal dialog box is shown on the
screen.
17
Summarizing List Data Pivot Tables
A PivotTable report is an interactive table that
you can use to quickly summarize large amounts of
data. You can rotate its rows and columns to see
different summaries of the source data, filter
the data by displaying different pages, or
display the details for areas of interest. You
can create a PivotTable report from an Excel
list, an external database (Access or Oracle, for
example), multiple Excel worksheets, or another
PivotTable report. But you really need to see
one to understand it
18
to create a Pivot Table select the Pivot Table
and PivotChart Report command from the Data
menuthis action will display the Pivot Table and
PivotChart Wizard as shown herefor this example
well choose to use an Excel List or Database as
the source dataand select the PivotTable option
for the Kind of Reportthen we click Next
19
Step 2 of the wizard asks for the data to be
analyzedsince it has already selected all of our
list (note the list data is surrounded by the
dotted line) we click Next
selected data lies within this boundary
20
Step 3 of the wizard asks for the location of
the new PivotTablea new worksheet is always a
safe betso, we click Finish to display the
PivotTable there are other features that can be
accessed from this step (such as Layout and
Options), but they are not needed for this
example, and do require a little more skill to
use efficiently
21
we now have been moved to a new worksheet (named
Sheet1) that has a PivotTable layout on it. The
PivotTable toolbar is now displayedfrom the
toolbar you drag fields to the appropriate
locations on the PivotTables layoutfor example
dragging the Boat Size field to the Drop Column
Fields Here cell and the Location field to the
Drop Row Fields Here cell will change the
layout to
22
from here you can change the settings of the
currently selected fieldhere the Sum of Price
field was selected so you can change the
calculation for that fieldsay from Sum to
Countor Averageyou can also change the number
format, which really needs to be done for this
exampleto do this click the Number button to see
the Format Cells Number dialog boxchange the
format to currency in dollars and its done