Using Excel and Mail Merge to Grade Efficiently

Hey all!

I have provided a few documents on the ELMS site that will help you more quickly and easily grade student work. This is especially useful during class presentations, so that you can grade on your laptop in the moment while students are talking. Once you get the hang of this method, it makes grading extremely efficient and easy--and it allows you to keep a log of how you scored your students across all categories on every assignment.

First of all, open up the provided Excel spreadsheet. I have created a category tab at the bottom for each assignment; simply select the assignment you are currently grading.

Then, enter your student names in the left-hand column.

If it helps you to enter scores, you can freeze the top row or the first column.

Next, enter the students' scores. You can do this while students are speaking, while you're looking over an outline, and so on.

The spreadsheet automatically calculates student scores. Double check my math, but this should automatically add up your students' scores properly so you don't have to fuss with all of that complicated basic addition. It should also calculate points for each part of the assignment.

The columns after the scores provide you a space to type in comments for students. Basically, as you scroll to the right you can add as many comments as you need to address.

Next, open up the provided Word document that corresponds with the assignment. You'll get a message like this; just click "yes."

The document should look like this. The << >> areas indicated places where fields in your Excel spreadsheet will auto-populate.

Once Word is open, go to the "Mailings," hit "Select Recipients," then "Use Existing List." You'll get a standard file-finding box; locate the Excel document there.

Once the document is selected, then choose the individual sheet in the file that corresponds to the assignment you graded (in this case, "Informative Interview").

If things work properly, you should be able to click "Preview Results" and scroll one or two sheets over to see what your merge documents look like. All of your spreadsheet information should auto-populate into the rubric when you preview. Check for any errors or discrepancies (again--I've never used this before so I may have made a small mistake somewhere!).

Finally, click "Finish and Merge" and "Edit Individual Documents." Scroll through for any discrepancies, weird blank pages, etc. Then print and give the grades to your students!

Voila! This looks complicated here, but I assure you--once you do the up-front work of designing the spreadsheets (which I've already done for you), this is a really efficient way to grade. Let me know if you have any questions or issues that need clarifying in the comments here.