When I mark student assignments, I usually put the students’ marks and comments in a spreadsheet, then I do a mail merge to send out the marks to the students. I like doing it this way because then I’ve got a copy of all the comments and marks that I’ve given the students, and I can do some simple calculations to see how effective my teaching has been in different areas of the course material. I’m also more able to be sure that I’m giving similar comments and marks for similar work.
The only problem I have, though, is that when I do a mail merge, the formatting of my calculated numbers is thrown off, and the worst is the percentages. I know that there’s a way to format them correctly, but I can never remember how to do it, and the notes I’ve kept don’t seem to always work, and I’ve never quite got my head wrapped around the way it works. Until now!!!
I ran across this web page that really helped me to understand what’s going on. While it’s still fresh in my mind, I wanted to write a blog post explaining it so that the next time I need to do this, I’ve got something to reference, and hopefully it will be helpful for other people, too!
Note that I’m using Microsoft Office 2010, although that webpage is for Office 2003, so I’m sure it works with other versions.
Update, Feb. 25, 2014: In the comments below, “Anna” mentioned that she’s verified that this also works in Office 2013.
Let’s start off with a simple spreadsheet. I’ll assume I’ve got an assignment with 3 questions on it, each worth 10 marks. I’ll record the marks for the students in that spreadsheet, then have a column that calculates the total marks, and another that calculates the percentage. The spreadsheet looks like this:
Then, I set up a mail merge document in Word, add in the fields from the spreadsheet, and get a document that looks like this:
So that looks OK, but when I look at it in preview mode, I’ve lost all of the formatting on my percentage field:
The problem is that when I do a mail merge, the number is transferred from Excel to Word, but the formatting is not. The solution is to tell Word how I want to format this percentage field. To do that, I need to mess around with “field codes”. A better explanation is probably given here, but here’s a step-by-step description of how I do this.
First, we need to see the field codes. Press ALT-F9 to display the field codes (pressing ALT-F9 again will undo this). You’ll see:
We need to specify that we want to format this field differently. Place the insertion point after the “MERGEFIELD Pct”, and type in:
\# #0.0
The “\#” tells Word that it should format this field as a number, and the “#0.0” tells it which specific format it should use for the number. I find it a little confusing because the octothorpe character is being used for multiple things: both to say that this field should be formatted as a number, and to say how that number is to be formatted. Anyways, “#0.0” tells Word to use up to two numbers before the decimal point and to include a single zero if the integer part of the number is zero, and to use exactly one number after the decimal point, even if that number is 0. As I understand it, “#” means display the digit if you need it or a space if you don’t (when you’ve got a leading or trailing zero), and “0” means always display the digit. Anyways, our field now looks like:
Let’s look at how this gets formatted. Press ALT-F9, then turn off and turn back on the preview results. Now we see:
Well, that’s close, but it’s not quite right, obviously. The problem is that the number is 0.8, not 80 (a decimal number, not a percentage). To get it to display it as a percentage, we need to tell Word to multiply that value by 100. To do this, first we need to highlight the merge field. Highlight “MERGEFIELD” and the name of the field, as shown:
Once you’ve done that, press CTRL-F9 to embed this mergefield into another field. Now we’ve got:
Notice the extra set of curly braces around the mergefield. It is extremely important that the curly braces be added by pressing CTRL-F9; if you type in the braces manually, it’s not going to work.
Once you’ve got this merge field embedded in another field, we can tell Word to do a calculation with it. Add an equals sign in front of the merge field, and “*100” after it; this tells it to multiply the value of that field by 100, making it a percentage rather than a decimal. It should look like:
Now, press ALT-F9 to turn off the display of the field codes, turn the merge preview results off then back on, and then we should see what we want!
I hope this helps!!!