How to create labels from Excel using Mail Merge

This tutorial explains how to mail merge and make labels from Excel data. You will learn how to prepare your Excel address list for mail merge, set up the Word document, make custom labels, print them and save the file for later use.

Last week we started to look into the capabilities of Mail Merge. Today let's see how you can leverage this feature to mail merge and print labels from Excel's address list.

How to make labels from Excel

If you've had a chance to look through Mail Merge from Excel to Word article, a large part of this tutorial will be already familiar to you because printing labels from Excel is yet another variation of the Mail Merge. Whatever intricate and intimidating the task may sound, it actually boils down to 7 basic steps.
7 steps to print address labels from Excel

Now let's have a closer look at each step. I will be using Microsoft Excel 2013 in this example, but the steps are absolutely identical in Excel 2016 and Excel 2010 and very similar in Excel 2007.

Step 1. Prepare the address list in Excel for mail merge

In essence, when you mail merge labels from Excel to Word, the column headers of your Excel sheet become placeholders in a Word document denoting the mail merge fields. Each placeholder corresponds to one entry such as first name, last name, salutation, city etc.

Microsoft Word will be pulling out the information from your Excel columns and placing it into the corresponding merge fields in this way:
Mail Merge labels from Excel

So, before starting the Mail Merge, invest some time in setting up your Excel spreadsheet to ensure it is properly structured for the merging purpose. This will make it easier for you to arrange, review and print your mailing labels in Word and you save more time in the long run.

Tips:

  1. Give clear and self-explanatory names to your Excel columns, for example First Name, Middle Name, Zip Code and so on. In this way you will be able to quickly locate the right column in a Word mail merge document.
  2. Separate the recipients' information into very small pieces. For example, you'd better create 3 separate columns for titles, first names and last names rather than a single Name column.
  3. Make sure your Excel spreadsheet does not contain any blank rows or columns. When doing a mail merge, empty rows may mislead Microsoft Word and as a result it may merge only some of the entries believing it has already reached the end of your address list.
  4. To correctly mail merge zip codes and any other numbers, please follow the recommendations provided in How to format mail merge numbers and dates. This will ensure that all leading zeros in your postal codes will remain intact.
  5. If you create a mailing list by importing information from a .csv or a .txt file, then use the Text Import Wizard. You will find the detailed guidance in this article - How to import CSV to Excel.
  6. To help Microsoft Word locate your address list during mail merge, you can give it a name in your Excel sheet in this way:
    • Select the address list in your Excel spreadsheet, including column headers.
    • Switch to the Formulas tab >Defined Names group > Define Name.
      Define a name for your address list in Excel.
    • Type a name for the address list in the Name box and click OK. Note that neither spaces nor hyphens between words are allowed, you can use an underscore _ instead.

Step 2. Set up the mail merge document in Word

Now you need to configure the main document for the mailing labels you want to print. The good news is that you have to do this only once for all the labels in the mail merge. You can also choose some other content (text or graphics) to include in each label, e.g. your company logo or return address.

You can configure the label main document in 2 ways:

  • Using the Mail Merge Wizard. It provides the step-by-step guidance which may be helpful for beginners.
  • Using the Mailings ribbon group. If you are pretty comfortable with the mail merge feature or prefer working with the ribbon, you can proceed in this way.

Mailings ribbon group in Microsoft Word

Now let's go ahead and configure your main label document. We will be using the step-by-step wizard in this tutorial to make the learning curve easier.

  1. In Microsoft Word 2007, 2010, 2013 or 2016, create a new document or open an existing one. The currently opened document will become your label main document.
    Note. If your company already has a package of label sheets from a certain manufacturer, e.g. Avery, then you need to match the dimensions of your Word mail merge document with the dimensions of the label sheets you are going to use.
  2. Head over to the Mailings tab > Start Mail Merge group and click Step by Step Mail Merge Wizard. The Mail Merge pane will open in the right part of the screen.
  3. Select Labels and click the Next: Starting document link near the bottom of the Mail Merge pane.
    Select Labels and click the Next: Starting document link near the bottom of the pane.
  4. Decide where you want to set up your mailing labels - in the current document, some other existing document or a new document.

    If you want to create a new sheet of mailing labels, select the Change document layout radio button and then click Label options.
    Click Label options to create mailing labels in a new document.

    Tip. If you do not see the document you want in the Start from existing box, click (More files...) and you will be able to select any other document from your computer or local network.
    Click (More files...) to select any document from your computer or local network.
  5. The Label Options dialog box will open and you select the needed options such as:
    • Under Printer information, select the printer type.
    • Under Label information, choose supplier of your label sheets.
    • Under Product number, select the number that matches the product number listed on your package of label sheets.

    For example, if you want to print Avery labels from Excel, your settings may be similar to this:
    Select the needed options in the Label Options dialog box.

    Tip. For more information about the selected label package, click the Details... button in the lower part of the dialog window.
    For more information about the selected label package, click the Details... button.
  6. When done, click the OK button. Then click Next: Select recipients.

