This article describes how to make printable progress reports from an Excel spreadsheet using the Mail Merge function in Microsoft Word 2011, without much time or effort. Progress reports have been shown to produce small, short-term gains in rates of assignment completion for some students (Clariana, 1992).When distributed periodically, they can be an effective means to encourage students to keep up with their homework.
Some tech-savvy teachers make grades available to students online using learning management systems, such as Moodle or Blackboard. However, if students do not check their grades online, a supplemental paper-based progress report may be worth considering.
Get started with Word
Open a new document in Microsoft Word. Type any text that you want to appear on every progress report. For example, common items might include the course name, the date, and descriptive labels, such as Name:, Student Number:, Quiz average:, Homework average:, and Current total:, including a space after each colon.
Use Mail Merge Manager
Next, to link an Excel spreadsheet to the newly created Word document, click Mail Merge Manager from the Tools drop-down menu in Word. The pop-up menu shown on the left below will appear. The mail merge function is available in Word 2008 as well.
Proceed using the steps below.
1. Select Document Type: Create New > Form Letters
2. Select Recipients List: Get List > Open Data Source > Select your Excel grade sheet. When prompted, choose Entire Worksheet.
3. Insert Placeholders: From the Contacts tab, drag and drop the appropriate fields to the desired locations in your Word document. For example, drag and drop the 
Quiz_averagefield next to the respective descriptive label made prior to starting Mail Merge Manager.
Before continuing, highlight everything, make it bold, and adjust the font size so that it fills as much of the page as possible as shown in the example in Figure 2.
4. Complete Merge by clicking the Merge to Printer icon in row 6 of the Mail Merge tool palette.
Printing
Since the document was created using a form letter template, it is designed to print one set of data per page. Unless extensive feedback is being given, a full page for each student is probably unnecessary. In the printer settings, change the number of pages per sheet to four, six, eight, or nine. On a Mac, this can be done in Layout, found in the printer settings. On a PC, this can be done in the main printer menu by changing the Pages per sheet. Print one sheet to see how it looks before printing them all. Finally, retrieve the progress reports from the printer and cut them with a paper cutter. That’s it!
Adding conditional feedback
To automatically add written feedback that reflects each student’s grade, include conditional formulas in your Excel spreadsheet. For example, the conditional statements below will show “Please see me after class.” for any student with a score less than 70 in column F . “Keep working hard!” will appear for any student with a score of 70 or higher. “You are doing great!” will be added for any student with a score higher than 80.
=IF(F2<70,“Please see me after class.”,“Keep working hard!”)
=IF(F2>80,“You are doing great!”)
Time saving tip
To save time copying and pasting formulas in Excel, use the auto-fill function by selecting the desired formula to reproduce. Then, scroll over the bottom right corner of the selected cell. When the mouse icon changes to a solid black plus sign “+”, double click. The information in the selected cell will be copied and pasted to the cells below. Any referenced information will be automatically adjusted. So, the F2 in the formulas above would automatically become F3, F4, F5, and so on. This will allow the feedback statements to reflect each student’s grade.
Conclusion
If you are already keeping a record of your grades in Excel, progress reports can be created quickly without much effort. With automated conditional feedback, it becomes easier to give at-risk students extra support. This could make a difference for some of your students. Please give it a try.
References
Clariana, Roy B. (1992). Rate of lesson completion in computer-based instruction: The effects of progress reports on students of different ability. Journal of Computing in Childhood Education, 3(2), 127-36.
