Colleague, Excel & Word Best of Friends

1. Colleague, Excel & Word Best of Friends Presented by: Joan Kaun & Yvonne Nelson College of the Rockies

2. Getting Started • What data are we after ? • The easiest way to retrieve it? • What software will we use ? • Colleague • Excel • Word

3. What will we try, mailing labels for our employees.

4. Where will we go, into Colleague, Uniquery [5].

5. Into “XMUP”

6. How do we find the queries? not sure, use “…”

7. What query are we looking for? 16:NELSONYJ_YNLABELS

8. If you have access you can set this query up, otherwise your programmer can do it.

9. If your query is successful it will show selected records.

10. Once the query has run, where does it take us? Back to the main menu.

11. “XHLD” Look for your report.

12. “Detail” into your report “Labels” to make sure the data is correct.

13. Now we want to “Kermit” the file.

14. No, we don’t want to see kermit. This is what we want to see.

15. To down load this file click on “file” select “download”. Save this file into a directory and you’re done in Colleague.

16. Now we’re off to Excel, open it up.

17. Let’s find the file we just saved and open it up.

18. When you open the file you are given 2 options “Delimited” or “Fixed width”. Select “Fixed width”, then click on “next”.

19. 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.

20. If we move the line, voila it adjusts the columns. Now click on “Finish”.

21. The data to work with.

22. 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”.

23. Cleaning up the data base. Delete blank lines and any columns you do not need.

24. Insert a row on the top of your data and label each column. These will be your “Headers”.

25. Save the file. Make sure you save this as an “.xls” spreadsheet, then close Excel.

26. Using Word

27. Go to “Tools”, select “Mail merge”. We now follow 1 - 3

28. 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”.

29. 2. Data source, select “Get data”. We then see another drop down menu. Click on “Open data source”.

30. Go find the spreadsheet that we saved in Excel.

31. When you click on your file the following box appears. Click on OK.

32. Yes, you want to set up this document.

33. It now takes you right to “Label Options”. Decide which option works for you and click on it.

34. We now create our labels. Click on the “Insert merge field” you can now pick and choose from the “Headers” you previously saved.

35. 3. Merge the data into the document. You want to click on “Merge”.

36. It will give you different options. Use new document, click on “Merge”.

37. Yippee, labels.

38. QUESTIONS

39. Using pivot tables in Excel.

40. 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

41. First we need the data, into Colleague to “XMUP”.

42. Here is the query we will use to pull off the payroll information.

43. 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”.

44. Another successful report!

45. “XHLD” – Lets look at the report. Detail into your query.

46. The data looks grrreat.

47. Now we kermit the file.

48. Once again we want to “Download” this file. This is a big file and make take a long time to download.

49. We’re back in Excel and we want to open the file we just saved.

50. Don’t forget to open this as “Fixed width”.