Transcript Excel Tips - Fresno City College

Presented by Cheryl Sullivan
 Name
 Department
 What
do you want out of the training?
 Favorite food
 Why
use pivot tables—if you have a lot of
information that you need summarized, Pivot
Tables are critical
 The presentation will be in Excel 2007. All of
the information is similar for 2003. Pivot
Tables in 2003 are located on the Data menu
 In the following example, we will use a
Simple Query from Datatel to pull
information. For selected records we will
count the number of students in a zip code
 Get
Data From a query:
Type as shown. Then
press Okay out of every
screen.
 All
Pivot Tables must be in a range without a
break (you can fake it with a numbered
column out front like we did on the data
sort, but every column must have a header in
your range).
 The area containing the details must have a
header at the top
Okay
Not

In this example the data contains zip codes with
extra sub-sets. We will use “Text-to-Columns”
on the Data Tab to move that to the next row.
Use Fixed Width with use Delimited and use “-”
Make sure to add
an extra header
From (zip code together)
To (zip separtated)
 On
the Insert Tab in 2007, select the
Pivot Table button (located on the far
left)
 This
will activate the wizard
 On this first step make sure
that the range is correct for
the data you are trying to
capture
 Drag
the items you want in the Row Labels,
and then drag values you want summed or
counted into the values area. In this case I
wanted the
city in the
row and
zip for
both row
and
value.
 In
this example
I added many
more data
elements to
the Row Labels

Another example of formatting, to make the row
labels go out horizontally rather than vertically,
you must change the Report Layout to “Show in
Tabular Forms”
 To
get rid of the subtotal
information, right click on the
header(s) and uncheck the Subtotal

Run Query Builder

Verb=LIST; File: STUDENTS; Output= XNCOA.STU.CITY
XNCOA.STU.ZIP; Before = GET.LIST CS.LIST; change PC
settings and advanced settings. (see slide 5-2; hit
okay out of all screens
“Text To Column” the separating the Zip fields
 Select All Data in the Range (make sure to have
column headings
 On the Insert Tab hit “Pivot Table”
 Select your data in the pivot table wizard
 Change the Report view to “Show in Tabular
Format” on the Design Tab

 Question?
 What
would you like other training would you
like?
 If you have any positive or negative
evaluation points, please explain so that we
can make improvements.
 Thank you!