g
Printer Friendly Version

editor  
BellaOnline's Desktop Publishing / Ebooks Editor
 

Printing Address Labels from Excel File

Printing Address Labels from Excel File

Excel does not have the capability to print labels directly from your Excel file. You have to use Word's Mail Merge feature and choose the Excel file as your source data. You will have greater success if your Excel data conforms to Excel's criteria for list management. Most importantly, your data list cannot contain any empty rows. Any empty row signals the end of the list data and any data following the empty row will not be included in your label selection. It is also important that the row containing column labels be formatted differently from the data contained in the list. Generally, I use one font size larger and a bold characteristic.

When you start your mail merge process, your Excel file may or may not be open; the process will work in either case.

Here's how:
Launch Word
With a blank document open, select Tools| Letters and Mailings | Mail Merge. This menu selection will open the Mail Merge task pane on the right hand side of your screen.
Put a tick mark in the radio button next to "Labels" to indicate the type of document you want to create
Click on "Next: Starting Document" at the bottom of the Mail Merge Task Pane
Put a tick mark in the radio button next to "Change Document Layout"
Click on the link to "Label Options" to select the size and layout of the labels you want to use. Generally, the Avery Standard Label product number equivalent is stated on most on the shelf label products. Use that number to determine the correct layout of your label. The most common address label size is Avery 5660. As soon as you click on OK, Word will apply the proper layout for the size label you selected to your document.

Setting up your Labels
Click on the Show-Hide button on the Standard Toolbar to display the non-printing characters. This makes it easier for positioning your text. This is an optional step but I find it helps to see where things will position.
Your insertion point should be displayed in the first label box of your document. If it is not, then position it there before you proceed with the next step.
Click on "Next: Select Recipients" from the Mail Merge task pane
Place a tick mark in the radio button next to "Use an Existing List"
Click on Browse and navigate to where your Excel file is located on your system. Click on Open, once you have located the Excel file containing your data. The "Select Table" dialog box will be displayed.
Select the worksheet containing the address data from the "Select Table" dialog box. If your file contains only one worksheet containing data, this step will be confirmatory.
Make sure there is a in the "First Row contains Column Headers" option, if it applies.
Click on OK The Mail Merge Recipients dialog box will display, you can filter your data at this point to print all records or selected records as needed. The column drop downs filter similarly to Excel's filtered list features by choosing certain criteria. You can also deselect certain records by eliminating the  next to the record to be skipped.
Click on OK. Your document will be displayed. You will see << Next Record >> indicators in each of the label boxes of your document except the first label where you will complete your label set up.
Click on "Next: Arrange your Labels" in the Mail Merge task pane
There are some predefined arrangements but those work best when your data is highly conforming. Most likely, you will need to use the "More Items" option to manually arrange your labels to suit your application. When you choose "More Items" the Insert Merge Field dialog box will be displayed.

NOTE: As you arrange your labels, you will need to close the Merge Field dialog box to insert any line breaks, commas, periods and spaces between fields.

Thus the sequence would be for example:
 Select "Name" and click on Insert. Click on Close
 Press your Enter key to drop to the next line in the label layout
 Select "More Items" to redisplay the Insert Merge Field Dialog box
 Select "Street Address 1" and click on Insert. Click on Close
 Press your Enter key to drop to the next line in the label layout
 Select "More Items" to redisplay the Insert Merge Field Dialog box
 Select "Street Address 2" and click on Insert. Click on Close.
 Press your Enter key to drop to the next line in the label layout
 Select "More Items" to redisplay the Insert Merge Field Dialog box
 Select "City" and click on Insert. Click on Close.
 Type a comma and space
 Type a space
 Select "More Items" to redisplay the Insert Merge Field Dialog box
 Select "Zip Code" and click on Insert. Click on Close.

NOTE: The above sequence is an example. Your field headers may differ or you may have multiple Names fields to include. Furthermore, your city, state and zip could be contained in a single field.

When you have your labels arranged the next step is to replicate your labels.

Click on the "Update all Labels" button in the Mail Merge task pane. You will see the fields you selected populate the labels.


Click on "Next: Preview your Labels". You will see the live data populate the labels. At this point, Mail Merge allows you to make final changes to your recipient list.
Click on "Next: Complete the Merge"
Click on Print to print your labels.

As a matter of practice, you may want to print to a plain piece of paper before printing to your labels. By doing this, you can make sure that the layout arrangement fits on your labels. You can reduce the font size by choosing Table | Select Table to select all of the text in the entire document; then, use the Font option on the Format toolbar to select the size font you want.

Once you have completed your merge and printed your document, you can safe the file to be reused which is a great feature if your data tends to be static and you do regular mailings to the list.


Desktop Publishing / Ebooks Site @ BellaOnline
View This Article in Regular Layout

Content copyright © 2013 by Chris Curtis. All rights reserved.
This content was written by Chris Curtis. If you wish to use this content in any manner, you need written permission. Contact Laura Nunn for details.



| About BellaOnline | Privacy Policy | Advertising | Become an Editor |
Website copyright © 2013 Minerva WebWorks LLC. All rights reserved.


BellaOnline Editor