Transcript Document

Colleague, Excel & Word
Best of Friends
Presented by:
Joan Kaun & Yvonne Nelson
College of the Rockies
Getting Started
• What data are we after ?
• The easiest way to retrieve it?
• What software will we use ?
– Colleague
– Excel
– Word
What will we try, mailing labels
for our employees.
Where will we go, into
Colleague, Uniquery [5].
Into “XMUP”
How do we find the queries?
not sure, use “…”
What query are we looking for?
16:NELSONYJ_YNLABELS
If you have access you can set this query up,
otherwise your programmer can do it.
If your query is successful it will show
selected records.
Once the query has run, where does it
take us?
Back to the main menu.
“XHLD” Look for your report.
“Detail” into your report “Labels” to
make sure the data is correct.
Now we want to “Kermit” the
file.
No, we don’t want to see kermit.
This is what we want to see.
To down load this file click on
“file” select “download”.
Save this file into a directory and you’re
done in Colleague.
Now we’re off to Excel, open it
up.
Let’s find the file we just saved and
open it up.
When you open the file you are given 2
options “Delimited” or “Fixed width”.
Select “Fixed width”, then click on “next”.
We want to make sure that the data in
each column is the proper fit.
If we look at the province and postal code
they are combined.
If we move the line, voila it adjusts
the columns. Now click on
“Finish”.
The data to work with.
Let’s look at the data.
Tip: If you move your mouse to the top of the 1 and in front of
the “A” click it selects your whole document then move the
mouse onto any column and when you get the line with 2
arrows double click it will adjust all columns to “best fit”.
Cleaning up the data base.
Delete blank lines and any columns
you do not need.
Insert a row on the top of your data
and label each column. These will
be your “Headers”.
Save the file.
Make sure you save this as an “.xls”
spreadsheet, then close Excel.
Using Word
Go to “Tools”, select “Mail merge”.
We now follow 1 - 3
1. You will now see main
document, “Create”, select this.
A drop down menu will then appear
click on “Mailing labels” and then
click on “Active window”.
2. Data source, select “Get
data”.
We then see another drop down
menu. Click on “Open data source”.
Go find the spreadsheet that we
saved in Excel.
When you click on your file the
following box appears.
Click on OK.
Yes, you want to set up this
document.
It now takes you right to “Label
Options”.
Decide which option works for you and
click on it.
We now create our labels.
Click on the “Insert merge field” you can now
pick and choose from the “Headers” you
previously saved.
3. Merge the data into the document.
You want to click on “Merge”.
It will give you different options.
Use new document, click on
“Merge”.
Yippee, labels.
QUESTIONS
Using pivot tables in Excel.
What is a pivot table and how
can it help me.
• A pivot table is a tool used for interactive data
analysis.
• We use pivot tables when we have numerous
questions on the data we have extracted.
• Examples
– total salaries
– number of employee
– total benefits costs
First we need the data, into
Colleague to “XMUP”.
Here is the query we will use to pull
off the payroll information.
Send this to the hold file, but make sure you
have typed NOHEAD under section 7.
And other options, page width and length
are “0”.
Another successful report!
“XHLD” – Lets look at the
report. Detail into your query.
The data looks grrreat.
Now we kermit the file.
Once again we want to
“Download” this file.
This is a big file and make
take a long time to download.
We’re back in Excel and we want
to open the file we just saved.
Don’t forget to open this as
“Fixed width”.
What are we checking?
That the columns capture the correct data,
and that they are formatted correctly.
Here is the data. What do we do
next?
Format and clean up the data.
We want to format this column
to “0” decimal places.
How would we insert 4 columns?
Move up to “Insert” and select “Columns”.
We are now going to break apart
column “B” into the correct general
ledger (GL) coding.
We are now in the first blank column.
Go into the “Function wizard.”
Look for “Text” and “Left” click on ‘Ok”.
Click on the text box, select “B1”
then the number of characters from
the GL we want would be “2”.
The result = “10”.
Move to the next cell. We want to use
the function wizard again and this time
click on “Mid”.
Text box will be “B1” start number will
be “3” number of characters “2”.
The top display line has the
formula.
We’ve moved to a new column what
do we do?
Use the function wizard and “Mid”.
What is the text box and what
number would we start at?
Text “B1” start at “5” and we want
the 6 digit account code.
Bravo!
We are on the last column. What do
we have left?
The last 4 characters, use “Right”.
Where do we get the text?
“B1”, and we want the last “4” digits.
At last we’re done!
Now, copy the formula down the columns.
Select “Data” and click on “Pivot
table”.
Make sure “Pivot table” is selected and
click on “Next”.
Does it cover the right “Range”?
If yes, click on “Next”.
It now asks you where you want the
pivot table.
Select “New worksheet”, then click on
“Layout”.
This determines how we want the
information reported.
We’re done. Click on “Finish”.
The results…
Back to the data sheet. Let’s try
another one.
Where do we go?
Into “Data”, and “Pivot table”.
Once again we have to make sure
we have the correct “Range” of
data for our pivot table.
Excel is so smart it now asks you if
you want to save memory by using
the same data source.
Of course, you’re going to say “yes”.
What option do we choose now?
We want to decide what to put on our
table so go into “Layout”.
We now have the “guts” of the
report, however we want to “Sum”
not “Count”.
We “right double click” on count,
choose “Sum” then go into “Number”
and “Format” the fields.
We’re done. Click on “Finish”.
The data formatted and as
requested.
Questions
How to reach us
[email protected] 250-489-8223
[email protected] 250-489-2751 (305)