How to Print Labels From Excel Spreadsheet?
Print label option in Excel used for various purposes. It saves a lot of time to the users in the arrangement of data and convenience in printing data presented under the single label or multiple labels. The following steps are to be followed to the creation of labels and printing them as shown in the figure.
As described in the figure, the steps should be followed clearly to create and use the labels effectively. These steps are the same for all versions of Excel from 2007 to 2016. Each step described is further required to follow the sub-steps to print the labels.
In excel, labels are printed in two ways. The following examples illustrate the use and print labels from excel sheet.
Example #1 – Print Address Labels from Excel with the Help of Word
- Step 1: In the first step, the data is arranged into the rows and columns creating the headers as shown in the figure.
As shown in the figure, header columns are created as Customer ID, Title, First Name, Last Name, Date of birth, Address, city, and state.
Give a name to the data presented in the excel sheet in the following way:
Select the list of addresses in excel sheet including the header.
Go to the “Formulas” tab and select “Define name” under the group “Defined Names”.
A dialog box called a new name is opened. In that give a name and click on “OK” to close the box.
- Step 2: Create the mail merge document in the Microsoft word. Place the cursor over the Mailings tab and select the ‘Start mail merge’ option then, choose the labels option as shown in the figure.
Then, the label options dialog box is displayed as shown in the figure.
Click on the Details tab to preview the label properties.
Here, we can change the margin, height, width, pitch, and size of the label and width, height, and size of the page. Click on ‘OK’ after making the required changes. Then, the page is displayed as shown in the figure.
Click on the Design tab to add table styles to the labels. The appearance is changed as shown below.
Step 3: Link the labels from the existing list from Excel to the mailing list.
- Go to the Mailing tab and choose the “Use an Existing List” option from the select participant’s category.
It displays the ‘select data source’ dialog box shown in below screenshot.
Choose the Excel sheet in which the table created is presented and click on open.
Choose the table name and click on the “ok” button.
Then, the page is displayed as shown in the figure.
- Step 4: Arrange the labels into the table.
Place the cursor in the first record of the table and insert the labels. To do this click on the “Insert Merge Field” button.
Click on each label one by one. While inserting the labels focus on the arrangement of labels and press “Enter” to add a label to the next line.
Next, click on the “update labels” option to apply the same format to the remaining records of the table. Then, the page is displayed.
Go to Finish and Merge and choose the option “Edit Individual Documents”.
Enter the record numbers from 1 to 12 (varies based on the table size in the excel sheet) to merge the data to the labels to the new document.
It will be displayed as given below.
- Step 5: Print the labels
To print the labels, go to print option in excel File Menu.
The Labels are printed as shown below.
Method #2 – Print Single Address Label from Excel without Word
- Step 1: Use the excel sheet which has a tiny macro to rearrange the column data to the printable address labels.
- Step 2: Enter data into column A.
- Step 3: Press CTRL+E to start the excel macro.
- Step 4: Enter the number of columns to print the labels.
Then, the data is displayed.
- Step 5: Set the custom margins as top=0.5, bottom=0.5, left=0.21975, and right=0.21975.
- Step 6: Set scaling option to “Fits all columns on one page” in the print settings and click on print.
Things to Remember
- Don’t forget saving a label created for reports and invoices. It supports in use of the labels in the future.
- Using the labels templates in word saves a lot of time in creating and printing labels
- Make ensure that excel sheet doesn’t present any empty cells, rows, and columns to avoid inappropriate results while merging in Word document
- Create individual columns for the first name, last name, and title headers instead of one to segregate the recipient information.
This has been a guide to Print Labels From Excel. Here we discuss how to print address labels from excel with and without MS Word along with some examples and downloadable excel template. You may learn more about excel from the following articles –