Lee's personal website, blog, and FAQ's

Main menu

Post navigation

How to make mailing labels with Excel and Word

I’ve done mailing labels over the years when I was the local chapter EAA newsletter editor and then a few times for Christmas cards, but each time I do them now, it seems that I can’t remember the steps it takes to repeat the process and as a result, it feels like I have to learn it all over again. So today I carefully went through the procedure because I’m helping out with my high school reunion and we will need to print out mailing labels several times in the next few months. I figured other people might want to know how to do it, so I will give the step-by-step method here in the blog.

First of all, you will need to put your mailing database in an Excel spreadsheet. Even though you’ll use the MailMerge function of Word, a spreadsheet is much easier for manipulating lists of data compared with trying to work with tables in Word. You should title the columns with intuitive names such as, LastName, FirstName, Address1, City, State, Zip code because if you do, MailMerge will usually correctly guess the order to arrange the data when it comes time to put them into a mailing label format.

I like using type 5160 Avery adhesive labels which are arranged in 8.5″ x 11″ sheets with individual labels 1″ x 2.625″ spaced 3 across and 11 down for a total of 33 labels per page.

[Note: When I first wrote these instructions, I was using Office 2003. Now that I have Office 2007, I can see that some of the menus have been changed. I’ll add some extra instructions below for those with Office 2007.]

Here are the steps for producing labels using the Mail Merge program in Microsoft Word:

Choose the option ‘Labels’ then select ‘Next’ at the bottom of the menu.

Step 2: Make sure ‘Change document layout’ radio button is selected and then and select ‘Label options…’ below it.

This will take you to a Label Options menu that allows you to select a label type. I use ‘Avery Standard’ and type ‘5160 Address’. Then select OK. Office 2007 asks you if it’s OK to overwrite your document at this point. Select OK.

In Word 2003, a grid should appear in your document that shows labels borders spaced in 3 columns, 11 rows per sheet. This grid doesn’t appear in Word 2007.

Step 3: Now select ‘Next: Select recipients’

Make sure the option to ‘Select/Use an Existing list’ is selected and the select ‘Browse’ and find the .xls file on your computer with your database and then open it. Select the spreadsheet page you wish to use. If it has just one page, you will see $Sheet1 and you should select it. In Word 2003 you may select ‘entire spreadsheet’ if you have only one sheet. You will then see a list of names from the spreadsheet. Select OK. You may have to select OK again. A list of <> entries will appear in the document.

Step 4: Now select ‘Next: Arrange your labels’

Select ‘Address Block’. Look at the preview field to see if the example label has all the information you want. Because of the layout of the heading row spreadsheet, it should be correct. If it’s not, you can fix it by selecting the ‘Match fields’ option and editing them. Now select OK. You should see <> in the first label and <> in all the other labels. Select ‘Update all labels’. This will cause the text <> to be added to all the labels that previously had just <> in them. So the first field will have <> and all the others will have <><>.

Note: If you wish to have more control over your labels, instead of selecting ‘address block’, you can select ‘More options’ and this will allow you to apply the exact amount of spacing, carriage returns, etc., around each field. I’ve found this helpful if you have international addresses mixed in.

Step 5: Now select ‘Next: Preview your labels’

Now you will see a sample page of the labels.

At this point, you can select ‘Print…’ which will walk you through the steps of printing out the labels on a local printer, or you can select ‘Edit individual labels…’ If you select that, it will create a Word document file that you can edit or store away for future use or print from another computer. I usually use that option.

I should mention that there was an issue with the zip codes that started with ‘0’ (zero). You should format that field in the spreadsheet as a ‘special’ zip code field, which is an option in the format command of Excel. Then during the import to MailMerge, I had to use these steps.

Preparing DDE settings in Microsoft Word:If you perform a mail merge in Office 2003 and use Excel as the data source, some of the numeric data may not retain its formatting when merged. To resolve this:1. Open Word2. On the Tools menu, click Options, and then click the General tab.3. Click to select Confirm conversion at open check box, and click OK.4. Continue creating the mail merge as explained above5. Open Data Source.6. In the Confirm Data Source dialog box, click MS Excel Worksheets via DDE (*.xls), then click OK.

You may have to answer a few questions when you import the .xls file but it will fix any issues with the zip code field, especially with those starting with ‘0’ (zero).

Another trick to making labels is printing a single sheet of labels out on plain paper first to make sure the alignment is OK. You can hold the sheet of labels up to the light with the printed paper labels behind it and insure that the text will all end up inside the sticky label borders. You don’t want to be making mistakes on the label stock because it’s much more expensive than plain paper.