How to mail merge and print labels from Excel

The tutorial explains how to do a mail merge from an Excel spreadsheet for labels. You will learn how to prepare your Excel address list, set up a Word document, make custom labels, print them and save for later use.

Last week we started to look into the capabilities of Word Mail Merge. Today let's see how you can leverage this feature to make and print labels from an Excel spreadsheet.

• Mail merge labels from Excel
  • Prepare Excel spreadsheet for mail merge
  • Set up Word mail merge document
  • Connect to Excel address list
  • Select recipients
  • Arrange mailing labels
  • Preview labels
  • Print mailing labels
  • Save labels for later use
• Make a custom layout of mailing labels
• Add missing address elements

How to mail merge labels from Excel

If you've had a chance to read our Mail Merge tutorial, a larger part of the process will be familiar to you because making labels or envelopes from Excel is yet another variation of the Word Mail Merge feature. Whatever intricate and intimidating the task may sound, it boils down to 7 basic steps.

Below, we'll take a closer look at each step using Microsoft 365 for Excel. The steps are essentially the same in Excel 2021, Excel 2019, Excel 2016, Excel 2010, and very similar in Excel 2007.

Step 1. Prepare Excel spreadsheet for mail merge

In essence, when you mail merge labels or envelopes from Excel to Word, the column headers of your Excel sheet are transformed into mail merge fields in a Word document. A merge field can correspond to one entry such as first name, last name, city, zip code, etc. Or, it can combine several entries, for example the «AddressBlock» field.

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

Before starting a mail merge, invest some time in setting up your Excel spreadsheet to ensure it is properly structured. This will make it easier for you to arrange, review and print your mailing labels in Word and save more time in the long run.

Here are a few important things to check:

• Create one row for each recipient.
• Give clear and unambiguous names to your Excel columns such as First Name, Middle Name, Last name, etc. For address fields, use the full words such as Address, City, State, Postal or Zip code, Country or Region.

  The screenshot below shows a list of the Address block fields used by Word. Giving identical names to your Excel column will help Mail Merge to automatically match the fields and save you the trouble of mapping the columns manually.
  

• Split the recipient information into very small pieces. For example, instead of a single Name column, you'd better create separate columns for salutation, first name and last name.
• Format the Zip code column as text to retain leading zeros during a mail merge.
• Make sure your Excel sheet does not contain any blank rows or columns. When doing a mail merge, empty rows may mislead Word, so it will merge only part of the entries believing it has already reached the end of your address list.
• To make it easier to locate your mailing list during the merge, you can create a defined name in Excel, say Address_list.
• If you create a mailing list by importing information from a .csv or a .txt file, be sure to do that right: How to import CSV files into Excel.
• If you plan to use your Outlook contacts, you can find the detailed guidance here: How to export Outlook contacts to Excel.

Step 2. Set up mail merge document in Word

With the Excel mailing list ready, the next step is to configure the main mail merge document in Word. The good news is that it's a one-time setup - all labels will be created in one go.

There are two ways to do a mail merge in Word:

• Mail Merge Wizard. It provides step-by-step guidance which may be helpful for beginners.
• Mailings tab. If you are pretty comfortable with the mail merge feature, you can use the individual options on the ribbon.

To show you an end-to-end process, we are going to mail merge address labels using the step-by-step wizard. Also, we'll point out where to find the equivalent options on the ribbon. Not to mislead you, this information will be provided in (brackets).

1. Create a Word document. In Microsoft Word, create a new document or open an existing one.

   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. Start mail merge. Head over to the Mailings tab > Start Mail Merge group and click Step by Step Mail Merge Wizard.
   

3. Select document type. The Mail Merge pane will open in the right part of the screen. In the first step of the wizard, you select Labels and click Next: Starting document near the bottom.

   (Or you can go to the Mailings tab > Start Mail Merge group and click Start Mail Merge > Labels.)
   

4. Choose the starting document. Decide how you want to set up your address labels:
   • Use the current document - start from the currently open document.
   • Change document layout - start from a ready-to-use mail merge template that can be further customized for your needs.
   • Start from existing document - start from an existing mail merge document; you will be able to make change to its content or recipients later.

   As we are going to set up a mail merge document from scratch, we select the first option and click Next.
   

   Tip. If the Use the current document option is inactive, then select Change document layout, click the Label options… link, and then specify the label information.
   

5. Configure label options. Before proceeding to the next step, Word will prompt you to select Label Options such as:
   • Printer information - specify the printer type.
   • Label information - define the supplier of your label sheets.
   • Product number­ - pick the product number indicated on a package of your label sheets.

   If you are going to print Avery labels, your settings may look something like this:
   

   Tip. For more information about the selected label package, click the Details… button in the lower left corner.

   When done, click the OK button.

