Microsoft Excel is the ideal place for storing the contact and address information of customers. You can easily organize a mailing list/address list in Excel. But printing mailing labels, envelopes, greeting cards, or anything else could be hard work.
Let’s say you want to send a mass mailing to a mailing list that you maintain in an Excel spreadsheet, the best way you can do that is by using the Microsoft Word mail merge feature. By creating a mail merge document in Microsoft Word, and linking this to an Excel worksheet, you can pull the data from the Excel list, into printable labels for mailing.
How to Make Mailing Labels in Word from an Excel Sheet
With the Microsoft Word Mail Merge feature, you can create a sheet of mailing labels from an Excel Sheet that you can print. If you don’t know how to mass print your labels from Excel, then read this step-by-step guide.
Prepare your Mailing List in Excel
To create and print the mailing labels, first, you need to set up your worksheet properly. Type the column header in the first cell of each column and fill the respective information under those column headers. Since we want to create mailing labels, create a column for each element you want to add to the labels (like first name, last name, address, etc.).
For example, if you are going to create mailing labels from an Excel sheet, it would probably have these basic contact information (as column headings):
- First Name
- Last Name
- Street Address
- ZIP Code
See the screenshot below:
When you enter data in Excel, break down the information into individual columns. For example, rather than creating a single name column, split the name into separate columns such as title, first name, middle name, last name, which would make it easy to merge data with Word document.
When you are entering data, make sure your worksheet doesn’t contain any empty rows or columns. Save the worksheet once you have finished entering data.
Name the Mailing List
After you created the data set, give a name to the data presented in the worksheet. To do that,
Select the list of addresses in the Excel sheet, including the headers. Next, go to the ‘Formulas’ tab and click on the ‘Define Name’ from the Defined Names group.
A new dialog box will open, enter the name in the ‘Name’ box, and click ‘OK’. If the name has more the one word, add underscore (_) between them (No Space or Hyphen allowed).
Confirm File Format
You will need to connect the Word document to the Excel worksheet containing a mailing list to print your labels. If you are connecting Word to Excel for the first time, you will have to enable a conversion format that will allow you to convert files between the two Microsoft programs.
To do this, First, open the Microsoft Word program. Click on the ‘File’ tab and select ‘Options’ at the bottom of the left pane.
A new ‘Word Options’ window will open up. In that, click ‘Advanced in the left pane and scroll down to the ‘General’ section. Check the ‘Confirm File Format Conversion on Open’ option and click ‘OK’. This will allow you to easily import data from Excel.
Set up the Mail Merge Document in Word
Next, you need to setup the main label document in MS Word for the mailing labels you want to add.
Open a blank Word document. Go to the ‘Mailings’ and click on the ‘Start Mail Merge’ icon. Then, the ‘Labels’ option from the drop-down.
You can also choose the ‘Step-by-Step Mail Merge Wizard’ option which will guide you through the process.
The ‘Label Options’ dialog window will appear, Here, you can select your label supplier and product number. We are choosing the 3M label manufacturer as our vendor because that’s the brand we are using. But if you’re using a particular vendor, e.g. Avery, then you can choose them instead.
Next, click on the ‘Details’ button at the bottom left corner of the Label Options window and a new dialog box will open. Here, you can change the margins, height, width, pitch, and size of the label. Click on ‘OK’ after making the changes.
Now, the Word page will appear like this:
Connect the Worksheet to the Word’s Labels
Now, you have set up empty labels in Microsoft Word to receive information from Excel. Next, you should to connect the word document to the worksheet containing your mailing/address list to transfer the data to your labels.
Go to the ‘Mailings’ tab in the Word document, and select the ‘Select Recipients’ option. In the drop-down, click the ‘Use an Existing List’ option.
Navigate to the Excel spreadsheet with the mailing list in the Select Data Source window, select the file and click ‘Open’.
If you see a Confirm Data Source dialog box, choose the ‘OLE DB Database Files’ and click the ‘OK’ button.
Another pop-up window named Select Table will appear. If you have named your address list (Customer_Mailing_List), select it and click ‘OK’. If not, select the worksheet containing your list and click ‘OK’.
As you can see, the word document is now filled with address labels that say ‘«Next Record»’.
Edit Recipient List for Mail Merge
Click on the ‘Edit Recipient List’ option in the ‘Mailings’ tab.
The ‘Mail Merge Recipients’ window will appear listing all the recipients from your address list. All will be selected by default. Here, you can sort, filter, add, or remove recipients from your list.
Clear the checkbox next to the names of the recipients you don’t want in your labels.
Add Mail Merge Fields
Now, you have to add mail merge fields to the document before you can finish the merge. When you add mail merge fields to your labels, those fields will become placeholders for the column headers in your worksheet. Once the merge is done, the placeholders will be replaced with the data from your Excel mailing list.
To add mail merge fields, click on the ‘Address Block’ from Write & Insert Fields group in the Mailing tab.
Here, you can specify the appropriate format for the recipient’s name to appear on the label. You see a preview of the selected address pattern under the ‘Preview’ section. Click ‘OK’ to add the fields.
If the parts of your address block are missing or mismatch with the chosen address format, that means the column headings of your address list in Excel vary from the default Word mail merge fields. Click on the ‘Match Fields’ button to match up the right fields with the fields required for the address block.
In the ’Match Fields’ window, check if the required fields for the address block match the column in your workbook. If not, click the dropdown button next to the required field and match it up with a column heading on your Excel file. Once you’re done, click ‘OK’.
Now, ‘«AddressBlock»’ will appear in the first label of your document.
Next, you need to copy the label format and layout of the first label (<<AddressBlock>>) to the rest of the labels in the word document. To do that, click on ‘Update Labels’ in the Write & Insert pane under the Mailings tab.
Perform Mail Merge
Before we merge the Word document and Excel File, let’s take a look at how the finished labels will look.
To review your results, choose the ‘Preview Results’ option from the top bar.
Now, you can perform the actual mail merge.
You can format the currently previewed label at the Home tab. You can even change the labels’ font type, font size, font color, etc. to your liking. Once, you finish formatting the currently previewed label, click ‘Update Labels’ under the Mailings tab to apply the formatting to all the labels.
To perform the merge, go to the ‘Mailings’ tab, click the ‘Finish & Merge’ option in the Finish group. Select ‘Edit Individual Documents’ option from the drop-down.
A small pop-up window called ‘Merge to New Document’ will be prompted. In that, select ‘All’ under Merge records and click ‘OK’ to finish.
Now the information from your address list will be transferred to your labels and a new document will appear with the mailing labels from your Excel address list. You can now edit, format, print, and save this label document just like any other Word document.
Bonus Tip: Add Borders to the Labels
It’s difficult to cut the labels without borders. To add borders, click the plus (+) icon on the upper-left side of the texts.
Now all the labels in the document are selected, click the ‘Border’ icon on the floating menu and click ‘All Borders’ from the options.
Immediately, you’ll see borders between the different labels.
All that’s left to do now is print out your labels, cut them up, paste them on mails, and send out your mails.