Join Aaron Quigley for an in-depth discussion in this video Creating grade reports with mail merge, part of Office for Educators.
In this video, we're going to use the power of microsoft mail merge to create a very basic grade report that we can quickly print out and send home to parents of our students. To do this, we're going to take the grade report form, which is located in chapter four of the exercise files. And we're going to connect it to an Excel grade book, which is also located in chapter four of the exercise files. Before we get started creating our mail merge, let's go ahead and make sure our computer is set up to merge these documents properly. There's one setting we need to confirm is turned on. Let's go to the file section, choose options, under advanced we're going to scroll all the way down to the general settings section.
Here, there's a check box that says confirm file format conversion on open. We want to make sure that this check box is checked. Otherwise, if we try to bring a percentage in from Excel, it'll come in as a number, not a percentage. We're going to go ahead and click okay. And head back to our greater port form. To get started, the first thing we need to do is actually start the mail merge. Under the mailings tab in the ribbon, we can go ahead and click on start mail merge. Now we're not creating letters or envelopes, so we can go ahead and select a normal Word document. Our next step is going to be to attach the Excel spreadsheet, which is our grade book, to this Word document.
To do that, we're going to click on Select Recipients. We're going to use an existing list, it's going to prompt us to find the file and select the list. That file is on the desktop under Exercise Files > Chapter four. And it is our final grade book including student data. Here it's going to ask me to open a data source this is another very important step to make sure that all of the numbers and formats come through correct. We do not want to open up just a general database file we want to click the show all button and we're going to scroll down until we see.
Microsoft Excel worksheets. This is going to make sure that the formats we did in Microsoft Excel will also come through to our Microsoft Word document. I'm going to click okay. I'm going to open the entire spreadsheet. And I'm now able to use the data that's in my grade report within this document. To do this, I simply need to place the cursor where I want the information to be displayed. Right here I would like to have the student's name listed out. It's going to be a grade report for, and I would like the student's first and last name. To do this, I can go up to my insert Merge field under the Mail Settings tab on the ribbon.
If I drop down this menu, I can see that information from my Cell Gray book is already coming over. Here's the student's first name, last name, grade, and then it's continuing to also show me some assignments. For this particular section, I want to insert the students' first name and last name. If I click on First Name, I get the word First with double arrows on both sides. It's important to remember that, that entire place holder will represent whatever word. Flows through from the Excel spreadsheet. Which means if I just go up and go Insert Merge Field and Last, then my first name and last name are going to be put side-by-side without a space.
So I need to make sure to put my cursor between the two and add a space. So far, we have our grade report and it's going to list out the student's first name and last name. I'm also going to come down here to where it says To the parents of. I'm going to put my cursor right after the word of with a space. I'm going to go back to Insert Merge Field in the center of the Mailings ribbon and I'm going to select First Name. I'm then going to come down to the grade section. I'm going to put my cursor on the grade section, go back to the Insert Merge Field and I'm going to choose Grade. Okay, at this time I've gone ahead and inserted the first and last name of the student.
The first name again in the citation, as well as the grade under the grade section. To preview what this looks like, I can go up to the top of the screen and click on Preview Results. At first, it looks like nothing happened. The reason is, is our grade book has two rows where there's no information. To get to the first line of student information, I'm going to use the next record button, to the right of the number one, and I'm going to go ahead and go over to number three. Number three brings up my first student. Which is Jeff. Jeff's name is listed now at the top of the grade report. It says To the parents of Jeff.
And Jeff's grade of 99.25% is showing up. Now, I'm still only looking at a single document That doesn't have every student listed. To get every single student to flow through we're going to go ahead and finish this mail merge to do that we're going to click on the finish and merge document. We're going to merge and edit individual documents and here we have several options. I can simply click all and have every single row of Excel spreadsheet to come through. I know that the first two rows of this Excel gradebook do not have student data, so I'm going to go ahead and have it start on line 3, and it's going to go through line 37.
That'll make sure that every student data comes through, but I'm not going to try to find student data in the first two rows of that gradebook. I'm going to go ahead and click on OK. And here I have a new document that's opened up. It was titled Letters 1 and I could easily save this. And this document contains a single page for each student in my class. Jeff is the first one, Michelle Banks is the second one. And I can keep scrolling through and notice that every single student has their own page with their name listed out as the grade report. With it to the parents of their first name, and with their grades showing up.
Using this document, I could quickly go through and add comments. (SOUND) Once all of my final comments are added, and I've doublechecked my work, I can simply print this page out. And quickly distribute a great report to all of my students.
- Creating lesson plan templates
- Creating worksheets with math equations, charts, and graphs
- Grading papers
- Creating a gradebook in Excel
- Creating an animated presentation
- Setting up a school email account in Outlook
- Storing documents online with SkyDrive
- Creating a class website with SharePoint
Skill Level Intermediate
Q: This course was updated on 10/01/2014. What changed?
A: We added a brand new chapter on Office Mix, the PowerPoint plugin that allows educators to record interactive presentations and test students with quizzes.
Q: This course was updated on 10/15/2015. What changed?
A. We added videos for OneNote, OneDrive, and Office Online. OneDrive replaced SkyDrive as the cloud-based file service.