Step 3. Connect the address labels to your Excel mailing list

It's time to connect to your Excel address list to merge the address information with your mailing labels.

  1. Choose to Use an existing list under Select recipients.
  2. Click the Browse button and locate the Excel worksheet you want to use.
    Connect to your Excel address list to merge the address information with your mailing labels.
  3. If you have given a name to your address list, as recommended in Step 1. Prepare the address list in Excel, select it and click OK. Otherwise, select the whole table, you will be able to refine, sort or filter the recipients to include in the mail merge later.
    Select the address list.

Those of you who prefer working with the ribbon can connect to an Excel worksheet by clicking Select Recipients > Use an Existing List...
Connect to an Excel worksheet by clicking Select Recipients > Use an Existing List...

Tip. If you wish to make address labels from your Outlook contacts, you do not necessarily need to import the contacts into Excel. Simply select Choose from Outlook Contacts... instead of Use an Existing List...

Step 4. Select the recipients to include in the mail merge

The Mail Merge Recipients window will open with all the recipients from your Excel mailing list selected by default.

To exclude some of the recipients, clear a check box next to their names. When done, click OK.
To exclude certain recipients, clear a check box next to their names.

When you are finished refining the recipients list, click Next: Arrange your labels.

Tips:

  • To sort the recipient list by a particular column, click the column's heading. The first click sorts in ascending order, the second - in descending.
  • To filter the recipient list, click the arrow next to the column heading and choose the option you want, e.g. blanks or non-blanks. You can also filter the list by some other information such as country or state.
    Filter the recipient list.
  • For advanced sorting or filtering, click the arrow next to the column name, and then select (Advanced...) from the drop-down list.
  • You can find more options under Refine recipient list section in the lower part of the Mail Merge Recipients window, e.g. find a recipient, find duplicate items and validate recipients. The last option is available if you have any validation software installed.

Step 5. Arrange the layout of your address labels

Now you need to define the content of your labels, decide what information to included and specify how the addresses will appear on each mailing label.

You do this by adding the placeholders to your Word document that correspond to the column headers in your Excel worksheet. These placeholders are called mail merge fields. When you perform the label mail merge, the placeholders will be replaced with the information from your Excel's address list.

To arrange your mailing labels, follow these steps.

  1. In the main Word document, click where you want to insert a certain field and then click the corresponding link on the Mail Merge pane. Typically, for mailing labels you would need only the Address block merge field.
    Add the Address Block.

    Another way to add the Address Block is to click the corresponding button on the Mailing tab > Write and Insert Fields group.
    To add the Address Block, click Mailing tab > Write and Insert Fields > Address block.

  2. The Inset Address Block dialog window will open. Select the desired options, check the result under the Preview section and click OK.
  3. When you are finished with refining your Address Block, click OK, and then Next: Preview your labels.

How to add missing address elements

It may happen that address elements you see under in the Preview section do not match the selected address pattern. Typically, this is the case when the column headings of your Excel sheet differ from the default Word mail merge fields.

For example, you have chosen the following address format for your labels: Salutation, First name, Last name, Suffix, but the preview shows only the First name and Last name, as you see in the screenshot below.

In this case, first off verify whether your Excel source file contains all the required data. If it does, click the Match Fields... button in the lower part of the window to help Word correctly interpret the elements from your Excel mailing list.
Click the Match Fields... button to help Word correctly interpret the address elements.

