Eshrak Kader
Labels consist of small bits of paper that provide information about an object. In fact, this article portrays how to print Avery labels from Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
2 Methods to Print Avery Labels from Excel
Microsoft Excel makes printing labels a simple task so explore the process in detail with the appropriate illustration. Here, the first method uses Word to print Avery labels while the second method prints label without the help of Word.
So, without further delay, let’s see how we can print labels.
1. Print Avery Labels Using Word from Excel
You can print Avery labels using Excel and Word. Let’s see the process step-by-step.
Let’s consider the following dataset shown in B4:F14 cells. Here, the columns show the Company Name, Address, City, State, and Zip Code of each of the recipients.
Step 01: Define Table of Recipients
- Initially, select the B4:F14 cells and go to the Formulas > Define Name.
- Now, a dialog box appears where we provide a suitable name, in this instance, Company_Name.
📄 Note: Make sure there are no blank spaces between the words. Rather, you may use underscore to separate each word.
Step 02: Make Avery Labels in Word
- Secondly, open a blank document in Microsoft Word. and go to the tab.
- Following, navigate to Mailings > Start Mail Merge > Labels.
- Now, choose the options as shown in the image below and click OK to close the dialog box.
- Next, select Design > Page Borders.
- Immediately, a Wizard box appears, choose Borders > Grid.
This generates the grid in the blank document.
Step 03: Import Recipient List From Excel into Word
- Thirdly, navigate to Mailings however, this time choose the Select Recipients > Use an Existing List.
- Next, we import the source data into Word by selecting the Excel file, in this case, Print Avery Labels.
- In turn, we choose the table name Company_Name from the list.
This establishes a connection between the Excel worksheet and the Word document.
Step 04: Insert Fields in Word
- Fourthly, go to Mailings > Address Block and choose the Match Fields options from the dialog box.
Clearly, the column headers from the worksheet automatically match their respective fields.
- Click OK to close the dialog box.
In turn, we see a preview of the labels to correct any flaws before proceeding further.
- Next, we click the Update Labels located in the Mailings tab.
As a result, all the labels change to AddressBlock.
Step 05: Complete the Merging Process
- Finally, go to Mailings > Finish & Merge > Edit Individual Documents options.
- Next, in the dialog box check the options according to the image below and click OK.
Eventually, all the labels appear in the Word document.
- Additionally, press CTRL + P to open the print option in Word.
Moreover, you can see a preview of the labels from the preview window.
Furthermore, you can also print Avery 5160 Labels by following this linked article.
Read More: How to Create Labels in Word from Excel List (Step-by-Step Guideline)
Similar Readings
- How to Create Mailing Labels in Excel (with Easy Steps)
- How to Mail Merge Labels from Excel to Word (With Easy Steps)
2. Print Single Avery Label Without Word from Excel
If you have data spanning only one column, then you can print labels without Word. It’s a simple process, so, just follow along.
Suppose we have the following dataset in the B4:B13 cells with only one column showing the Address.
Step 01: Make a Copy of the Dataset
- Firstly, copy the dataset and paste it into a new worksheet.
📄 Note: You need to paste the data in the first column starting from the A1 cell and remove any column headers.
Step 02: Insert the VBA Code
- Secondly, go to the Developer > Visual Basic.
- Next, insert a Module where you’ll paste the VBA code.
For your ease of reference, you can copy and paste the code from here.
Sub Makelabels()
Application.Run "EnterColumn"
Cells.Select
Selection.RowHeight = 75.75
Selection.ColumnWidth = 34.14
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
End Sub
Sub EnterColumn()
Dim reference As Range
Dim item As Long
Dim data As Long
Set reference = Cells(Rows.Count, 1).End(xlUp)
data = 1
On Error Resume Next
incolno = InputBox("Enter Number of Columns Desired")
For item = 1 To reference.Row Step incolno
Cells(data, "A").Resize(1, incolno).Value = _
Application.Transpose(Cells(item, "A").Resize(incolno, 1))
data = data + 1
Next
Range(Cells(data, "A"), Cells(reference.Row, "A")).ClearContents
End Sub
Code Breakdown:
Now, I will explain the VBA code used to generate labels. In this case, the code is divided into two sections.
Section 1: Explanation of EnterColumn() sub-routine
The explanation of the VBA code is provided below.
- 1- Firstly, the sub-routine is given a name, and the variables are defined.
- 2- Next, we count the number of rows and create an InputBox to take inputs from the user.
- 3- Then, a For loop runs as many times as specified in the InputBox.
- 4- Finally, we Transpose the column into rows, Resize the cells, and remove any extra contents.
Section 2: Description of Makelabels() sub-routine
In a similar fashion, the VBA code is explained below.
- 1- In this section, the sub-routine is given a name.
- 2- Next, we execute the sub-routine.
- 3- Finally, specify the cell formatting using the Cells property.
Step 03: Running the VBA Code to Generate Labels
- Thirdly, press the F5 key to run the Makelabels() sub-routine.
- In the dialog box enter the number of columns.
You can add borders using the All Borders option in the Home tab.
Step 04: Print Labels from Excel
- Fourthly, go to the Page Layout tab and click the Page Setup arrow at the corner.
- Then, select the Margins tab and adjust the page margin as shown below.
- Next, use CTRL + P to open the Print menu.
- At this point, press the No Scaling drop-down and select Fit All Columns on One Page option.
Finally, you’re ready to print the labels. In addition, you can observe the print preview as shown in the screenshot below.
Read More: How to Create Labels Without Word in Excel (Step-by-Step Guide)
Things to Remember
- Firstly, method 2 only applies if you have a single column in your dataset.
- Secondly, format column headers so that they stand out from the rest of the data.
- Thirdly, ensure there are no empty cells as this may lead to unexpected results.
Conclusion
In essence, this article shows 2 effective methods on how to print Avery labels from Excel. So, read the full article carefully and download the free workbook to practice. Now, we hope you find this article helpful and if you have any further queries or recommendations, please feel free to comment here. Lastly, visit ExcelDemy for many more articles like this.
