How to use linking to create form letters?

Excel© is a popular spreadsheet that is a part of Microsoft Office. Spreadsheets are used to organize data and allow manipulation of the information. PDA’s using either the Palm or Windows platform can use Excel© of Excel© compatible files and make it possible to use the PDA as a grade book.  Once the data is recorded in class it can be transferred to your PC using HotSync.  Information is then readily available for analysis, reports, sorting, or easily setting teams based on a variety of groupings.  The following areas will be presented: why use a spreadsheet; an how to set a class template with worksheets for merging, attendance/participation, skill performance, projects, fitness scores, test scores, summary scores, scoring weights; and how to use this data with Word© as a merge file to generate individual reports.

There are shareware and commercial grade book programs available for Windows and Macintosh based systems.  Spreadsheets are a program that allows the development of a record keeping and data calculating file that can including charting and some database capabilities. Excel and Word are available and transportable between both Windows and Macintosh, so if you are using a different system at home you can still work on your grades.  The cost of add on grade book programs can range from shareware at about $20 to several hundred dollars for commercial programs.  If your school has a standard grade program, see if there is a version available for PDA's Many people have MSOffice on their computer and have no additional cost to use Word and Excel.  Spreadsheets look familiar to tables or grade books.  You can set up information in a grade book you create in a way that is meaningful for you

When using Excel© as a grade book I find it easiest to have a workbook for each class.  Each workbook contains many ”worksheets” with different information on each sheet.  The information should be linked to allow entry of information on one sheet and automatic updating of the information on all of the other worksheets as needed.  Using a template for your grade book provides a standard format that is used with only slight adjustment for each class.  The worksheets you might want to include are one for merging with Word, one for attendance, skills, fitness, and tests and projects.  Once you create a format that contains the information you want, use "Save As" to save the file as a template.  To use this you open the template file and then use the save as command to save this as an Excel file using the class name and import your class roster if you receive your roster electronically.

In setting up a workbook template here are some things to keep in mind: Start the data a few lines down to allow for comments about the worksheet; Enter names in two fields of last name & first name to allow ease of sorting; Use new worksheets for different information such as attendance/ participation, skill performance, fitness data, and tests, quizzes, projects and summary information. Using different worksheets for information allows easy data viewing.  Information can then be used with mail merge in Microsoft Word© to create reports of the class data for students, parents, and school officials.  With physical education programs in danger it is more important then ever that we document what our programs are doing.

There are two types of sharing data between files, embedding and linking.  Embedded data is a copy of the data that is pasted into another location in the form it was copied. You use the special paste function and select value.  Linking is data that is copied and placed in another location.  When linked data is modified in its source location, it will automatically update in the new location.  

When merging Excel data with a Word document it is easiest if the data to be merged is on the first worksheet with the field names in the first row.  When you set up the Word document use: Tools | Mail Merge | Create | Form Letter, to create a merge document.  Once you have started to create the word document use: Tools | Mail Merge | Get data | Open Data Source | MS Excel Worksheet | Entire Worksheet, to get the data from the Excel file.  The field names in the first row of the Excel file are now the data source.  These fields will be listed in a new box on the Word tool bar called, Insert Merge Field. 

Now as you write your letter, wherever you want to insert a merge field, you can. You will see the name of the field as, {{field name}}, inside double brackets.  When you have finished writing the letter you should save it.  You can then use: Tools | Mail Merge | Get Data, to make copies of the merge document with the appropriate names of the students and their scores and comments.  This will help your parents better understand your program.  Many people still don’t understand what is happening in physical education, as seen on the piece on HBO about dodgeball.  We can use technology to inform parents about our goals and program.

File for a class template, a sample form letter, and sample class data are available from mdumin@radford.edu via email.