Learn Excel from MrExcel Episode 906 – Holiday Mail Merge


Hey, alright, welcome back to the MrExcel
netcast, I’m Bill Jelen. Well, we’re getting close to the holiday season. You might want to be sending out the holiday
cards, and if you already have your customer list in Excel, we can take that data and create
labels over in Word. This is always a hard problem. People say, “Why don’t you write about this
in your books?” And I say, “It’s real simple, it’s not an
Excel problem; it’s a Word problem.” Go find MrWord.com and see if he puts it in
his books. But let’s talk about it since the data starts
out in Excel and I’m getting questions about it. The first thing, that data in Excel has to
have the headings in Row 1. And in this case, it doesn’t. Now, we could delete that nice title up there,
or instead just create a named range. So I’m going to select all of that data and
create a named range here– click in the name box, type “Data”, and now the first row of
the name range has the headings. So we’ll save that and close it. We can actually close out of Excel and go
over to Microsoft Word. Now, in Microsoft Word, we’re going to start
with a blank document; we’re going to go to Tools; Letters and Mailings; and say that
we want to do a Mail Merge; and it brings up a six-step process. In step one, we choose “Labels” and click
Next; in step two, you want to say you want to “Change document layout” and click Label
options, and figure out what kind of labels you have. Now, usually I have 5160s– those are 30 labels
to a sheet, but if you have, you know, company name and suite numbers, you might need a taller
label like a 5162– click OK, and you see it creates some blank labels there. Now, we click Next, and this is where we get
to browse for our Excel file. So it starts out in some weird folder, I’ll
go back to the folder where I saved that file; specify; and I’m going to make sure to choose
the range name– the Data range name– and first row of data contains column headers;
click OK. Alright, so it shows me all the data and we’re
good here. Next, arrange your labels. Well, they try and give you an address block
that never works for me. I always choose more items and basically I’m
going to add these items: Company first– see I’m double clicking– Last, Title, Street
address, City, State, and zip, and then click Close, and we get a horrible looking label
over here. Now, I’m going to go through and actually
kind of format that. So I’m going to click right before the City
and hit a return between the City and State, put a comma, space, between the State and
Zip, put a space right before “Addressed to”. I’m going to hit return right before Street. It’s interesting: If there is no Address To,
there is no Suite number, it won’t put that line in. You know, I put Title in, I really don’t want
that– I’m going to get rid of the Title. First… between first and last, I’m going
to put a space, and then before first put a return. So we get everything nicely arranged. And once we have that first label, we then
click this button over here on the right-hand side, “Update all labels”. Alright. And basically, it just copies that layout
down to all the other labels on one sheet. Notice we still have a one sheet document;
we’ll click Next; Preview your labels; and they show us what the first sheet’s going
to look like; click Next again. And it’s interesting, we have to say “Edit
individual labels”– Edit individual labels will allow us to actually create a brand new
document with all of the labels. Now, here we have four pages of labels ready
to send out for all of your holiday cards. So there you have it, how to do a mail merge
to take data from Excel, and create labels in Microsoft Word. Want to thank you for stopping by, we’ll see
you next time for another netcast from MrExcel.

2 thoughts on “Learn Excel from MrExcel Episode 906 – Holiday Mail Merge

  1. Hello there,
    I am creating a database including employee Job Descriptions, the information in some fields can be up to 30 Lines, nevertheless, after I completed the database I tried to hock it to a mail merge system. but the problem is that some of the fields that include data does not appear fully in the mail merge.
    but what is even more confusing that this incomplete information does not occur in all fields.
    in the forum at

  2. Hello there,
    I am creating a database including employee Job Descriptions, the information in some fields can be up to 30 Lines, nevertheless, after I completed the database I tried to hock it to a mail merge system. but the problem is that some of the fields that include data does not appear fully in the mail merge.
    but what is even more confusing that this incomplete information does not occur in all fields.

Leave a Reply

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