For years now, I've been using a combination of Microsoft Excel, Office, and Entourage to record student grades and then generate gradesheets for the students. After conversation with a few academic colleagues, I've realized that many people aren't aware of how helpful Office's mail merge functionality is for this process, so I thought I'd document what I've been doing.
At the beginning of the quarter, I grab the students' first and last names and RIT user IDs using our student records system. This is a little clunky, since there's no easy import--I have to copy and paste from the tabular data on the website and then clean it up in BBEdit before importing to Excel. That takes me 15-20 minutes, after which I can easily use Excel's concatenation functions to add columns for their email address (userid@rit.edu) and required website URL (http://people.rit.edu/~userid/imm/). I use the classlist spreadsheet to generate a web page with the student names linked to their URLs. I do this by using the mail merge function in "catalog" mode, like this:
I've got a standard Excel spreadsheet for each assignment that my students do. The first few columns are for their first and last names, email address, and URL, which I cut and paste from the classlist. The rest are for the various graded components of the assignment I'm grading--organization, design, content, mechanics, etc.
I grade the projects using the spreadsheet (which I can email to a grader if I'm having them do part of the work). Once all the projects are graded, I open up a Word document I've created that serves as the nicely-formatted gradesheet, with spaces for each of the individual point values, the total grade, and any comments from me or my grader. I used to then print these out and hand them out in class. Last year, however, I realized that I could use the merge function to send attached documents to email addresses, rather than outputting to the printer. Here's what the document looks like, along with the merge tool window. (Click on it to see a larger version.)
Once I've checked it over to make sure it's formatting properly (I can use the icon labeled ABC in the preview portion of the merge window to see what any given gradesheet looks like when output), I use the merge to email option in the last pane of the merge window. It gives me this dialog box:
Selecting "Mail Merge to Outbox" will generate email messages to each person in the classlist with an attached personal gradesheet, and will place these in my Entourage outbox. I can then check them over quickly before telling Entourage to send them.
I seriously considered switching to Google Docs this year for my grading, since it would have been easier to share spreadsheets with my TAs and graders. However, much to my amazement, Google's spreadsheets offer no mail merge functionality, so I had to scrap that plan. I'm excited to hear that the new Office Live may give me the ability to maintain my current workflow while also allowing me to share the spreadsheets with my graders.
Leave a comment