Transcript Excel Tips - Fresno City College

For Datatel and other applications
Presented by Cheryl Sullivan
 Name
 Department
 What
do you want out of the training?
 Favorite drink
 This
example is based on a GLSA budget
report
 Tip 1- at a Datatel entry screen, if you are
trying to find multiple yet similar items
use…”
 This
brings back all of the similar items. If
you want them all. Click the Select All
Button
 Tip
2 When your output devices is H for
“Hold/Browse File Output” (best practice)
and you think you may want to see the
report again. Put a file name in the Banner
(format for the file should be your 3
initials.filename)
 To
get back to a file
with a saved Banner, go
to UTFB.
 For Directory File
Name
type: Hold (it will
automatically put in
“_HOLD_”
 For Item Names, type
the file name in CAPS
 Once
the report browser is up, you will
select the export button and save your file
wherever you choose.
 Open
Excel, change the type of file to “All
Files”, then find your file, and open it.
 The
“Text Import Wizard” will automatically
come up. For this kind of import we will use
“Fixed width,” and click “Next”
When picking where to put your column breaks
should go, scroll down so that you see the
column
headers.
 To add and
remove lines as
needed by clicking
and double clicking.
Always try to
put the lines at the
beginning of the
headers. In this
example I added
some between the
GL numbers.

 Step
3 of the wizard lets you change the type
of formatting for
the columns.
If you have
student ID’s
and want to
keep the leading
zeros, change
that column to
text.
 Then click “Finish”
 Run
Datatel report to “H- Hold” and name
your Banner
 Export the report to the desktop
 Open Excel and import file
 Put lines where wanted for column breaks
 Finish the import
 Practice pulling your report back up from
UTFB
 Leave Excel up for the next assignment
Times
10:00
1:00
2:00
3:00
4:00
5:00
6:00
7:00
8:00
9:00
UP
 First
“Select All” in Excel, by clicking the
most upper left square between the A
column and row 1.
 Then
double
click between
the Column heading
A and B (or any other two columns); make
sure the cursor image looks
like this:
; this will auto size the column
widths to the longest entry.
 To
make the information easy to sort
through, first add a column before A.
Number the column down using consecutive
numbering.

To use auto numbering by dragging, you must
first list the numbers 1,2,3; select all three
numbers; and then drag down by the bottom
right corner

(cont.)


To auto number you could also use the formula A1+1 in
cell A2 and drag this down. Occasionally the dragging of
numbers doesn’t work and this can be helpful.
If you use this method make sure when you are done
with the step above, to Copy Column A and right click
and Paste Special… and select “Values”. This copies the
formulas and only pastes back the number. This will
allow you to sort and easily put the numbers back.

To begin the filtering, first click on the
row that contains the row headers that
you would like to filter on. Next:

2007—Go to the Data Tab and click on the
filter button.

2003—Go to the Data Menu, click on Filter, then
AutoFilter
Now you can look at only the data
any way you want.
 In this example I selected
only the object code
94490


In this example
there are a list of
student IDs and when
you hit the down arrow button, the
menu to the right opens.

Select All is the default. To unselect
one, simply uncheck it.

To only select one or a few, check Select
All (which unselects them all) and then
select only the ones you want.
 When
using filtering the most powerful
formula to use is =Subtotal(“number from
below”, “beginning of range”: “end of
range”).
Ex: =Subtotal(9,A1:A342)
 Formula Values:



 This
1 = Average
2 = Count
3 = CountA



4 = Max
5 = Min
9 = Sum
formula will only calculate the numbers
that are showing in the selection
 Final


Filtering Tips
Put the subtotal above the filter
range so that it does not get
included in the filter and
disappear.
By having numbered Column A,
you can now resort your data by
various columns so that you can easily delete all
of the titles etc. that you may not want. After
you delete them, you can always put the report
back to the original by sorting on Column A.
 Auto
Resize the Excel columns from previous
exercise
 Add a new Column “A” and number the rows
in the column consecutively
 Apply the AutoFilter
 Put in the subtotal formula at the top of the
numerical fields: =subtotal(9,range)
 Filter on any variable you choose
Times
1:00
2:00
3:00
4:00
5:00
UP
Tired of running a report and Datatel logging
off while you are at lunch?
 You will run your report as normal, but on
the final page for Output Devise Select E
 Put in your
subject and
type in your
email and save
out as usual
 Now
you have a report, but in an unusable
format
 In Outlook

In the body of the email, copy all of the report
by holding CTRL+A to select all, then copy and
paste to Excel
 In


Excel
In Excel make sure to select only Column A.
Then begin the Text To Columns


2007-On the Data Tab click the Text-to Columns button
2003—Click on the Data Menu and click “Text to
Columns”
 You
use the Covert Text to Columns exactly
as you did in the previous example.
 The benefit of “Text To Columns” is that you
can select only certain ranges to change to
columns. This allows you to keep the
header at the top
of the Datatel
report in tack.
To do this you do
not select all of
Column A, but only the rows you want to put
into individual cells.
 Final


tip on Text-To-Columns
If you are using this feature to break-up first and
last names, make sure you have plenty of open
columns to the right for the information to go
into. You may need to insert extra columns.
This process will over write any previous
information that may have been in the field. If
you move this information into the last empty
column you will not have to worry about this.
 Log
in to Datatel and run any report
 Send it to your email
 Open your email
 Select all of the text in the body of the email
(CTRL+A)
 Paste into Excel
 Highlight areas in Column A to break into
columns. Do not include the header.
 Complete the “Text To Columns” using Fixed
Width
Times
10:00
1:00
2:00
3:00
4:00
5:00
6:00
7:00
8:00
9:00
UP
 When
you import a file with a negative
number it can be difficult sometimes to
convert them to a number format.
 If the number is in the format of “12.00-”
and excel needs “-12.00”, use the following
steps

Sort your numbers by the amount column you
want to change. Put a blank row before and after
your numbers (so you don’t change the wrong
ones.)

(Cont.)
Delete the “–” from
your selected cells,
using Search and
Replace. Search for “-”
and Replace with
nothing
 Type -1 in a blank cell
 Click on the -1 and
copy
 Highlight the selected
cells again, click paste
special, and select
“Multiply”

 Why
use pivot tables—if you have a lot of
information that you need summarized, Pivot
Tables are critical
 The rest of 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 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).
 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 our knowledge to
move that to the next row. Instead of using
Fixed Width with use Delimited and use “-”
Make sure to add
an extra header
 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
zip for
both.
 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”
 Run

Query Builder
Verb=LIST; 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 Zip fields
 Select All Data in the Range
 On the Insert Tab hit “Pivot Table”
 Select your data
 Click on Zip and select Students
 Change the Report view to “Show in Tabular
Format” on the Design Tab
Times
10:00
1:00
2:00
3:00
4:00
5:00
6:00
7:00
8:00
9:00
UP
 Question?
 What
would you like to see at next months
training?
 If you have any positive or negative
evaluation points, please explain so that we
can make improvements.
 Thank you!