Step 3. Connect to Excel mailing list

Now, it's time to link the Word mail merge document to your Excel address list. On the Mail Merge pane, choose the Use an existing list option under Select recipients, click Browse… and navigate to the Excel worksheet that you've prepared.

(Those of you who prefer working with the ribbon can connect to an Excel sheet by clicking Select Recipients > Use an Existing List… on the Mailings tab.)

The Select Table dialog box will pop up. If you have given a name to your mailing list, select it and click OK. Otherwise, select the entire sheet - you will be able to remove, sort or filter recipients later.

Step 4. Select recipients for mail merge

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

Here are some of the actions you can perform to refine your address list:

• To exclude a particular contact(s), clear a check box next to their name.
• To sort the recipients by a certain column, click the column's heading, and then choose to sort either ascending or descending.
• To filter the recipient list, click the arrow next to the column heading and pick the desired option, e.g. blanks or non-blanks.
• For advanced sorting or filtering, click the arrow next to the column name, and then select (Advanced…) from the drop-down list.
• A few more options are available in the Refine recipient list section near the bottom.

When the recipient list is all set, click Next: Arrange your labels on the pane.

Step 5. Arrange layout of address labels

Now, you need to determine what information to include in your mailing labels and decide on their layout. For this, you add placeholders to the Word document, which are called mail merge fields. When the merge is finished, the placeholders will be replaced with the data from your Excel's address list.

To arrange your address labels, follow these steps:

1. In your Word document, click where you want to insert a field, and then click the corresponding link on the pane. For mailing labels, you'd normally need only the Address block.
   

2. In the Insert Address Block dialog box, select the desired options, check the result under the Preview section and click OK.
   

When you are finished with the Address Block, click OK.

The «AddressBlock» merge field will appear in your Word document. Note that it's just a placeholder. When the labels are printed out, it will be replaced with the actual information from your Excel source file.

When you are ready for the next step, click Next: Preview your labels on the pane.

Step 6. Preview mailing labels

Well, we are very close to the finish line :) To see how your labels will look like when printed, click the left or right arrow on the Mail Merge pane (or the arrows on the Mailings tab, in the Preview Results group).

When you are satisfied with the appearance of your address labels, click Next: Complete the merge.

Step 7. Print address labels

You are now ready to print mailing labels from your Excel spreadsheet. Simply click Print… on the pane (or Finish & Merge > Print documents on the Mailings tab).

And then, indicate whether to print all of your mailing labels, the current record or specified ones.

Step 8. Save 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 Word mail merge document connected to the Excel sheet

   Save the Word document in the usual way by clicking the Save button or pressing the Ctrl + S shortcut. The mail merge document will be saved "as-is" retaining the connection to your Excel file. If you make any changes to the Excel mailing list, the labels in Word with be updated automatically.

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

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

2. Save merged labels as text

   In case you wish to save the merged labels as usual text, click the Edit individual labels… on the Mail Merge pane. (Alternatively, you can go to the Mailings tab > Finish group and click Finish & Merge > Edit individual documents.)
   

   In the dialog box that pops up, specify which labels you want to edit. 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.

How to make a custom layout of mailing labels

If none of the predefined options in the Address Block is suitable for your needs, you can create a custom layout of your address labels. Here's how:

1. When arranging the labels layout, place the cursor where you want to add a merge field.
2. On the Mail Merge pane, click the More items… link. (Or click the Insert Merge Field button on the Mailings tab, in the Write & Insert Fields group).
3. In the Insert Merge Field dialog, select the desired field and click Insert.
   

Here's an example of how your custom labels may eventually look like:

How to add missing address elements

It may happen that the 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 in your Excel sheet differ from the default Word Mail Merge fields.

For example, you have chosen the Salutation, First name, Last name, Suffix format, but the preview shows only the First name and Last name.

In this case, first verify whether your Excel source file contains all the required data. If it does, click the Match Fields… button in the lower right corner of the Insert Address Block dialog box, and then match the fields manually.

For the detailed instructions, please see How to get mail merge to match fields.

Hurray! We finally did it :) Thanks a lot to everyone who has read our Mail Merge Labels tutorial to the end!

You may also be interested in

• Outlook mail merge to send personalized bulk email
• How to mail merge letters from Excel to Word
• Mail Merge formatting issues in Word
• How to print Excel spreadsheets
• How to set print area in Excel