How to Print Address Labels in Excel

How to Print Address Labels in Excel
Image Credit: Ridofranz/iStock/GettyImages

Microsoft Excel serves as an excellent program for managing email lists. Large mailing lists are especially easy to manage in Excel while categorizing and organizing the contacts in a custom format.

Excel and CSV formats are used to import and export lists from mail managers, and the cell divisions mean you can separate the name, address and any other relevant information into individual columns. You can also opt to place the entire contact within a single cell.

The flexibility for skilled Excel users is convenient, but printing labels directly from Excel is not easy. While the process is not overly complex, it is not obvious either, and you can create a technical disaster. Using a mail merge process is the most efficient means of setting up labels for the printer.

Mail Merge Labels From Excel

The fastest and most effective way to print labels from your Excel mailing list is through the mail merge process. This process takes your organized Excel information and compiles every address into an individual label for the printer.

Although this sounds simple, there is a catch. The Excel sheet must be formatted to a specific layout that uses columns for each category. If you used a different method when you entered the addresses, reformatting is time-consuming.

Ideally, you are starting fresh and can type each contact in the correct format. If you have everything divided into categories already, they may only require simple label changes and adjustments to meet the formatting requirements.

Preparing the Excel Sheet

Set up the Excel spreadsheet with column headings. Each column represents a specific category. Create a separate column for the first name, last name, address, city, state and ZIP code.

The information for each individual sits in a horizontal row. Use the exact spelling, abbreviations and capitalization desired in each field. For example, use NV instead of Nevada unless you want to spell out the full state name.

Also, consider how you want the names to look on the labels. The way you style letters is a matter of personal preference. Some individuals like capitalizing all letter, while others only capitalize the first letter in each word.

You also have the option to add columns as needed. For example, if you want the first line of the label to read the company name, change the first column to reflect this information.

After the columns are all set, the next step is the tedious task of input for each contact. Add all the data and do a round or two of edits to ensure everything looks good.

Set Up the Mail Merge Name

After the contacts and all the associated information are in the spreadsheet, highlight everything including the column headings. You can drag the cursor across all the fields while holding the mouse button to highlight everything or use the select all keyboard shortcut, Ctrl+A.

After the information is highlighted, select the tab labeled Formulas and click Define Name. Type a list name in the box that pops up. For example, use Wedding_Invitations or Christmas_Cards.

If you print labels with any regularity, adding a date to each list is also useful. Contact lists are frequently updated, and the date gives you an idea about when the list was last updated. It also ensures you are printing the correct and most current version.

Save the newly named Excel contact worksheet and exit the document to continue with your mail merge.

Moving to Microsoft Word

Microsoft Word is where you print the labels. Excel is ideal for organizing and hosting the data, and it works well for quickly loading the list into other programs. Microsoft Word, however, has a built-in label printing template that lays out the addresses in the format required by the printer.

The mail merge process combines Excel for the ability to organize the fields and Word for the ability to format the labels for the printer.

Open a new Word document and click the Mailings tab to open a list of options. Click the drop-down option for Start Mail Merge to reveal a secondary menu where you may need click Start Mail Merge again before you can select Labels to open a menu of Label Options.

At this point, you need information about the printer and labels to move forward. You need to choose between continuous feed printers and page printers. Consult your printer manual to determine which type you are using. If you have a page style printer, another dropdown requires you to select the specific type of tray for your page printer. A bypass tray is fairly common.

Next, choose the label supplier. Each supplier has its own specific layout, and this is important for the printer. Microsoft, Avery and many other supplier options are listed in the dropdown. Select the one that matches the labels you purchased for the job.

Lastly, select the product number that corresponds with the product number on your labels. This is typically found on the packaging and not the label sheets themselves. The product number tells your printer the paper dimensions to ensure the printing is aligned properly with the labels.

In rare cases, the product number may not be available for your labels. Printing can still move forward, but you must manually measure the individual label size. You also need to know how many labels are on a page and how many are situated in each column and row.

To enter a custom label dimension, select the Details option in the Label Options window to show the layout for the label sheet by columns and rows. Enter the margins, columns and rows in the field provided. Click OK if this matches your configuration. If not, click Cancel and then select New Label and try again.

Type a name for your labels so that the dimensions save for future reference. Any name will suffice. Next, set the dimensions to the measured height and width. Also, enter the row and column layout. Go ahead and click OK to save the new size settings then click OK again to save all the settings.

Adding Fields to Labels

At this point, you have the labels prepped in Excel and the Word document formatted correctly to print the labels. Now you need to import the contact information to populate the labels.

In the Word document, click File and then Options and Advanced. Select the check box adjacent to Confirm file format conversion on open and choose OK. Return to the Mailings tab and click Select Recipients to open a dropdown of options. Choose Use Existing List to locate your Excel contact information on your computer. Select the file and click OK. This process connects the Excel sheet to the Word document. Although the programs are connected, the labels are not yet filled with the information.

The easiest way to import the entire list is through placeholders. The placeholder designation corresponds with the columns you created in the Excel file. For example, you type <> where you want the first name to appear on the label. Keep in mind that this sequence repeats across all the labels.

A standard address sequence looks like this:

<> <>

<>

<> <> <>

Each label corresponds directly to a column in your Excel sheet. If you wanted to add custom information, it must live in a labeled column on the sheet. Then you enter the field in your preferred placement sequence for the label.

If you are frequently loading lists using the mail merge process, an address block shortcut also exists to combine multiple fields of data under a single <

> block label. For most users, typing out the sequence suffices.

Attaching Contacts to Labels

Now that the labels are set with the sequence for each field, you can effectively load hundreds or even thousands of contacts while creating a consistent labeling sequence for the printer. The mail merge is ideal for bulk contact lists.

You must map the mail merge so Word reads the correct columns from Excel. Return to the Mailings tab, click Match Fields and choose Write and Insert Fields. A box appears with a number of labels listed alongside drop-down boxes.

The labels are common elements of an address label. It lists Title, First Name, Last Name, Street Address, and so on. In the corresponding dropdowns, you see the column labels from your Excel sheet. Many of these will already be matched but make sure each element is aligned and properly matched up. This means when you write <>, it pulls only from the First Name column in the Excel sheet.

Now that the fields are mapped and the labels are built, click Preview in the Mailings tab. Before you decide to print, do a thorough preview to view the labels. Double-check the alignment and formatting with this view.

When you are ready to print, click Finish and Merge under the Mailings tab. Click Print Documents to send the final job through to the printer. Make sure the printer is stocked with your label paper before sending the job through.

Alternatives to Excel

While Excel is a great means of running a mail merge, you can also import contacts from Outlook. Outlook allows you to export contacts to an Excel worksheet, where you can use the process as outlined above.

If you want to pull contacts directly from the Outlook email program, pull up your contact list. Use Shift+Control to select and highlight multiple contacts until every desired contact is highlighted.

Go to the Home tab and select Mail Merge. Choose Only Selected Contacts followed by All Contact Fields. Then, select New Document and Mailing Labels as the document type.

This action triggers a new Word document with a mail merge. Select Write and Insert fields under the Mailings tab and make sure everything is aligned correctly. Click Update Labels to generate the labels.

Use the preview function to review and ensure everything is properly aligned. After you edit and make any final revisions, preview again and then run Finish Mail Merge and Print to send the job to the printer.

Outlook is great at managing contacts, and running a mail merge from the program is easy. Not everyone has Outlook as an email manager, however, and the print-labels-from-Excel process remains the best alternative option for managing contacts.

references