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

Method 1 – Print Avery Labels Using Word from Excel

Let’s consider the following dataset shown in B4:F14 cells, withCompany Name, Address, City, State, and Zip Code of each of the companies.

Step 1 – Define Table of Recipients

Select the B4:F14 cells and go to Formulas and select Define Name.
A dialog box appears where you need to provide a name. We used Company_Name.

Note: Make sure there are no blank spaces between the words. Rather, you may use an underscore to separate each word.

Step 2 – Make Avery Labels in Word

Open a blank document in Microsoft Word. and go to the tab.
Navigate to Mailings > Start Mail Merge > Labels.

Choose the options as shown in the image below and click OK to close the dialog box.

Select Design > Page Borders.
A Wizard box appears. Choose Borders tab and select Grid.

This generates the grid in the blank document.

Step 3 – Import Recipient List From Excel into Word

Navigate to Mailings.
Choose Select Recipients and selectUse an Existing List.

Import the source data into Word by selecting the Excel file, in this case, Print Avery Labels.

We choose the table name Company_Name from the list.

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

Step 4 – Insert Fields in Word

Method 2 – Print Single Avery Label Without Word from Excel

Suppose we have the following dataset in the B4:B13 cells with only one column showing the Address.

Step 1 – Make a Copy of the Dataset

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 2 – Insert the VBA Code

Go to the Developer tab and select Visual Basic.

Insert a Module where you’ll paste the VBA code.
Copy and paste the code from here.

Sub Makelabels()Application.Run "EnterColumn"Cells.SelectSelection.RowHeight = 75.75Selection.ColumnWidth = 34.14With Selection.HorizontalAlignment = xlCenter.VerticalAlignment = xlBottom.WrapText = False.Orientation = 0.AddIndent = False.IndentLevel = 0.ShrinkToFit = False.ReadingOrder = xlContext.MergeCells = FalseEnd WithWith Selection.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.WrapText = False.Orientation = 0.AddIndent = False.IndentLevel = 0.ShrinkToFit = False.ReadingOrder = xlContext.MergeCells = FalseEnd WithEnd SubSub EnterColumn()Dim reference As RangeDim item As LongDim data As LongSet reference = Cells(Rows.Count, 1).End(xlUp)data = 1On Error Resume Nextincolno = InputBox("Enter Number of Columns Desired")For item = 1 To reference.Row Step incolnoCells(data, "A").Resize(1, incolno).Value = _Application.Transpose(Cells(item, "A").Resize(incolno, 1))data = data + 1NextRange(Cells(data, "A"), Cells(reference.Row, "A")).ClearContentsEnd Sub

Code Breakdown:

The code is divided into two sections.

Section 1 – Explanation of EnterColumn() sub-routine

The explanation of the VBA code is provided below.

The sub-routine is given a name, and the variables are defined.
We count the number of rows and create an InputBox to take inputs from the user.
A For loop runs as many times as specified in the InputBox.
We Transpose the column into rows, resize the cells, and remove any extra contents.