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.


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. Admittedly, I have skipped the details about labels which you may explore if you want Here, the first method uses Word while the second method prints label without 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.

Dataset 1

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.

How to Print Avery Labels from Excel Using Word

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.

How to Print Avery Labels from Excel Using Word

  • Now, choose the options as shown in the image below and click OK to close the dialog box.

Avery Labels

  • Next, select Design > Page Borders.
  • Immediately, a Wizard box appears, choose Borders > Grid.

Grid Layout

This generates the grid in the blank document.

Grid

Step 03: Import Recipient List From Excel into Word

  • Thirdly, navigate to Mailings however, this time choose the Select Recipients > Use an Existing List.

How to Print Avery Labels from Excel Using Word

  • Next, we import the source data into Word by selecting the Excel file, in this case, Print Avery Labels.

How to Print Avery Labels from Excel Using Word

  • In turn, we choose the table name Company_Name from the list.

How to Print Avery Labels from Excel Using Word

This establishes a connection between the Excel worksheet and the Word document.

Grid Layout

Step 04: Insert Fields in Word

  • Fourthly, go to Mailings > Address Block and choose the Match Fields options from the dialog box.

How to Print Avery Labels from Excel Using Word

Clearly, the column headers from the worksheet automatically match their respective fields.

  • Click OK to close the dialog box.

How to Print Avery Labels from Excel Using Word

In turn, we see a preview of the labels to correct any flaws before proceeding further.

How to Print Avery Labels from Excel Using Word

  • Next, we click the Update Labels located in the Mailings tab.

How to Print Avery Labels from Excel Using Word

As a result, all the labels change to AddressBlock.

How to Print Avery Labels from Excel Using Word

Step 05: Complete the Merging Process

  • Finally, go to Mailings > Finish & Merge > Edit Individual Documents options.

How to Print Avery Labels from Excel Using Word

  • Next, in the dialog box check the options according to the image below and click OK.

How to Print Avery Labels from Excel Using Word

Eventually, all the labels appear in the Word document.

Grid Layout

  • Additionally, press CTRL + P to open the print option in Word.

Print Option

Moreover, you can see a preview of the labels from the preview window.

How to Print Avery Labels from Excel Using Word

Read More: How to Create Labels in Word from Excel List (Step-by-Step Guideline)


Similar Readings


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.

Dataset 2

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.

Pasted Data

Step 02: Insert the VBA Code

  • Secondly, go to the Developer > Visual Basic.

How to Print Avery Labels from Excel Without Word

  • Next, insert a Module where you’ll paste the VBA code.

How to Print Avery Labels from Excel Without WordFor 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

How to Print Avery Labels from Excel Without Word

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.

Code Explanantion

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.

Code Explanantion

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.

How to Print Avery Labels from Excel Without Word

You can add borders using the All Borders option in the Home tab.

Grid Layout

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.

Page Margin

  • 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.

Print Option

Finally, you’re ready to print the labels. In addition, you can observe the print preview as shown in the screenshot below.

How to Print Avery Labels from Excel Without Word

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

To conclude, I hope this article helped you understand how to print Avery labels from Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo