Mail Merge How-To: Word and Excel Address Labels
Pluralsight blog Where devs, IT admins & creative pros go for news, tips, videos and more.
3,500+ tech & creative courses authored by experts - unlimited & online Get it now →
August 1, 2012

Mail Merge for Dummies: Creating Address Labels using Word and Excel 2010

By Mail Merge for Dummies: Creating Address Labels

Doing a mail merge using MS Office 2010 may get you confused or stuck, not because it is difficult or impossible to do, but because there are specific steps you need to follow. Missing a step, or doing something out of order may give you unwanted results.

I would like to help you out and give you step by step instructions, complete with images, on how to do a mail merge and create mailing labels using Microsoft Excel and Word 2010. I will also go over how to add a return address to your labels.

Ready? Let’s get started!

Address Labels

1. To start the mail merge for labels, you should have a list of all the addresses. I put all my addresses in an Excel spreadsheet with specific columns for first, middle, and last name, address, city, state, and zip code, saving all 3,000 of my addresses to my desktop with the name of “3000 names.xlsx”. You can see a screenshot of my Excel spreadsheet below.

Open Spreadsheet

Open Word

2. Now let’s open up Word 2010 and click on the Mailing tab all the way on top.

Select Mailings Tab

3. Click on Start Mail Merge and then select Labels. Please don’t worry if your window looks a little different and you have bigger icons on your toolbar. My Word 2010 window was resized and was not maximized so it was only trying to fit as many options as possible in the space I was allowing.

Select Labels

4. Once you click on Labels, a Label Options window opens. Go ahead and select the Label vendors. In my example I am going to go with the popular Avery US Letter.

Pick Label Vendor

5. Then select the labels you want. I am going to use 8662 Easy Peel Address Labels as I still have a lot of them left from when I did the merge in Office 2007. Once you are happy with your selection, click OK.

Select Product Number

6. After you hit OK, click on Select Recipients and then select Use Existing List. We are going to use that list I mentioned in step 1.

Select Recipients

7. Point to the location where the excel spreadsheet is located. As you can see, my list is on a desktop in a folder called Addresses. Select your spreadsheet and click on Open.

Select Desired File

8. Select the appropriate Table – in my example it is the one called 3000$. If you did not rename your table it is probably just called Sheet1$ – so select that one. Please check the check mark next to the first row of data that contains column headers. You should have column headers otherwise you will only see A, B, C, etc. and it will be very difficult for you to figure out which column is the first name and which one is the zip code. Once you make the table selection and you check the box, click on OK.

Select Table

9. After you click OK, your Word 2010 document should look like this. You should see each separate label.

Table Tools

10. There are two ways of adding the addresses to our labels:
a. The first one is to click on the Address Block

Click on Address Block

i. When you select the Address Block, you need to make sure the format is what you want and that all the fields you want are included. In the example below we select the format with the middle initial, but when we scroll down to record 4, for Lisa, we notice that her apartment number is not listed. In order to include it, we need to manually match fields. So if you are in similar situation, go ahead and click on Match Fields.

Insert Address Block

ii. After looking at Address 2 on the left, I noticed that (not matched) is currently selected. I will click on the arrow pointing down and select my AddressLine2 field from my Excel sheet to make sure the apartment number is going to be included in the address. After that I click OK.

Select AddressLine 2

iii. Now double check that the format with middle initial is still selected, make sure the apartment number is showing for my record for Lisa – all looks good – click OK.

Double Check

iv. Now you can see Address Block on my first label. Time to click on Update Labels. On my window all you see is a little icon that, when you point to it, says “Update Labels.” If your Word 2010 window is maximized you will see the icon with the words “Update Labels.” Go ahead and click on it.

Update Labels

v. This is what it should look like after updating labels.

Next Record

vi. Alright, it’s time to click on Finish & Merge! But before you print the documents, click on Edit Individual Documents.

Edit Individual Documents

vii. Make sure All is selected and click OK. This will make sure all of your records from the spreadsheet are being added to the mail merge.

Select All

viii. Double check one more time to make sure everything looks good. I am going to look at record 4 and make sure Lisa’s apartment number shows up. I want to point out that my document has 215 pages for my 3000 addresses. Another thing I want to point out in the second image is that my page 2 is different so it is not Page 1 repeating 214 times. You are ready to print your labels!