Click the arrow next to the required field and select the right match. For Word to remember your choice, check the Remember this matching for this set of data... check box.
Match address fields.

Click OK and make sure the address elements are displayed correctly. As, you can see in the next screenshot, the Courtesy Tile does show up now. And since our Excel data source file does not have the Suffix column, missing Jr. or Sr. is fine : )
The address elements are displayed correctly under Preview.

Note. The Address Block will appear simply as «AddressBlock» in your Word document. When you print labels from your Excel source file, the chevrons (« ») won't be there, they are used only to distinguish the mail merge fields in the Word document.

How to make a custom layout of mailing labels

If none of the pre-defined options in the Address Block is suitable for you, you can arrange a custom layout of your address labels in this way:

  1. Click the More items... link on the Mail Merge pane or Insert Merge Field button on the Mailings ribbon group.
  2. In the Inset Merge Field dialog, select the desired field and click Insert.
    In the Inset Merge Field dialog, select the desired field and click Insert.

Your custom mail merge fields and the printed mailing labels may look similar to this:
Custom mailing labels

Tips:

  • To copy the format and layout of the first label to all other labels, click Update all labels on the Mail Merge pane.
  • In addition to the mail merge fields, you can add some text or graphics to be printed on each label, e.g. your company logo or return address.
  • You can change the format of a particular merge field directly in the Word document, e.g. display dates or numbers in a different way. Select the needed field, press Shift+F9 to display the field coding and add a picture switch to the merge field, as explained in Mail merge with dates, currencies and other numbers.

Step 6. Preview your mailing labels

Well, you are very close to finished : )

  1. Click the left or right arrows on the Mail Merge pane or Preview Results ribbon group to see how your mailing labels will look like when printed.
    Click the left or right arrows on the Mail Merge pane to preview the labels.
  2. If you want to change the labels' font type, font size, font color or make any other changes, switch to the Home tab and design the currently previewed label to your liking. The edits will be automatically applied to all other labels.
  3. When you are satisfied with the appearance of your address labels, get back to the Mailings tab and click Next: Complete the merge.

Tips:

  • To find and preview a certain label, click Find a recipient... link and type your search criteria in the Find Entry box.
  • To make changes in the address list, click the Edit recipient list... link and refine your mailing list.

Step 7. Print address labels

You are ready to print the address labels from your Excel mailing list.

  1. Simply click Print... on the Mail Merge pane or Finish & Merge > Print documents on the ribbon.
    Print address labels.
  2. Decide whether to print all of your mailing labels, current or selected ones.

Step 8. Save the labels for later use (optional)

If you may want to print the same labels at some point in the future, you have two options:

  1. Save the mail merge document connected to the Excel address list.

    Click the Save button or press Ctrl+S to save the Word document in the usual way. The mail merge document will be saved "as-is", connected to your Excel source file. If you make any changes in the Excel mailing list, your address labels in Word with be updated automatically.

    The next time you open the document, Word will ask you whether you want to merge the information from the Excel data file. Click Yes to merge labels from Excel to Word.
    Click Yes to merge labels from Excel to Word.

    If you click No, Word will break the connection between the document and Excel data file, and replace the mail merge fields with the information from the first record.

  2. Save merged labels as text.

    If you want to save the merged labels as usual text, click the Edit individual labels... on the Mail Merge pane.
    Click the Edit individual labels... to save the merged labels as usual text.

In the Merge to New Document dialog box, specify what labels you want to merge. When you click OK, Word will open the merged labels in a separate document. You can make any edits there and then save the file as a usual Word document.

Third party add-ins to print mailing labels from Excel

There exist a number of third-party add-ins purposed for printing address labels. Some of them are manufactured by label sheets' suppliers and designed especially for formatting and printing their label packages.

Let's have a quick look at one of such add-ins - Avery Wizard for Microsoft Office. As its name suggests, this tool is intended for printing Avery labels. The wizard will help you print all kinds of Avery labels from Excel, Word, Access and Outlook. It supports all versions of Office XP to Office 2010 and 2013 (32-bit and 64 bit).

