42 thoughts on “Grade Reports: Merging Excel and Word

  1. Sue – Thanks for the great “tips” – this one looks like something I can really use!
    Sally

  2. Good tutorial Sue. Instead of shrinking the number you can also use a formula to convert it to a Percent. After toggling your Field Code, select your mergefield and press Ctrl-F9 to embed an additional field. Next enter the following formula: {={MERGEFIELD YourField}*100 \# 0%}

    Cheers!

  3. Thank you, thank you, thank you!

    I’m not a whiz with Word (in fact, I rather hate it, but ….)

    I’ve been hand-writing these things and I figured that there *had* to be a way to generate them electronically. I just want to have it generated on “index card” or “mailing labels!” I have 125 students, and that would be a lot of paper to generate a full letter-sized report each time!

    I have 3 lecture classes and 5 labs — and I keep all the grades in the same Excel file in different tabs. Will this work with that, or will I need to save each tab as a single file first? (not that that’s a problem — when I get them uploaded to WebCT [our version of Blackboard] I have to do that and save as a .csv file!)

  4. We’re not allowed to send grades over email. TPTB don’t feel like it’s a Secure Venue ™.

    I am so playing with this over the weekend.

  5. Great! I also keep my classes in the same Excel file, each one has it’s own worksheet (tab). When you tell Word which Excel file to link to, it will ask you which worksheet you want. So, no, there’s no need to put your classes into their own files.

    If emailing your students is an option, there’s no need to use any paper at all. If you want to print it on something smaller, in your Word grade report file, under ‘page layout,’ click the ‘size’ button to choose a different paper size. If your printer will print on index cards, go for it!

    Give it a try and let me know how it goes!

  6. Bwahahahaha! Phear my mad Word skillz! Of course, I had to lay it out in WordPerfect first! I’d be a really happy camper if I could figure out how to make Word’s help tell me what all the formatting things are!

  7. How do you change the line height/spacing and paragraph spacing for an entire document instead of having to change it for *EVERY* paragraph? Drives me nuts! Took me forever to figure out how to “right justify” *part* of a line like you do when you do a Table of Contents. Piece of Cake in WordPerfect!

    But MailMerge is much nicer in 2007 than it was in 2003!

  8. Thanks. With WP, everything from where I change settings has the new setting. The only time I need to select anything, is if I want to change it for a specific part of text.

  9. Do CTRL-A to select the entire document, then change the line spacing. All of your previous paragraphs will change to the new spacing, and anything new you type will also use that new line spacing. Once you’ve set up a document, you can save it as your default ‘style.’ A quickie blog post is coming soon on how to do that!

  10. HELP!!!!!!!!!!!!!!!!!

    So I got the files all set up for the Lecture Classes (I’ve got 3 lectures and 5 labs) — and I’m setting up the sheets for the 5 labs now; and I broke something.

    After “Selecting Recipients” and navigating to my spreadsheet, and clicking ok, I get a list of tabs. When I click on the proper tab for that particular lab section, I get another *similar* popup window titled “Select Table” only it’s empty. I can click on options and make it show me stuff — reselect the proper tab and I get the message “Word is unable to open the Data Source.”

    What does it mean? What did I do wrong? I got it a few times last night — but I was trying to open the wrong tab for the file — or at least I *think* that’s what I was doing wrong — but that got fixed!

  11. Hmmm… This happened to me once. I ‘repaired’ Office 2007, and it worked fine after that. The select table popup shouldn’t be there; I don’t think you’re doing anything wrong.

  12. Actually, I took all the files to school on my stick, and opened them up in Office 2003 — and everything worked just fine!

    Who Knows!

    Thanks!

  13. Fantastic! This is exactly what I needed. I just gave it a little test run on my OfficeMac ’08, and it worked perfectly. I think my school frowns on emailing out grade lists though. I’m not sure though–I’ll ask around. If not, I can just print the grade reports and hand them out in class which is just fine. Thanks for the tutorial!

  14. Great! I used to print them, and it worked fine. I’d put the student’s name at the very top in bold print and then would them fold the reports almost in half, leaving the student’s name showing. It made it easy to distribute without anyone inadvertently seeing the grades.

    This term I’m trying out Google Docs’ forms feature for short assignments. Students follow a link and answer questions. The responses are dumped into a spreadsheet. I download the spreadsheet and enter my comments. Then I do a mail merge to return the assignments. I’m really liking it so far.

  15. Dear Sue,
    I am replying 2 years aflter the post, but I am in trouble with keeping the Fomatting, which disappears with Mail Merge. Are you still looking at this?

  16. Nshuti, are you losing formatting you created in Excel? If so, the way around that is to do all of the formatting in your Word document. Or maybe I’m not understanding…?

  17. Yes, I lose the Conditional Formatting done with Excel. Before I do Mail Merge, some numbers in the excel sheet are BOLD and UNDERLINED, but when I am done with Mail Merge, the print preview and the print out , all the numbers look the same. I am not sure how I can do this in Word.Please assit.
    Thanks for replying.
    Nshuti

  18. In Word, bold and underline the merge fields. For example, if the field is called “points,” then in Word, bold and underline <>. That will bold and underline whatever is in that Excel column for all of final documents.

  19. I appreciate your time and attention to respond, but am not getting it. The bolding and underlining is done AUTOMATICALLY, depending on the VALUE within a cell in the excell sheet. That meas it is not a uniform case i.e, it is done according to a CONDITION. If I bold Merge field, it will be applied to every individual copy, while data contained in the cells differ.e condition to bold the number is to be below 10, all the numbers belwo 10 will be bold.But if I change one cell’s content to 11, the bolding disappears.

    More precisely, if th
    Will you persist to help me?
    Thanks again
    Nshuti

  20. Hi Sue! I found your article very helpful although I am still having a few issues with my excel sheet. I have done everything like you asked but mine isn’t working. Every time i get to the ‘insert merge field’, it keeps bringing out F1, F2, F3,F4 etc. It doesn’t show me my headings on my excel sheet. Is there something i am missing? i am trying to prepare a statement of result. please help!!!!!! thank you

  21. Hi! It sounds like you have an extra row at the top of the spreadsheet and that your column headings are actually in row 2. If so, delete row 1, and you should be good to go. Let me know if that was it. — Sue

  22. Hello,

    This looks like exactly what I need! I have been searching for a way of attaching unique attachments to each individual’s email and everything else I’ve found looks really complicated. This all looks very straightforward, Before I give it a go, can I ask a couple of questions please?

    1. If I want to Save the grade reports before sending, does it save each report as a separate file? Or all within one long file?
    2.One of the fields that I need to insert is a long list of open box comments. These are currently laid out as one row per comment (so multiple rows for each ‘student’) in a second spreadsheet. Whereas all the data is within one row per student, as in your example. When bring =ing across comments, does the mail merge work if there are line breaks? I had been told it wouldn’t. BUt to get them all in one cell, I would need to insert line breaks to present the comments as a list….. not sure what to do here.

    Thank you, this blog is so useful!!

    Sarah

  23. This is great! Do you know how to not show any rows that do not contain grades? eg we have around 30 subjects studied by students (and all these show in the excel spread sheet) but not all students have grades for all 30 subjects, so I only want to show on their report the subjects they have grades for?

  24. I love this post Please I have some questions. I’m working with excel spreadsheets for my school. I have done all the calculations but my problem now is to print each students report on a separate A4 sheet. Is there a way for me to do this automatically. I can attach the work I have done as an example. Please anybody to help? Thanks.

  25. I’m really concerned to know, does it create reports for all students in the excel sheet at once? Or should I make it n times if i have n students
    Plus can I save the reports as a pdf instead of mailing them?

  26. All of the students and their grades are in one Excel spreadsheet. When you merge that Excel file with a Word document, you will get one page in Word for each student. And, yes, you can save that Word document as a PDF.

  27. I had to do something similar for work and i was in a huge pickle. Thanks to your clear instructions I was able to accomplish the task!! Very grateful for you taking the time to post this! 🙂

Comments are closed.