How to Print Avery Labels from Excel (2 Simple Methods)

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.

How to Print Avery Labels from Excel: 2 Methods

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 labels 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 an 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 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 Print Address Labels in Excel

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.

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 Print Labels from Excel in Word

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.

Download Practice Workbook

Conclusion

In essence, this article shows 2 effective methods for 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.

Related Articles

• How to Print Avery 8160 Labels from Excel
• How to Print Labels in Excel Without Word

<< Go Back To Print Labels in Excel | Mailing Labels in Excel | Mail Merge Excel | Learn Excel