Office 2013 Class #51: Fix Word 2013 Mail Merge Number Format Problem: Excel & Access Data Source

Office 2013 Class #51: Fix Word 2013 Mail Merge Number Format Problem: Excel & Access Data Source


Welcome to Office 2013,
class video number 51. Hey, this is our last
video for our office class. We’re going to talk
about mail merge. You want to download the
Excel workbook with grade information, the access
file with grade information, which is a zip file. You’ll have to unzip it. And then our letter to students. That’s a Word document. And we’re going to do mail
merge to connect a Word document to a database,
either in Excel or Access. All right. Here’s Windows Explorer. Here’s the files. This is our system of folders in
our class notes for mail merge. I’m first going to open
up mail merge Excel file. And take a look. We have a grade table here. Here’s Joe, and Joe scores,
total, and percentage. Here’s Phil, the grades,
the total, and percentage. Now notice this is
a proper data set. It has field names at the
top; name, email, score one, score two, percentage grade,
and then records in each row. Now this is a unusual table
in that the first record is showing us the
maximum possible points. But the rest of these
are regular records that we would see in
a proper data set. And here’s what we’d like to do. We’d like to send each
student a letter that shows them their
scores and their grade. So if we count, there would
be six different letters. But I don’t want to have to type
each one of those letters out. Now let’s go open up our Word
document, letter to students. And here’s the amazing
thing about mail merge; we can tell
this single the letter, this is only one letter. We typed it only once. But we can tell
this letter to go look at this proper
data set, or a database, take each one of the records
and for each record print out a single letter. All right. Let’s start over in Word. Here’s how you do it. In essence, we’re going
to put our cursor, and let me zoom in on this. Right there it should
say dear, student name. So right there I’m going to
say hey, that spot in Word, please go look at the name
field over in that grade table. Then I’m going to
click here and say hey, right in that spot
on the Word document, go and look at score one. Once we connect the
letter to each one of the fields over there
in that Excel table, we can click the Print
button or the merge button and it will print out
an individual letter for each one of the records. Even though there’s
only one letter here. Now, before we do our mail
merge, I want to do two things. The first is I want to go
back over to our Excel file and close it. We do not want to
have this open. This is our record source,
our proper data set. So I’m going to close that. And the second thing
is I want to save this. Right now, it says
letter to students. I want to hit F12, save as. And at the very beginning I’m
going to say ExcelNumber01- So, ExcelNumber01 because
we’re going to do two mail merges in Excel and
then one from Access. Letter to students. And click Save. All right. Now, what do we have to do
since we want this letter to be communicating with those fields? The first thing is we come
up to mailings, and over to select recipients. This button, when I click it,
I click use an existing list. And I can connect the
letter to some database. All right, I’m going to
go down to our class notes for mail merge. And there is our mail
merge class notes. And I’m going to double
click this Excel file. Instantly, mail merge looks
inside and sees the sheet. I select the one I want. Now, when you’re setting
your data up in Excel, you have to be sure
it’s a proper data set and nothing else is in
any of the cells, which is exactly what we had
in our Excel workbook. I’m going to click OK to
accept that sheet as our table. All right. Now we can go click
Edit recipient list. And look through. There is the name. There’s the email,
score one, score two. All the way over to our grades. So these are the fields. So now Word is looking
over into that table. Now, there is a problem
with numbers in mail merge. You could see there’s a
bunch of extraneous decimals. And here’s the crazy thing. Even though these extraneous
decimals are not actually in the cells over there, Word
interprets them this way. So we’re going to
have to fix that. And there’s two different
ways we’ll fix that. All right. But for this step,
we just say yes. We’re connected to that table. Click OK. Now, we need to tell each
part of our document here to look to the correct field. So here’s how you do it. After, and let me zoom in here. Dear space comma, and I come
up to Insert merged field. I click on the drop down. And what’s this called? We saw it in pivot tables. We saw it in access. We saw it in the
relationship window, in queries in access right. It’s a field list. I need to say hey, name field. Boom. I click on it. And there’s the code. Now when you click
on this, it’s gray. We’ve seen this gray, which
means there’s some code here. This is called a field. We saw it earlier in the class
when we did table of contents. And a few other instances. Now, there’s the name field. Let’s come down here. For score one, it says
colon and then that space. Notice how our non printing
characters are helping here. After the space, I’m going to
come up and insert score one. There’s the field code that
connects this Word document to score one field
over in our Excel file. All right. So I click by score
two, score two. Score three, score three. Scroll down for the total. And then finally, the grade. Now we will have a problem
here that we’ll have to fix. But you can see that each
one of these is a field. Now, let’s just go
ahead and see what would happen if we
tried to finish this. Preview. And there we can go through. Well maxes are all fine. But once we use our arrows up
here to go to the next one, there’s our extraneous decimals. Now we want to remind ourselves
we learned earlier in the class how to go and view the code. The keyboard is Alt, F9. So I hold Alt down and F9. And no way. Merge field, score one. Merge field, score two. Merge field, total. Alt F9 is a toggle. I’m hitting Alt F9. Alt F9. It’s a toggle. Here we see the result of
looking over to the fields. Alt F9, here we see the
secret code behind the scene. Now notice something, Alt F9. This date up here
is also a field. Alt F9. You could see this
field will update every time we open
this with a new date. So we’re seeing mail merge
field, we’ve seen a date field, we’ve seen table of
contents, and a few other things earlier
in the class. Fields means there’s
something secret going on. Some code behind the scenes. So Alt F9 is the toggle. Now, we’re going to
need to fix this. And here’s how we do it. Now how do you remember
how to do this? Because most of us don’t
have to do mail merge. What I do is every
time I forget the code, I go over and open up Google. And pretty much if you type
mail merge number and something like format, it’s
a known problem that’s been around forever. So then you click on it. And you can go and read. Down here it says there is this
switch, this backslash pound space and then some code after. Now, you’ve got to be careful. The code is a little bit
different in earlier versions. So you’re going to have
to be version specific. All right. So I’m going to close this. That’s how I remember. I just go to Google. But here’s the code. You have to click at the end. And you could see the
cursor after the space. Pound space. And then to round it to
two decimals, say 0.00. Now let’s just toggle. Alt F9 and see if
that one worked. It didn’t. But we can go back one
And then forward one. And no way. Look at that. So actually use that to go back
and then come back to forward and then it enacted the code. All right. You ready? Alt F9 to toggle. And I’m going to copy this. Just that little
thing is the code you have to add to the merge
field behind the scenes. Control C. And then I’m going
to click my cursor, Control V. Very carefully. Make sure your cursor
is flashing right there. Control V. Control V. Now, let’s just go look. Now, Alt F9. Back one. Forward one. All right. Well you can see it’s
working for everything. But not the percentage grade. We’re going to have to
do something even more complicated. And again, this is in
the notes for the class. And it’s in the description
below in the video. Alt F9. All right. Check this out. We’re actually going to have
to take this whole merge field. Because remember, a percentage
is really a decimal underneath formatted in Excel
to show percentage. It was a number
format over in Excel. Well guess what. We’re going to have
to actually take that decimal, which is what is
being brought over from Excel, multiply it by 100, and then
add a format similar to this. All right. You ready? We’re actually going to have
to add two fields together. So you highlight
the whole field. And now you have to use your
special, your second keyboard Control F9. Control F9 adds another
field in essence. And watch this. We’re going to have to
come to the beginning of this little bit right here. And type an equals sign. And then at the end, we
have to go times 100. All right. So we had to take
that merge field from over in the Excel
workbook, multiply it by 100. And then this second
field here, is what we’re going to do
to add the formatting. I’m going to Control v. But right now, that
will just show us without a percentage symbol. You come to the end and you
add a percentage symbol. Right. So we actually had to take
the field equals sign, merge field, grade,
times 100 space. And then this is
called a switch. And then that’s the
custom number formatting. Actually, this custom
number formatting is similar to how
we do it in Excel. In business 216, Computer
Application class, we talked a lot about
number formatting. When we go and study in
spreadsheet construction, business 214 or business 217,
or the Highline Excel class at YouTube will talk a lot
about custom number formatting. But this little
bit right here is exactly what you’d use in Excel. It’s just over in Word. And when we’re
doing mail merge we have to do it in this merge
field code behind the scene. All right. Let’s Alt F9 to get
out of that code. And then back one, forward one. And boom. There we go. Now as we go forward, each
student has their letter. Now to finish
this, and I’m going to Control S. You simply
go to finish and merge. If you were going to send
an email, which we are not, you have to have Outlook
on your computer. But let’s just click this
and see what happens. It’s polite. Because we had a field called
email over in our database, ours happened to be in
Excel, it put that here. That means this part
of the mail merge is looking over
to the Excel file in the email column
or the email field. Now we type something
like business 216 grades, or something like that. So we can add the
subject line that will show up in every
one of these emails. Mail format, you can
choose what you want there. There’s an attachment,
if you want that. And then all current record, or
you can go from one to another. So this is another way
to exclude records. Earlier we saw how we could
select from edit recipients and uncheck if we
didn’t want some. But you have some control
over which records get sent. Now we’re actually not
going to send this. I’m going to click Cancel. Right. Control S. Now I want to close this. And I want to do this again. But I want to show you a
second, different way, instead of having to mess
with that code. We can actually fix
the numbers over Excel. All right. I’m going to close
this Word document. Go over to Windows Explorer. Open up letter to students. I’m immediately
going to hit F 12. And watch this. I’m going to show you a
really dangerous trick. It says letters to students. Usually if I click
on this, whoa. Look what it does. It puts the name in. And I love this trick. Because any time I have
lots of the same files, but I just want to
change part of it when I’m doing save as, if
I clicked Save right now it would ask me if I
want to replace it. But because I want the
same name but I just want to change one
of the numbers, I just change the number. Right. So in essence,
when I click here, and I’ll even do
it again right now. It steals the name from
that file puts it here and then you can change
it just slightly. All right. And then click Save. Now let’s do mail merge. Control S. We’re actually
going to save this file. And now we’re going to
go over and open Excel. And now what we’d like
to do is right below, I’d like to create
a table that uses what’s called a text function. All right. So I’m going to
copy the formatting. I’m going to use my paint brush. And then I’m going
to click right here. Now I want to give
this a defined name that we’ll use in mail merge. So I’m going to come
up to the name box. And I’m going to call this
something ridiculously long, mail merge table
format correct enter. Remember, we can go up to
formulas, name manager, now we can see we have
our define name there. We’ll use that. Notice, if we have these two
things here and did mail merge, it would be confused
because there’s extra data. But now that we used
the define name, we can use just that
define name and it will pick up just those cells. All right. Now the first thing
is, I would like to highlight all the cells. And in the active cell I
want to click equal sign. Click on the name, those are the
field names in the max score. And remember from our study of
Excel, if you have a formula and you want to populate it
into all the highlighted cells, you use Control Enter. Now, for the rest
of this, actually we can do that here too. Active cell, equal sign. Click on that one. Notice it corresponds perfectly
to what we need down here. And that’s a relative
cell reference. So when I Control Enter
it enters all of that in. It’s all of this number
data that’s different. We’re going to use a function
called the text function. The text function is great
because it will take a number and convert it to text. That way, when we send
this table and this number data for each record over to
mail merge, it’s not a number. So it doesn’t cause a problem. It’s text. All right. So you ready? Comma. And you have to know the
custom number format. Well, we already saw
this a little bit. You have to put it
in double quotes. 0.00. That’s the custom number format
that says show two decimals. End double quote. The Format Text argument. That Format Text
right here, that’s the part that tells the text
function to take this number up here and format it
and show it as text. That’s the actual value. All right. You ready? Close parentheses. Control Enter. Now notice, from
our study of Excel that alignment to
the left means text. Alignment to the right,
all these numbers, they are numbers. Now let’s drag
this down and over. Now we’re going to have
to amend this over here. So I’m going to highlight
the whole range. And in the active cell
I’m going to hit F2 and I’m going to add
that percentage symbol. Control Enter. Boom. There we have it. Now, this table, control
S, this defined name here. And we could test this. We could come up to
our defined name drop down and click on that. Sure enough, that’s working. Control S. That’s what will
show up when we do mail merge. Close this. Now, let’s go over to our Word
document and I want to connect. Mailings, select recipients,
use an existing list. We’ll scroll to our
mail merge folder. I’m in my mail merge folder. I come over here
and I double click. And now, look at that. It has are defined name. That is so cool. So this part of the
mail merge looks at defined names and sheets. Click OK. And now, this is going
to be a lot easier. Insert merged field. The name is never going
to have a problem for us. Score one, now it’s not. Because it’s going to be text. I’m just putting the fields in. Scroll down a bit. Total. And we insert this last one. Now we go up to preview results. Preview the results. It’s looking good there. Click the forward
arrow, and no problem. I like that way
better than having to mess with that code in Word. That is totally cool. Now, if you wanted to
print, what do you do? You click on merge and print. And that it asks
you, same thing as we saw with the email, all current
record or from some point to some other point. Click Cancel. Control S. Now, we want to do this
one last time for Access. All right. You ready? Come over to letter to students. Open it up. F12. I’m going to come
to the beginning. Access. Letter to students, click Save. Now let’s go over to mailings
and select recipients, use an existing list. We’ll go to our mail
merge class notes. And over here, grades. We never opened this up, but
the scores is the actual table. The grades is a query. So now I click OK. Now let’s come up
to edit recipients. And guess what, there’s our
database with our field names and we have the same
problem over here. Let’s insert each
one of our fields. Name. So we’ve inserted each
one of our fields, including decimal grade. We’re going to run
into the same problem. Preview results. Oh, there we go. So I’m going to unselect
Preview Results. Alt F9. And we’re going to have to do
our switch and our code again. Backslash pound
sign, space 0.00. Now I’m going to copy this. Control C, Control V,
Control V, Control V. I’m going to highlight
this whole bit here. Control F9. Equals sign, times
100, arrow key. Right after that, space
Control V. And then add a percentage symbol. All right. So there is the code
we had to add in Word. Alt F9. And there we go. Oh, I can see that
I have one record. So record number four I
don’t want to print out. I’m going to go back up
to edit recipient list. And on number four I’m
going to click uncheck max. Click OK. And so now that
one is not there. And you could email
or print this out. All right, so that’s a
little bit about mail merge. Now, in this example here,
this was a student letter. But you absolutely could do
mail merge if you had customers and you were sending
some information to. You use the customer database
with their names and addresses and emails. And write your letter. Or you worked in the AR
department, Accounts Receivable and you needed to
send out letters to customers who
are paying late. The idea is the same. The same letter needs
to go to lots of people. Use mail merge. All right. That was an amazing, fun class. In this class, we studied
Windows Explorer, Word PowerPoint, Excel, Access, and
even just looked at mail merge. Now, this class, business
216 is the prerequisite for business 214, which now will
change to the number business 217, which is an
advanced Excel class. At Highline, it’s called
spreadsheet construction. At YouTube, it’s the high
line Excel class series. So now if you want
to learn a lot more about Excel, a lot of the
concepts in this class, business 216, and
not just from Excel. There’s things we learned in
every one of the programs, with concepts about computers,
data, databases, formulas, that we’ll use in the Highline
Excel class spreadsheet construction. All right. We’ll see you next
video and series.