You can download a free trial using the above link. To be able to download, you will need to provide them some basic information such as your name and email address. But this seems to be the only complexity. The installation goes smoothly and once completed, you will see the new AVERY tab on your ribbon. Clicking the Avery Wizard button opens a nice looking wizard that will walk you through the process:
Avery Wizard to print address labels

One more example of the printing labels software is Brother P-touch Add-In. This tool might help you print labels using the Ptouch printer. However, the significant limitation of this add-in is that it currently supports only 32-bit versions of Office 2010 and 2013.

And this is the end of our printing labels tutorial. Thanks a lot to everyone who has read to the end! If you have any questions or run into problems when printing labels from Excel, do not hesitate to leave a comment here, we will be happy to help!

26 Responses to "How to create labels from Excel using Mail Merge"

  1. Joanna Dickie says:

    I cannot get to grips with being able to type an address and have it printed on one of the labels on my label sheet. I found this to be easy previously.

  2. Toni says:

    Mail merging and printing labels from Excel has gone smoothly until the last step. All the addresses on my Excel spreadsheet are not transferring to the merge. It always stops at line 369 on my spreadsheet. Any suggestions?

  3. Suzanne Charette says:

    I can preview all of my labels one by one but can only see and print the first page?

  4. John Doggart says:

    How can the label be set to print so it misses out blank fields?

  5. Jane Foley says:

    I have printed labels for years, and not had a problem, but suddenly the labels are not printing within the labels outline from about the middle of page one. This gets progressively worse over the pages.
    I have addresses in Excel 2010, use Word 2010 mailmerge wizard, have the Avery codes loaded, and am using L7160.
    The Xerox printer (new) was thought to be the issue, but the engineer says it is only printing what is being sent to it. So, I have some old labels from 2013 and I sent them to the printer, and they printed OK!

    It's a peculiar one and it has me beat. Does anyone have any ideas?
    Thanks, Jane

  6. Chris Neil says:

    I am attempting to produce labels by merging from an Excel spreadsheet into Word 2013. I am using Avery j8162 labels, therefore 16 labels per sheet. I have 34 records to merge. The label layout (not an address block, but a unique layout) is done by inserting merge fields from the spreadsheet, and "update all labels" to transfer the label layout to all records on the page. All goes well until I preview results. The first page has records 1 to 16 on it, the second page has records 2 to 17 and so on, so that I end up with 34 sheets with massive duplication, rather than just 3 sheets with a single label for each record.
    Can you help?

  7. PL says:

    I'm having a problem that looks like people above are having also. Only the first two pages of labels are printing. There should be 24 pages. Any advice or help would be very appreciated, thanks.

  8. Alaska Williams says:

    I need the solfware to print labels form my excel address list

  9. Penny B says:

    Ok, trying to use this to print name labels for folders for conference. I just need the name and I think it is formatting for address label. I tried to format in excel before the mail merge process and it didn't migrate over correctly... i.e. bold, centered,16 pt. Is there a way to format the label in word during mail merge process?
    any help would be appreciate. frustrated in Greenville, SC

  10. Ammi says:

    This was very helpful. Thank you so much

  11. Chris says:

    Extremely clear and very useful. Great job.

  12. David ,B says:

    I customise labels on a sheet. The Mail Merge advice is great and in the preview all labels are OK. Trouble is when printing, the first line of the next row creeps on to the previous row!, so it all down to printer set up I guess?

  13. sarika says:

    hello sir i am trying to make labels in ms word its created but when i am taking printout its not come in correct format please tell me what i do

  14. sarika says:

    i am taking printout on laser printer

  15. Hi says:

    I copied A4 size label format from Google and type addresses in that but when I take print out it's not coming in order I don't know what is a problem

  16. paul says:

    pls how can i Draw and label Microsoft excel sheet

  17. Theticus Maximus says:

    Hi
    Am wanting to merge and print multiple of same labels defined by a certain excel field.
    In my excel sheet, has only 3 columns Part, Name, Count
    And I wish to print Part and Name fields only.

    When I merge, if the Count has value 3, I wish to print 3 times of the same row (Part, Name) three times and if the Count has 1, move to next row and continue. etc.

    Can you help me please?
    Thanks in advance,
    Theticus

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28