Merging, at Undine's Request!

In the responses to my last post, Undine said she’d like to learn how to do a merge from an excel file to a word document.  So, here goes.  (excel and word are trademarked, of course.  I use them because my university buys a license and puts it on my campus computer, and I bought a license and have it on my personal computer.  Please respect intellectual property.)  It's a lot easier than it probably looks here.  Once you've done it a couple times, it's really easy.

First, set up your excel file.

I put names down the left, and then horizontally, the assignments I’m grading.  Here’s something REALLY helpful.  At the very top, put a very easy and recognizable “code” for each thing you want to be able to report.

Here’s an example from my senior seminar excel file.  (The Shakespeare file is too wide to use here because it has spaces for 17 word paragraphs):

KW here means a Keyword assignment.  KWT means the total of the keyword assignments and so on.

I enter grades on a 100 point scale, but you can use whatever scale you want, and set up the math so it makes sense to you.  (When I set up excel files for grades, I add in a “mythic 80 student” and enter 80% for every grade for that student.  Then I do the math in that mythic student’s row, so that I’m sure the math makes sense.)

Here's my Mythical 80 Student from the senior seminar:

If you’re doing a midterm report, rather than an end of semester report, add a vertical cell group column right after the final number grade.  Figure out what percentage of the overall graded work the students have done.  At this point, my Shakespeare students have done 50% of the graded work. Let’s imagine the final number grade vertical list column for your mythic student is AD35.  So you’ve added a new column in AE.  In the AE cell, type =AD35/50.  That will give me the current grade that the student has earned.  Label the top of that column something like MTG (for midterm grade).

Here's what this looks like on the right side of my Shakespeare class file:

Good.  Save the excel file for now.

Now, type up the report letter.  Here’s what my basic one looks like for Shakespeare.  The WPs are “word paragraphs”; students need to do 10 of 16 or so opportunities in the semester, so they add complication to the math at this point.  Question sets are an assignment where students write two questions they have.  They turn in three of these.  Then when we’re doing the research project, they’ve done some thinking and gotten some feedback about potential research questions.

Leave room in the letter for where you want information from the excel document to go.

Save that document in the usual way.

In that document in word, look at the top horizontal menu list, and find “mailings” (red).  Click.

Look in the next to top horizontal list, and in the second box, you’ll see “start mail merge” (orange).  Click.  Choose “normal word document.”  (And at this time, let me note that I’m no computer guru, so I’m probably using the simplest way possible because that’s what I was taught.)

Click on “select recipients” (blue) and choose “use an existing list.”  That will open up a browse box.  Find the excel document you saved earlier, and choose it.  Mine is on one sheet, so I choose that one sheet.

Next, click on “edit recipient list” (just to the right of the blue circle).  You’ll see a box that shows the excel file (in small size) and lets you unclick any horizontal sections you don’t want to send messages to.  Leave your students names checked (there are check boxes) and uncheck any others.  (If you look at my screen shot thing, I uncheck the bunch at the top, and anything at the bottom where I’ve put notes that aren’t student grades.)

Click to accept the changes.

Now in the word document, go to the space where you’ve left space for the student’s name.  Click “insert merge field” (green) and it will give you a vertical list from your excel file of the things you’ve got across the top horizontal line.   

So, if you’ve written “name” there, it should be easy to see.  Click on that “name” choice.  That should enter something that looks like two pointy arrows on either side of the code (blogger doesn't like pointy arrows); in the spot in the word document.  In this example, from F2 to F19 are word paragraphs and an extra credit slot.  If I scroll down, it looks like this and you can see my codes.  W Ct is the count of word paragraphs:

Do that for the rest of the letter, finishing with the new code that shows where the student stands grade-wise at this point in the semester.

You can see on my letter that I put a grade scale and show the math of my mythic 80 student.  That’s so students can see what I’m doing.  I’m not totally happy with my letter format, but I think it’s clearer than the old one.

When you’ve entered everything, you can preview results.  You can save the merged document.  Or you can click “finish and merge” (purple) and print out the whole thing (assuming you have a printer).  There’s also a way to email, but I don’t know how to do that.