Mail Merge for Dummies: Creating Address Labels using Word and Excel 2010By Gosia Grabowska
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!
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.
2. Now let’s open up Word 2010 and click on the Mailing tab all the way on top.
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.
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.
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.
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.
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.
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.
9. After you click OK, your Word 2010 document should look like this. You should see each separate label.
10. There are two ways of adding the addresses to our labels:
a. The first one is to click on the 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.
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.
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.
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.
v. This is what it should look like after updating labels.
vi. Alright, it’s time to click on Finish & Merge! But before you print the documents, click on 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.
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!
b. The second way to insert the fields is to do it manually by selecting Insert Merge Field and then clicking on each field.
i. When inserting the fields manually, do not forget about inserting the spaces, periods and commas.
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.
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.
iv. Once they are updated, they should look something like this.
i. Now let’s click on Finish & Merge. Before you print the documents, click on Edit Individual Documents.
ii. Make sure all records are selected and click OK.
iii. Double check your results to make sure extra fields are included and then you are ready to print!
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.
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.
13. Now that we have the label template ready, click on Select Recipients and then Use Existing List.
14. Pick the excel spreadsheet with all your addresses.
15. Just like earlier, select the table, check the box next to First row of data that contains column headers, and then click OK.
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.
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.
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.
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.
20. The address block should appear on the rest of the labels.
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.
22. Make sure All is selected, then 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.
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.
About the Author
Gosia Grabowska (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.
- Mail Merge for Dummies: Creating Address Labels in Word 2007
- Merging for Dummies: Creating Mail Merge Letters in Word 2007
- Excel 2010 in Microsoft Office 2010 Suite
- Excel 2007: Your Guide to Effective Pivot Tables
- Beginner’s Guide to Excel 2010 Data Filters
- 5 Things You Need to Know How To Do in Excel 2007
- How to Save Documents to the Web with Office 2010 and Windows Live SkyDrive
- Writing IT Resumes with Word 2010 Part 1: Three Time Saving Tips
- Microsoft Word 2010 Updated Features and Benefits
- 13 Best Keyboard Shortcuts for Excel 2007