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!!!
This solution just saved me! I tried a couple of other ways I found online, but this one rocks! I used it to produce mail merge letters that included merit increase % – works like a charm. Many thanks.
This was awesome!!! Thanks for your help, it works on 2013 too 🙂
Hey, great Anna! I’m glad it worked for you, and thanks for letting me know it works on 2013, as well!
helped me out a lot, thanks
I just tried this, and at the end pressed alt F9 and the formula disappeared, am I missing anything?
Hi Clare, did you remember to turn “Preview Results” off and then back on? It doesn’t seem to show the updated numbers until you’ve done that. The other thing to check would be to press ALT-F9 again and check to make sure that your formula is still there — I know I’ve accidentally hit the space bar sometimes when I’m pressing the ALT key, and that’ll remove what you’ve got highlighted.
I just spent hours trying to get percentages to work properly in a mail merge. I went through countless blogs and help sites with no luck. Thank god I found this one. It works! THANK YOU so much for your perfectly detailed explanation. This was truly “Percentages for Dummies”!
Great, Reyne! I’m glad I was able to help you out!
Shane
I second that!!! thank you!!
You are totally awesome for writing this. 🙂
Thanks!
You did an excellent job of resolving a particularly vexing problem. All the steps were there and very clearly explained to someone who has used Excel/Word a lot but had no idea what field codes were or how to use them.
Thanks very much. Very glad I found your post.
Great, Steve! Thanks for your kind words — I’m glad I could help you out!
Shane
This is very helpful, however, I am running into one slight issue! when I use ” *100\##0.0 ” to change the format of the decimal to a percentage (1.0 to 100%) it doesn’t seem to work. The end result looks like 1.0*100%
Any idea why this is happening?
Sorry to not get back to you for so long … I was away for a while!
I think I figured out why you are seeing what you’re seeing. It involves the placement of where you’ve got the “*100”. I can recreate your problem by placing it outside of the mergefield:
({ MERGEFIELD Pct \# #0.0 }*100 %)
You want to be sure that you add in another set of curly braces using CTRL-F9, and add in the *100 there:
({ ={ MERGEFIELD PCT }*100 \# #0.0 } %)
Can you see the difference? Be sure to use CTRL-F9, though, because if you just type in the curly braces, it won’t work.
I hope this helps!
Shane
Thanks, Shane, this is great!
A possible solution for those who see the formula disappear after pressing ALT+F9. I turn the Preview Results on, then off after editing the MERGEFIELD and before pressing ALT+F9. Thereafter, I press ALT+F9 and voila – the formatted percentage appears. One caveat: the percentage from the first merge record is then always on (the MERGEFIELD can’t be toggled on anymore), but the mail merge works.
In my case, I get the percentage sign to appear from the resolution of the mail merge field with the below format.
{ =100*{ MERGEFIELD MyField } \# “0%” }
Thank you!
You are very welcome! 🙂
Thank you Shane. Explained in the most simplest yet powerful manner. Thank God I didn’t had to wander a lot since this opened quickly in Google search.
I’m glad it was able to help you out!
Hey Shane. I am trying to get it to show me “0%”… rather than just showing nothing and leaving my “%” there. For example, I can get it to show me any numbers, but if the actual number in the spreadsheet is “0” it won’t do it when I put the \## in. Have you run into that?
Yes, I believe what you need to do is to put in \# 0 — the 0 gets replaced with the actual number. If you have # in there, it gets replaced with a space. That’s why I used #0.0 in my example, as it will always show at least one number before the decimal point and one after. Hope that helps!
Hey Shane – in the even that the percent is a full number what can you use to remove the ‘.0’ so that it only shows 80% instead of 80.0% ? I would only want to see the decimal if it is relevant such as 80.5% or 80.75%
Oooh, that’s a tough one! All I can find is how to get rid of the numbers, not the decimal point. If you use #0.#, that will display at least one number before the decimal point, and at most one after but none after if it’s zero. But, it will still show the decimal point: “80.” rather than the preferred “80”. I haven’t been able to find how to get it to skip the decimal point as well. Sorry!
Thank you so much for this. I’ve been trying to transfer a complex set of UK election results from Excel to Word for a book and this has saved me many hours of manual inputting! Absolutely brilliant.
I’m glad it was useful to you!
Excellent tutorial! This was extremely helpful as I struggle with this everything I produce commission statements.The answers from Microsoft worked once but that was it. Thank you.
Thank you for your kind comment!
You’re amazing! By far, the best tutorial I have read to resolve this issue.I appreciate your detail notes and screenshots.
Thanks for the nice comment! 🙂
Thank you!! Saved us today too! Appreciate you taking the time!
From Alyse and Cindie!
I’m glad it helped you guys!
THANK YOU SO MUCH!!!! I was about to explode with frustration!!! You have really helped me a lot!!! Thank you!!!
I’m glad you found it useful! This merge stuff can be really frustrating!
Thanks a lot Shane! One problem: how do I solve the problem when there is no value to multiply in the datasheet? In then gives a error code unfortunately.
I don’t think it’s possible to fix that solely in the Word document. What you probably need to do is to change your spreadsheet to handle the problem. The IFERROR() function may be what you’re looking for. For example, in my example, in cell G2, I’m calculating the percentage using the formula =F2/F5. If I wrap that in an IFERROR() function, I can specify a value to use if there’s an error in my calculation. So, if my formula is =IFERROR(F2/F5,0), and there’s an error in my calculation of F5 (maybe F5 is 0, causing a divide by zero error), it will just use the value 0, and then my mail merge works without error.
I hope this helps!
Shane
This document is a treasure and should be stored in the Pyramids of Giza. Thanks for this article and it has helped me fix an issue to the core. Neatly explained even for layman to understand it. If anyone cant understand or complains about this article then he is not even eligible to use Mailmerge.
🙂 You are very kind!!! I’m glad it helped you out.
Perfect! Thank you so much for sharing 🙂