Merging for Dummies: Creating Address Labels in Word 2007
If you’re creating invitations for a wedding or another big event, you think it’s no big deal … it’s only a few hundred, you can knock that out in a couple of hours right? Type them up in Word, print them out and you’re done!
A couple of months later, you’ve got another job ahead of you … the thank you cards. Hopefully you saved the Word doc that you slaved over last time … formatting it and making it look just right.
But even if you did save your doc, you’ve still got plenty of work ahead of you … going through all of the names one by one, checking them off, making changes … it takes some time.
The solution? An Excel spreadsheet that allows you to very easily sort the list, make any necessary changes and have your labels ready in no time.
Working with an Excel spreadsheet makes the whole process of creating address labels — as well as other things, such as personalized letters, thank you cards, etc. — less time consuming and very easy.
Knowing how to merge Excel and Word documents can come in very handy in many situations. So today I’m going to show you a very easy but very useful time saver — how to create address labels in Word 2007 using an Excel spreadsheet.
Here’s what a simple Excel spreadsheet can look like:
Save this baby to your hard drive, call it something obvious like AddressList and all you need to do now is a quick merge between Excel and Word and your address labels are done in less than 10 minutes.
Here’s a step-by-step that shows you how easy it is to create address labels in Word 2007!
Creating Address Labels in Word 2007
1. Before you start make sure you have your Excel spreadsheet ready. Now open up Microsoft Word 2007 and click on the Merge tab.
2. Next, click on the Start Mail Merge button and select Labels.
3. Now it’s time for you to select the Label Vendor. In my case I am going to use Avery US Letter.
4. I have the 8662 labels so I am going to scroll down and select that. You should find the label number that’s appropriate for your labels, it’s usually on the box.
When you find your label number, select it and click OK.
5. You might get the following message. What this means is that if you typed something in this document and you did not save it, it will be lost. If you do not want that, hit Cancel, save the document and then repeat the steps from 1-5.
If you have a blank screen behind this message then go ahead and click OK to continue.
6. Next, click on the Select Recipients button on the ribbon and select Use Existing List.
7. Browse to the location where your Address List file is and select it. I have mine on the Desktop. I will click on it once, and then select Open.
8. In this window, select the sheet you have your list on. Mine is on the first sheet as this is all I have there. If you didn’t do anything special in Excel, your address list should be on the first sheet as well.
Also notice the check box next to First row of data contains column headers. You should have that checked if your columns have headers such as: First name, Last name, Address, etc.
If you don’t have the column headers, I would recommend going back to the excel file and adding them in. It’s going to make your life a lot easier while going through the merge. When ready, click the OK button.
9. Once you click OK you will see the following document. It may look a bit scary but don’t worry, all is good.
10. Since you are creating address labels, you will have two choices here. The first choice is to click on the Address Block button on the ribbon.
This option will automatically select all your columns and create an address for you.
The address looks good, but notice that we are missing the state. It’s not listed here but when we check back in our Excel document, it’s there.
Well this is ok, we can fix it. Go ahead and click on the Match Fields button.
11. Now in the left column find State and in that row click on the arrow pointing down, then select your header name for the State column. Mine is called State, so I will select that and click OK.
12. Now we see that the state is showing and the address looks good. Go ahead and click the OK button.
13. You will notice that in our first address label spot we have something called << AddressBlock >>.
If you decide to print your labels now, you will only get that first address in the first label. You need to make sure that the << AddressBlock >> appears on each label following the << NextRecord >> code. To do that you need to click on Update Labels option on the ribbon.
14. Once you click on that button, all labels will update.
And you are almost done! Now you have to click on the Finish & Merge button and select Edit Individual Documents to see what your labels will look like.
15. Make sure All is selected and click OK.
16. TA DA!!! Now all you need to do is print your labels.
Another Way to Merge …
1. Another way to do the merge is to pick the fields manually. To do that you will need to select the Insert Merge Field option on the ribbon and select one field at a time.
2. To start out, insert First Name, hit the space bar and then insert Last Name. After that, hit the enter key and insert Address.
Hit the enter key again, insert City, hit the comma key, and the space bar, insert State, hit the space bar again, and insert Zip Code.
This way all of your spacing and punctuation will be correct and your first label should look something like this.
3. You can also format the first label any way you want it. For example, you can change the font, you can center the text, make it bold, etc.
4. Once you are done making edits to the text and formatting, don’t forget to go back to your Mailings tab on your ribbon and select the Update Labels button. This will make sure that all your formatting and changes that you made to the first label are copied over to all the other labels.
5. Now you’re ready to view and print your labels. Click on the Finish & Merge option on your ribbon and select Edit Individual Documents.
Now isn’t this much easier than typing the names and addresses on the labels, over and over, and over again? And merging works just as well with letters, invitations, thank you notes, or anything else you do in Word. Try it out!
And don’t forget, there are lots of other cool things you can do in Word and Excel. Check out the sample videos below!
Get Microsoft Word 2007 Training and learn how to fully utilize this popular and powerful program!
Learn about all the new features and options, strategies that save you time, and how to integrate Word with other Microsoft programs including Excel, Outlook and PowerPoint.
Our complete Word 2007 training covers everything you need to know to become the Word expert.
Master Excel and become the spreadsheet expert with Microsoft Excel 2007 Training
Take advantage of all the new features of Excel and see how much more you can accomplish with your data.
Our complete Excel 2007 training – beginner to advanced – offers instructor led video, instructor’s notes, and plenty of Excel exercises to put your new knowledge into practice.
Related Posts:
- Merging for Dummies: Creating Mail Merge Letters in Word 2007
- 5 Things You Need to Know How To Do in Excel 2007
- Excel 2007: Visualize Your Data with Ease
- Excel 2007: Your Guide to Effective Pivot Tables
- Learn How to Create Relationships & Master Them in Access 2007
- So You Think You Know Word … Then Take Our Challenge!
- 13 Best Keyboard Shortcuts for Excel 2007
46 Responses to “Merging for Dummies: Creating Address Labels in Word 2007”
Zach Says:
May 28th, 2008 at 11:24 am
I hate Microsoft Office. I use WordPerfect…That’s not true. I was just trying to get back at you for throwing me under the bus in your post on my Vista article, but no one is going to believe I use WordPerfect. I’m pretty sure the guy that created WordPerfect glances from side to side to make sure no one is looking when he uses Microsoft Office. Great article; very informative!
penelope Says:
September 25th, 2008 at 8:47 am
Thank you so much! I have to print 3,000 labels each month for my company, and had no idea how to do it. Thank you for saving my job ;)
Jennifer Says:
September 25th, 2008 at 4:14 pm
You rock! You and your screen shots helped me so much! Thank you, Jen
Brady Bragg Says:
October 6th, 2008 at 10:48 am
Thank you! Why can’t Microsoft make things as simple as this?
Missy Says:
November 24th, 2008 at 5:20 pm
This was so helpful. My aunt was going to try to explain it to me but she always has to do it the hard way. These instructions were so easy to follow and I agree with Jennifer, the screen shots did make it way easier to follow. Thank You!
Gina Says:
November 29th, 2008 at 12:03 pm
Thank you for a wonderful, easy to follow article! The screen shots helped me to find the correct buttons, which is always the hardest part of following a tutorial… I am sending out my Save the Dates for my wedding and you just saved me!! thank you, thank you, thank you!!
Katy Jack Says:
December 4th, 2008 at 10:48 am
Thankyou so much for this easy to follow tutorial.. You have made one Old Granny very happy. How about a book for Confused Elderly Surfers!!!!
Gosia Grabowska Says:
December 4th, 2008 at 11:55 am
You are very welcome, you guys. Please let me know if there is anything else you would like to see here and I will do my best to create any tutorials you want!
Katy, I am going to work on the Confused Elderly Surfers tutorial for you :)
Gina – Congrats on your upcoming wedding!
Katy Jack Says:
December 5th, 2008 at 12:15 pm
Gosia you have made my day what a kind person you are. If you can help my generation to “do” computing you would be doing a great service for mankind trust me in this !! There are millions of us afraid to touch cash machines automated checkouts not to mention computers. We are desperate to learn but too timid to bash on on own own just in case we press the wrong button and break the equipment and who could teach us as those that know the secrets are always so busy leading hectic stressful lives that they cannot afford the time even if they wanted to.
So I repeat Gosia you are a star….Thankyou.
Nicole Says:
December 11th, 2008 at 12:45 pm
This was sooooo helpful. While trying to do 400 Christmas cards from spreadsheet I kept trying to create a label merge, getting to a certain point (Address Block) and not knowing where to go from there.
Thanks for posting this very user friendly and informative “How- To”.
Total lifesaver.
Sincerely,
Nicole
Jim Walsh Says:
December 26th, 2008 at 2:37 pm
A good review. One item that you left out that has me stumped, and on which I have blown away a few hours without success. In Word 2003 mail merge, there was the option to not include a field if the field was empty. Typically this is in an Excel workbook where I have fields Address1 and Address2. Only two people in the database have something in Address2 (they live in apartments). In the past, I could put <> <>. If I do that now, I get a blank line for everyone after Address1, except for the two people that actually have something in Address2. Can Word 2007 handle this correctly?
Thanks,
Jim
Ashley Says:
February 3rd, 2009 at 11:14 am
This was the most helpful merge site that I found. Others have been very unclear on how to do complete the process step-by-step. Thank you!
Sonia Says:
March 15th, 2009 at 5:42 am
Greaaaaat!!!!! i was really searching for how to go about with it…But didnt get d correct detail way : )
Thankszzzzzzzzzzzzzzzzzzz
Jason Says:
March 21st, 2009 at 12:48 pm
Thank you so much. Your demonstration was very easy to follow, I had no trouble printing my labels! Thank you so much. Jason
laura Says:
April 14th, 2009 at 12:25 am
It was very easy to follow the steps, so simple, and like Ashley says, it was the most helpful site that I found. Thank you very much. Laura
Carrie Says:
May 1st, 2009 at 1:17 pm
Thank you for this wonderful tutorial!
I have a question, though. Before I ran accross this tutorial, I was trying to use the mail merge wizard to create my labels. I used the wizard when I had the old word and it always worked like a charm. In the new word, no matter what I’ve tried, it never continues with the next name/address on the next page.
Example:
Say our people are A, B, C, D, E, F & G.
Page one will have A, B & C, but page two begins with B and continues with C & D
Page 3 has C, D & E
Page 4 has D, E & F
And so on.
Do you know why this is? I’m thinking there must be a setting or option I am missing, but haven’t been able to figure it out yet.
Thank you,
Carrie
Jim Says:
October 24th, 2009 at 3:57 pm
Thank you so much. I’m new to Word 2007 and was lost on how to do this. I tried to use the Avery Wizard but it would work either. You saved my sanity.
Rosa Says:
October 31st, 2009 at 11:32 am
Thank you so much. I love word 2007, but I am used to using word 2003 mail merge, was kind of lost with this. You made my work so easy. If some one can’t figure it out using these instructions, then they need to hand write their labels.
Debbie Says Says:
November 17th, 2009 at 11:50 pm
Thank you so very much, I have beat my head against the concrete wall trying to figure this out. You made my life so much easier.
You are an Angel to me.
Thanks
Emswyx Says:
December 8th, 2009 at 8:26 am
I need to make name cards for my school books….Can you help me please? I need to make it before the end of this December :(
Mush Says:
December 9th, 2009 at 6:31 pm
Thank you so much. I could never understand the instructions in my Word program. You made it so simple.
Courtney Says:
December 11th, 2009 at 12:54 pm
THANK YOU!!!! We are trying to do our Holiday cards… and my husband about threw his computer across the room last year trying to figure this out. He finally did, but didn’t write it down. I am bookmarking this page for next year!!!
Dae Schattie Says:
December 15th, 2009 at 3:27 pm
Thank you, thank you, thank you…This is the only site that “fully” explained how to print labels from excel 2007. I now can mail out my Christmas cards.
sudha acharya Says:
January 4th, 2010 at 12:17 pm
thanxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx a lot for the informaton. it is soooooooooooooooooooooooooooooooooooooo useful. i’m glad to have visited you on this site. one more help needd… can we put border line arond each label? it wld be great if you have solution for this
love
sudha
Barry Collin Says:
January 12th, 2010 at 8:02 am
I have spent 2 hours on the same problem as Jim Walsh – how to exclude blank lines on an address label because some of my addressees have all 6 address lines, others have anything from 2 to 5.
In Word 2000 I just ticked a box, and they were excluded, but not in Word 2007.
Please help, please – it’s driving me insane.
Gosia Grabowska Says:
January 12th, 2010 at 9:21 am
Hi guys! Thank you for all the comments. I am working on answering all of your questions :) Promise! Let me start with a quick answer to Jim’s and Barry’s question.
Word 2007 is smart enough to remove the empty fields – you don’t need to do anything extra. Make sure you are inserting the fields (not typing them). Check out the “Another Way to Merge …” part and try it that way.
I just double check it and it works! :) If you still have a problem, let me know!
Gosia Grabowska Says:
January 12th, 2010 at 9:41 am
To answer Sudha question about adding borders to your labels.
There are two ways of getting a border around yoru labels:
1. You create the label from scratch and add a border. Then use this label in your mail merge.
2. You download a label with a border from Microsoft’s site. To do so, open Word 2007, click on Office Button (the one on the top, left corner), select New, and then click on Labels. I saw few labels with a border in the Mailing and Shipping Category for either Business or Holiday.
Good luck!
Gosia
Ryan Says:
January 17th, 2010 at 4:15 pm
WOW!!! Thank you so much!!! Why cant Microsoft do their help site like this!!!!!!!
Shoefong Hu Says:
January 30th, 2010 at 8:26 pm
This was definitely the easiest and clearest site to learn how to make address labels in Excel. The illustrations helped a lot.
I have a question. I’d like to enter “Current Resident or” above the name. How do I do that?
Many thanks,
Shoefong
Gosia Grabowska Says:
February 1st, 2010 at 7:41 am
Hi SHoefong,
Thank you for your comment.
To add “Current Resident or” before the name simply type “Current Resident or” (and then hit Enter – that way it will be on the first line), before inserting the Address block or the field for “First_Name”.
Everything else is the same. Hope this helps!
Gosia
John Baxter Says:
February 9th, 2010 at 6:29 pm
Hi,
Having a seemingly endless hassle with Access 2007.
I want to print labels but am getting lots of blank lines where there are no address items to include in them.
How do I lose these and tighten up the addresses?
I am not into codes etc, and am sure in old Access it was a tick box thing. I have no problem in Open Office or Ability.
Hope you can help before a jump out the window!
M. Elliott Says:
February 19th, 2010 at 10:00 pm
Please allow me to say THANK YOU!!!!!!!!!!!!!!!! I am a computer and software illerate small business owner who just took on a direct mail campaign of 10,000 pieces. From the start, I knew that it was possible to create labels, but had no clue as to the process. I used Google to find the info, but the instructions were incomplete or just too complicated (even the Dummies instructions did not work for me). I was almost ready to give-up until i came upon your site. The instructions were simple and straight-forward! You are a God-send…THANK YOU!!!!!!!!!!!!!!!!!!!
Laura Says:
April 10th, 2010 at 6:06 pm
I may name my first born after you. . . even if it’s a boy.
Darlene Says:
April 11th, 2010 at 12:13 pm
THANK YOU SOOOO MUCH!!! This was so helpful for the 2007 version!!!
Carol Dault Says:
April 14th, 2010 at 2:10 pm
Gosia, thank you for great, easy to follow directions. I could not have done a successful merge w/o your help. You really made my day! :)
Stoney Says:
May 10th, 2010 at 7:55 pm
I want to thank you SOOOOO MUCH! I have looked at quite a few pages that claimed they could help me make these labels and yours was the only one that made it so simple! Thanks!
Peter Says:
June 17th, 2010 at 7:06 pm
My “labels” are Avery post cards, 4 to an 8.5 x 11 page. I’d also like to add a return address on each card. Can you help me? Thank you!
Connie Says:
July 6th, 2010 at 4:15 pm
Thanks for the great information I tried for over an hour to figure things out on the help tab in Word and finally decided to find other help. I love your step by step advice I was done in less than 10 min I wish I had thought to look sooner. Thanks again!!
lizzy Says:
August 4th, 2010 at 1:34 pm
i’m pretty sure i would kiss you right now, if i could!!!! thank you for such a wonderful and comprehensive tut!! you made my word day…heck you made my work week!!! TY!!!!
jane Says:
August 7th, 2010 at 7:02 pm
THANK YOU!!! This was a marvelous, detailed and exact description of how to merge excel labels with Word. You saved me!!!
Rob Khoo Says:
August 10th, 2010 at 1:03 pm
I kept getting just one label per page ….your site is the only one that mentions that you have to click Update Labels in order to place the “address block” to enter all the date from the merged Excel spreadsheet…..I am not sure why no one remembers to mention this critical step. Kudos to you for making your tip page complete. Many thanks.
Kasia Grabowska Says:
August 10th, 2010 at 4:34 pm
Thanks for letting us know Rob! We’re glad the article was helpful :)
luanne Says:
August 16th, 2010 at 1:33 pm
how am i doing this wrong?
my word document keeps coming up with random phrases like “Where did my worksheet go?” and “When you export data to a new workbook, your new worksheet containing exported data goes to Sheet 1. When you export to an existing workbook,”
after i merge the two, update it and preview it. it does not let me choose which phrases to merge. it only provides “autome” “autome1″ and “autome 2″
what am i doing wrong..??
Eric Chagnon Says:
September 2nd, 2010 at 9:08 am
Thanks for this great how-to, I could not get the job done before reading it… and now its perfect! I’m sending lots of love your way right now :-)
Ann Says:
September 3rd, 2010 at 2:30 pm
This rocks! Your instructions were very clear and easy to follow. You should get a raise.
Kasia Lorenc Says:
September 3rd, 2010 at 5:49 pm
Thanks Ann! I’m forwarding your comment to Scott (our boss) as we speak!