How to Export Data From Excel to Make Labels
Creating print labels using data exported from Microsoft Excel will require a mail merge to be performed with Microsoft Word. A mail merge creates a sheet of mailing labels using data stored in fields on an Excel spreadsheet. To attain the desired results, the spreadsheet data must be properly prepared and then Word must be used to configure, organize and print the labels.
Things You'll Need
- Microsoft Excel
- Microsoft Word
Prepare the data on the Excel spreadsheet for the mail merge by using clearly identifiable column headers. At the top of each column that contains data, there should be text typed in that explains the nature of the data in that column. This is called the column header. Examples include "First Name," "Last Name," "Street Address" and "City". Do not use ambiguous names like "Column 1 Data."
Ensure that a separate column is used for each element that will be exported in the mail merge. Do not combine elements such as street address, city, state and zip code in the same column. Save and close the spreadsheet.
Open Microsoft Word. Click the "Start Mail Merge" option in the "Start Mail Merge" program group, located on the mailings tab.
Click "Labels." Choose the "Continuous-feed printers" option or the "Page printers" option, then specify which tray to use. Select the label vendor information from the drop-down menu, then select the label product number which should be listed on the label packaging or back of each label sheet. Click "OK." Word will create a document with the layout of the label sheet divided by grid lines.
Click the Microsoft Office button (the circular button bearing the Microsoft Office logo), then click "Word Options." Click "Advanced." Scroll down to the "General" section and click the checkbox next to "Confirm file format conversion on open" to place a check in it. Click "OK."
Click the "Select Recipients" option in the "Start Mail Merge" program group, located on the mailings tab. Click "Use Existing List."
Locate the Excel spreadsheet file using the "Select Data Source" dialog window. Click "Microsoft Excel Worksheets via DDE (*.xls)" option from the drop-down menu, then click "OK."
Select the cell range or spreadsheet from the Microsoft Office Excel dialog window under the "Named or Cell Range" text. Click "OK." The labels are now connected to the Excel spreadsheet data.
Click the "Edit Recipient List" option in the "Start Mail Merge" program group, located on the mailings tab.
Use the "Mail Merge Recipients" dialog window to select individual records by clicking the checkbox next to each record you want to include or clearing the checkbox for any records you want to exclude. Click the heading of each column to change the sort order between alphabetical and reverse-alphabetical order. Change the sort order by clicking the "Sort" button under the "Refine recipients list", then selecting your sort order from the "Sort Records" tab in the "Filter and Sort" dialog window.
Filter the records by clicking "Filter" on the "Refine recipient list" tab. Use the "Filter and Sort" dialog box on the "Filter Records" tab to sort the records by name, country, region, city, zip code, etc.
Populate the labels with placeholders that tell where the spreadsheet data will be put on the label. Each placeholder corresponds to the data in each column and is enclosed in chevrons. For example, if the column header for a column of spreadsheet data is "Street Address" the placeholder that should be put on the label in the place where you want the street address to appear will read "«Street Address»". To insert a placeholder, use the icons in the "Write and Insert Fields" group on the Mailings tab to choose a placeholder type, set placeholder options in the pop-up dialog box and click "OK" to insert.
Click the "Update Labels" icon in the "Write and Insert Fields" group on the Mailings tab to replicate the placeholders from the first label to all other labels on the page.
Click "Preview Results" in the Mailings tab to preview the mail merge. Click the "Finish and Merge" icon on the Mailings tab to export the data from the Excel spreadsheet into Microsoft Word. Click "File" and choose "Print" to print out the labels.