This tutorial explains how to use mail merge to print envelopes 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.
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 envelopes 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.
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 2019, Excel 2016 and Excel 2010 and very similar in Excel 2007.
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:
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:
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:
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.
If you want to create a new sheet of mailing labels, select the Change document layout radio button and then click Label options.
For example, if you want to print Avery labels from Excel, your settings may be similar to this:
It's time to connect to your Excel address list to merge the address information with your mailing labels.
Those of you who prefer working with the ribbon can connect to an Excel worksheet by clicking Select Recipients > Use an Existing List...
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.
When you are finished refining the recipients list, click Next: Arrange your labels.
Tips:
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.
Another way to add the Address Block is to click the corresponding button on the Mailing tab > Write and Insert Fields group.
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 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.
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 : )
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:
Your custom mail merge fields and the printed mailing labels may look similar to this:
Tips:
Well, you are very close to finished : )
Tips:
You are ready to print the address labels from your Excel mailing list.
If you may want to print the same labels at some point in the future, you have two options:
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.
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.
If you want to save the merged labels as usual text, click the Edit individual labels... on the Mail Merge pane.
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.
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.
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:
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!
29 responses to "How to create labels from Excel using Mail Merge"
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.
Hello, Joanna,
For us to be able to assist you better, please describe the issue in more detail.
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?
Having same problem except no addresses transfer to the merge. Totally blank. Have tried every possible way to make it work and it jus doesn't.
Hello, Toni,
For us to be able to help you better, please send us a sample workbook with the problematic row in Excel to support@ablebits.com.
I can preview all of my labels one by one but can only see and print the first page?
Try pressing the finish and merge button at the end of the ribbon and it should load all pages of labels? :)
How can the label be set to print so it misses out blank fields?
Hello, John,
We'd recommend to initially exclude rows with blank fields in your address list in Excel.
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
Hello, Jane,
Sorry it's hard to say what causes the issue. You could try to check the printer settings on your PC.
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?
Hello, Chris,
Unfortunately it's hard to say what causes the issue. Please try to check the printer settings on your PC.
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.
Hello, PL,
Unfortunately it's hard to say what causes the issue. Please try to check the printer settings on your PC.
I need the solfware to print labels form my excel address list
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
This was very helpful. Thank you so much
Extremely clear and very useful. Great job.
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?
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
i am taking printout on laser printer
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
pls how can i Draw and label Microsoft excel sheet
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
Theticus:
What you want to accomplish will require some VBA code and creating some buttons on your sheet or a dashboard all of which are beyond the scope of this blog. However, I can point you in the right direction and you can take it from there.
MrExcel has a couple of discussions that deal with this topic. You might want to start at:
https://www.mrexcel.com/forum/excel-questions/398935-vba-print-x-number-copies-based-number-specified-cell.html
Then go to another site extendoffice at:
https://www.extendoffice.com/documents/excel/3815-excel-dynamic-print-area.html
I recently merged an excel document with a word document. I have 76 lines (records) in excel and the merge did not accept all records. When I reversed the alphabetically order from A to Z to Z to A, the merge did not same number of files from the top of the list, but not 100% of the files. Either A to Z or Z to A left out the last few files in the list which were not the same files because I had reversed them. Is there a limit in the number of lines/records? than can be merged between Excel and Word?
When creating mailing labels by merging data from an Excel document with a Word document only the first sheet of labels shows up.
1. How do I access the remaining sheets?
2. Assuming I can see the other sheets, how can I print the labels without printing sheet 1 for example?
Thanks for your help.
Good morning,
Is there a way to create page breaks in mailing labels? I have created an address list using Excel with forced page breaks. When I merge the labels using Word, it doesn't recognize the page breaks and prints the labels continuously without breaking. Any help would be appreciated. Thanks.