Arrows

In Word

b. The second way to insert the fields is to do it manually by selecting Insert Merge Field and then clicking on each field.

Select First

i. When inserting the fields manually, do not forget about inserting the spaces, periods and commas.

Format the Fields

ii. You can also format the fields. In my example I made them centered and bold. You can also do that with the address block as in the previous example.

Format Addresses

iii. Once you are happy with what the label looks like, it’s time to click on the Update Labels button. On my window all you see is a little icon that, when you point to it, says “Update Labels.” If your Word 2010 window is maximized you will see the icon with the words “Update Labels.” Go ahead and click on it.

Update Labels Again

iv. Once they are updated, they should look something like this.

Updated Addresses

i. Now let’s click on Finish & Merge. Before you print the documents, click on Edit Individual Documents.

Finish & Merge

ii. Make sure all records are selected and click OK.

Merge to New Document

iii. Double check your results to make sure extra fields are included and then you are ready to print!

Double Check

But wait! What if you want to include a return address on a postcard or a big shipping label? How do you do that?

Return Address Labels

11. Go back to step 4 to open up the Labels Option window. Select the label you want. In this example I am going to continue using Avery US Letter for Label vendor and then select 8577 Postcard for the big label. Basically, if you want to include a return address you need to have enough space on the label – that’s why I am selecting a postcard – I know it will have enough space for me to include the return address.

Label Options

12. Type in your address on the first label/postcard. You can format it however you want it (make it bold, change the font, etc.). Then select it and click CTRL-C to copy it. Hit the Tab key to move to the second label and hit CTRL-V to paste it. Do that for the entire first page of labels. In my example I only have 4 labels per page so I will have to do that 4 times. This is creating a label template that then will be used to create the mail merge.

If you want, you can also add an image to each label or some other information. Just remember to copy and paste it for each label on the first page.

Return Labels

13. Now that we have the label template ready, click on Select Recipients and then Use Existing List.

Use Existing List

14. Pick the excel spreadsheet with all your addresses.

Select Spreadsheet

15. Just like earlier, select the table, check the box next to First row of data that contains column headers, and then click OK.

First row

16. Now you have two options to add the addresses to your labels: either use the address block or insert individual merge fields manually, just like in step 10.

Select Address Block

17. This time around I am going to use address block. I will make sure all my fields are matched and then click on OK.

Match Fields

18. Once I see the address block on my first label, I will select it and using my indent markers on the ruler I will move it to the position I want the address to appear.

Indent Markers

Left Indent

19. Once I have my address block in the position I like, it’s time to update all the labels. Go ahead and click on the icon for Update Labels.

Update Return Addresses

20. The address block should appear on the rest of the labels.

Address Block Now

21. We are almost done. All we need to do is make sure all our records in the excel spreadsheet will be transferred for the mail merge. To do that, click on Finish & Merge and select Edit Individual Documents.

Edit Individual Documents

22. Make sure All is selected, then click OK.

Click OK

23. It may take a few seconds but you should be able to see the document being created. Since I am using bigger labels/postcards I have 750 pages created for my 3000 names (which makes sense right? 3000 / 4 = 750). Make sure the addresses appear the way you want them. In my example I will be checking record 4 for Lisa to make sure her apartment number is showing. You can also go to the second page to make sure the new page starts with a different name.

Check Record

Mail Merge Done

24. Now you are ready to print them!

Let me know if you would like to see any other tutorials when it comes to mail merge in Word 2010 by leaving your comments below.

Ready to test your skills in Excel? See how they stack up with this assessment from Smarterer, the newest addition to the Pluralsight family. Start this Excel test now.

 

About the Author

(MCTS Active Directory, MCTS Vista, Network+, Linux+, Project+, PMP) is an experienced Network Support Specialist and an expert in Windows Server support. She graduated from DePaul University, Chicago with a Bachelor degree in Network Technologies with highest honors. Gosia has over 8 years of technical and support experience and has worked as Systems Administrator for a high profile law firm, where she managed the Backup and Disaster Recovery plan. Gosia has been a part of the Train Signal team since 2006 as the Product Manager and has written many articles on a variety of topics, including Exchange Server 2007, Windows Vista, Small Business Server, and more.


Discussion