How to Convert an Excel Sheet to Labels
By Regina Edwards
Excel spreadsheets enable users to organize contact information for mailing lists in columns containing the category or field name. Users may update information on the spreadsheet or create labels for mailing or shipping from the Excel spreadsheet (also called a worksheet). Since data on spreadsheets cannot be directly converted to labels, consider using the Microsoft Word label templates and mail merge function to generate labels. Labels created in MS Word can contain data from an Excel spreadsheet and then printed on pre-cut sheets available from office supply stores.
Open your Excel spreadsheet and confirm that the first row contains column headings for each category (such as First Name, Last Name, Street Address, etc.). If no categories are listed in row 1, then click the first cell (A1) and select "Row" under "Insert" in the top menu bar and type a category name for each column.
Sort your data, if desired, by selecting the diamond at the top left corner of your spreadsheet (or choose "Select All" under "Edit" in the top menu bar), select "Sort" under "Tools" in the top menu bar. Specify that the worksheet has a header row by selecting that option in the dialog box; select the sorting criteria using the drop down menus and click the "OK" button.
Save the latest version of the spreadsheet by selecting "Save" under "File" in the top menu bar.
Open a new blank document in Microsoft Word and select "Labels" under "Tools in the top menu bar. Note: in Microsoft Word 2007, this function is under the "Mailing" tab.
Click the "Options" button in the "Label" section and choose your label format according to the label manufacturer and product number. Note: the dialog box displays a summary of the label description for the highlighted label (e.g., type of label, label size and page size) so that you can select a label configuration if desired.
Click the "OK" button to return to the main label set-up window. Select the option, "Single label" under the section, "Number of Labels" to specify that each record will be placed on a label (instead of all labels with the same information).
Click the "Data Merge" button and check that a "Data Merge Manager" toolbar is displayed with a new document.
Select "Open Data Source" in the Data Merge Manager toolbar, under "Data Source."
Select the Excel worksheet updated in step 3 and click the "Open" button.
Select the worksheet tab from the drop down menu under the "Open Document in Workbook" section and click the "OK" button to open an "Edit Labels" wizard.
Click the "Insert Merge Field" button and select the desired category from the worksheet (identified in step 1). If desired, press the "Space" key to insert a space between categories (such as between First Name and Last Name) and type additional standard information (such as a comma between City and State, or the "Enter" key for a line break). Repeat this step to insert each category as a field and then press the "OK" button to close the Edit Label wizard.
Select the "Merge to New Document" button under the "Merge" section of the Data Merge Manager Toolbox to create a new document containing the Excel worksheet information as labels.
Save the new document containing pages of labels by selecting "Save As" under "File" in the top menu bar and type the desired document name in the field before clicking the "Save" button.
Insert the sheets of labels into the printer paper tray, if desired, and select the Print function to print labels (the Print function is accessed by selecting "Print" under "File" in the top menu bar).
- After step 11, consider formatting the label by changing the font, font size and type style by highlighting the text and selecting "Font" under "Format" to access the options.
- Microsoft Office 2007 (Excel and Word) includes a "Mailings" tab in the top menu bar to replace the Data Merge Manager Toolbox.
Regina Edwards has been a freelance writer since 1990. She has penned video scripts, instructional manuals, white papers and abstracts. She has also ghostwritten diabetes journals. Edwards is a scuba instructor and Usui and Karuna Reiki teacher. She holds a Bachelor of Science from Saint Joseph's University.