In this video, learn how to perform a mail merge in Word using contact information stored in an Excel file, and create envelopes with an address block that uses and formats the data found in the Excel spreadsheet.
- [Instructor] I often get asked by people who have contact information stored in an Excel spreadsheet if they can use that spreadsheet in a mail merge in Microsoft Word. The answer is a resounding yes, it's actually quite easy. A lot of automation is built right in to this particular scenario and that's what we're going to explore in this week's Word Tip. We'll start with the Excel spreadsheet. If you have the exercise files, open up Contacts067. You'll notice that there are a number of columns and each of these columns represents a single field.
You wanna separate them out as best as possible. First name is separate from last name, for example. You can see I have two address fields. One for the main street address and if there are any units or apartments, there's a separate column for that. Notice that some of them have and some of them do not have contents in those fields. Then we have city, state, zip, country, even though most of them are USA, there is one that's not, we'll keep that in mind. We even have a phone field. You may have additional fields like company name and titles, et cetera.
We don't have to use all of the fields when we perform a mail merge in Microsoft Word. This is the file we're going to use. Keep it in mind as we switch over to Microsoft Word now and start a new blank document. Click file, then new, and select blank document. Now, we're going to go up to mailings. When you're doing a mail merge, it could be with a form letter, it could be with labels, it could be envelops. So in our scenario, let's say we wanna create envelops for each of the people we saw in that Excel spreadsheet.
On that case, we go to start mail merge and this is a drop down, you can see we could start a new letter, a form letter, an email message, envelops, there's labels there, and a few other options, but envelops is what we want, so let's give it a click. Right away, you're going to see envelop options where you get to choose the envelop size. Mine's already there, size 10 the standard business envelop, but you can see there's a long list to choose from. I'm gonna keep mine at size 10. We can change the delivery address and return address fonts.
You can see, and their alignment, but there's a preview of what it's going to look like down below. Exactly what I want, so clicking OK is going to create the first envelop. My return address is already in there, so it shows up. You can go in and type in your own return address if you want to and use it going forward. Mine's there from a previous Word Tip where we created a single envelop. Next, I'm gonna zoom out just a little bit, down here with the zoom slider. I'm gonna click the minus sign to zoom out to 130% so you can see the entire envelop.
All we need now is those addresses down below. Now if you start clicking in the envelop, you can see nothing happens until you get low enough. Once you're down there, you can click inside this little area that's reserved for the address block. At this point, we can start typing in addresses or perform that mail merge. If we're going to use a mail merge, we have to select where the recipients are coming from, and that's our Excel file, so that's step two. Go up here to start mail merge in this section and click the select recipients drop down.
What we wanna do is use an existing list. We don't wanna type out a new one, we already have it in Excel. So let's use an existing list. When we click that, we just have to browse to that file. And again, it's in your exercise files if you have them, and it's Contacts067, an .xlsx file. With that selected, click open and those are the contact we'll be using from our sheet, sheet one. You can see dates modified and created, the type is table.
Also, down below, checked off, the first row of data contains column headers. And if you recall, ours does, those labels at the top of each column. We don't wanna actually perform a merge with those, they'll be used as column headers. Everything below will be merged. Let's click OK. Nothing really happens except all of a sudden on the ribbon, a number of other items are now available to us because we've selected our recipients. I want you to see what happened automatically in the background. Let's go to match fields, click there, and you're gonna see what happened automatically.
You can see first name was matched up with our first name column. Others that aren't being used are not matched up. Last name, last name. Next one is address one, street address one. Address two, street address two. If anything doesn't match here, we can change it. Notice we can click these little drop downs and choose from our Excel fields or columns. But it looks like Word did a good job matching city, state, postal code, and zip. I'm in Canada, so I see postal code.
Country or region is matched with our country. Business phone is matched up with the phone column, even though we're probably not going to use that in our address block. Lots of other options to be matched up if we needed them. All we have to do now is click OK and know that everything's going to work out right as we start adding the fields here. Now, we can do it the long way, inserting a single field at a time. We could insert first name, leave a space after that, and then insert the next field, last name. But then when we get to the addresses, if they didn't have a second address, unit number, apartment number, for example, how do we make sure a blank line doesn't show up? Well, all of that's built in to something that's much easier than us entering these one by one.
So use your backspace key to take them out until they're gone, cursor's still flashing here. Instead, we'll insert an address block, give that a click. And now you can see that there are a number of check boxes already checked off. You can see the formatting for recipients' names. There's a preview over here on the right hand side of what that's going to look like. In this case, our first record does have Unit B. If we move using the arrows here to the next one, it doesn't include an apartment or unit number and there's no blank line.
It's removed for us automatically. Notice also it doesn't show USA. However, when we get to the last record, it does show Canada and that's because only country regions that are different than the USA will be included. That's because of this radio button. If you want the country to be included if it's USA included, choose always include. And now when we go through our records in the preview, you can see USA does show up. That's up to you. All we have to do now is click OK and the address block is there.
Wanna see what that looks like? Click preview results up top and it actually shows you records that we can navigate from left to right. Looks perfect. Click preview results to turn that off. Now at any time if you wanna see the coding or edit the coding that goes into the address block, click anywhere inside, hold down your shift key and press F9, that's function key nine. You'll see the entire code that goes into the address block. The backslash f is a code that says if there's nothing in one of these fields, remove the blank line following that field.
That's something you'd have to add doing the insert merge field one by one on your own. You have to understand that coding. You can see there's some more down at the bottom. When you're done reviewing the coding or even editing if you wanted to, press F9 all by itself to get back out. There's your address block, the preview looks good. All we have to do now is go up to finish and merge, and choose how we wanna finish things up. Do we wanna go right to the printer with our envelops, send them in an email, or edit individual documents? I like this one, it creates a Word document with one envelop for each of our recipients.
Go ahead and click edit individual documents. We'll make sure all records are being used and click OK. And there we go, there's our first envelop, and as we scroll through the document, we can see multiple envelops with each of those addresses, thanks to the mail merge and our Excel file containing our contact information.
Note: Because this course an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.