29 comments

  1. Thanks Mike.  I have used Mail Merge from Excel and it works great.. never knew the Ctrl F9 trick… Thanks.  In the past I convert the data before it comes over to Word.  I always seem to have an issue with email addresses.  Could not get the format correct on the Word Doc side.  I was getting the email address twice.  Do you have any advice on the correct MM Word format to display email addresses?  Merry Christmas to you and your family.  ps.  Got a reply from Isaac about his GoGos… 🙂  lol.   

  2. Mr. Mike hope u r fine this is sohel fm Bangladesh, I always keep look on ur youtube channel for new things. very recently i tried to make pay slip for company staff & worker using word mail merge. the salary sheet is prepared with excel 2010. i able to preper one payslip in one page is there any way that i can make it 6 person payslip in one page, i have tried with label option but that time i miss excel field label. i mean label only show employee name but i want before his name it will show Say category Name, before age it will show Age, Before ID no. It will show ID no. I will be very helpful if u upload solving that prob video

  3. In Excel                   In Word Merged Doc             What I used to get
     
    72,996.12                   72996.120000000001            72,996.12
    How to fix?

  4. This was very helpful for the concepts. Thank you. I have a very large database, so creating a second table with the cells formatted as text won't really work. But what I'm having a problem with is the currency format. What is the format for the switch for this? I have been searching but can't find it. Thanks

  5.  We have to send over 700 letters out of access database. Some are sent to individuals some are sent to couples. When letters are sent to couples there is an "and" in between first names. Unfortunately the "and" is also present when the letter is sent to an individual. The only way I know is to individually delete each "and" which is very tedious. How should I fix this problem.  

  6. Excellent tutorial. Complete novice with this stuff.  Converting decimal to percentage has been a bear with other instructions because none of them say to highlight the entire field, then hit ctrl F9 to add additional brackets to allow to enter formula.

    Thanks so much!

  7. Oh my goodness…this was seriously awesome…I have been doing a revolting (but very important) mail merge and DDE would NOT work so I was a bit upset as to how to fix my formatting…..you have made the day of a new business owner many, many miles away  in New Zealand…Thank you 🙂

  8. This is so great. You do not know the frustration that this has caused me with those silly decimal points. Love it. Thanks. 🙂

  9. The numbers I am using is a million( e.g 1748391.19) and use the text method, I get 1748391.19. I need to separate millions to thousand and hundreds (1 748 391.19). How do I do that?

  10. The numbers I am using is a million( e.g 1748391.19) and use theF9 Trick but I get 1748391.19. I need to separate millions to thousand and hundreds (1 748 391.19). How do I do that? I am on word 2016

  11. I tried this and it worked for the most part, but my percentages have more then 2 numbers at the end (83.2551%) and only 83.25% shows up, does anyone know how to fix this?

  12. Thank you for always explaining things so perfectly. I'm late to the party, upgraded office computers from 2010 to 2016 this week. I didn't need to do any of this with 2010, all my merge stuff worked perfect, percents, numbers and dates. So frustrated. Anyway, worked on one document doing the above and worked great, but on the next when I do the percentage tweak, say it's 5.00%, after I finish the above and hit Alt F9, instead of seeing the regular insert merge field I see 0.05. All the other fileds (dates numbers etc) revert back to showing the merge field but not this percent one. What can be going on? I deleted it and re-inserted the merge field several times. The field is called int_1. I expect to see { MERGEFIELD int_1 } but i see .05 while everything else is showing the MERGEFIELD format. Any ideas?

  13. Mr. Mike… pls advice how to give coma separator in mail merge (in Indian currency format). Like : 12,34,000.00 and 3,65,125.00 and 14,500.00 and 4,500.00 … Thank you.

  14. I am having another numeric problem in mail merge that is decimal places (e.g 25,625.00) how can it be done in MS Word

  15. Hi Mike, thanks for the video . I have a question; What if I want to broadcast customers with multiple lines? Will they have multiple emails?

  16. Can you please guide me regarding mail merge of bar chart for the students.
    I want to print the certificate of 240 students with their bar chart of Marks.

  17. Thank you your video was very helpful seeing your video finally helped me realize there was a "cntrl F9" and an "alt F9". very good thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *