Excel: Mailing Labels in Word

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I have address information in Excel and I have to make mailing labels.

Strategy: You can use the Mail Merge in Microsoft Word to make the labels. Here are the steps:

  1. Make sure your data in Excel is set up with each address going across a row.
  2. Have headings in row 1.
  3. Close the file in Excel.

  1. Set up your data properly in Excel.
    1. Open a blank document in Microsoft Word.
    2. In Word, go to Mailings, Start Mail Merge, Labels.
    3. Choose the right size labels in the Label Options dialog. Click OK. You get a document full of blank labels.
    4. Go to Mailings, Select Recipients, Use an Existing List....

  2. Choose an existing list.
    1. Browse to and select your Excel file.
    2. In the Select Table dialog, choose Sheet1$. Make sure First Row of Data Contains Column Headers is selected. Click OK. You now a confusing <<Next Record>> in all but the first label.
    3. Notice that the insertion point is in the first label. Go to Insert Merge Field and choose the first line of your address field.
    4. This is tough to do, but carefully press Shift+Enter to go to the next line without inserting a bunch of space.
    5. Repeat steps 10, 11, 10, 11, 10 until you have all four lines of the address in the label.

  3. Build the first label.
    1. If you want any special font, add that formatting to the first label.
    2. Press the Update Labels button to repeat your fields in all of the labels of the sheet.
    3. Choose Finish & Merge, Edit Individual Documents. You will have labels from the Excel data.

    Gotcha: Step 14 seems to be the non-obvious step that trips most people up.

For more resources for Microsoft Excel