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

Let’s consider the following dataset shown in B4:F14 cells, with Company 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 select Use 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

• Go to Mailings and select Address Block.
• Choose the Match Fields options from the dialog box.

• The column headers from the worksheet automatically match their respective fields.
• Click OK to close the dialog box.

We see a preview of the labels to correct any flaws before proceeding further.

• Click on Update Labels located in the Mailings tab.

• All the labels change to AddressBlock.

Step 5 – Complete the Merging Process

• Go to Mailings, select Finish & Merge, and choose Edit Individual Documents.

• In the dialog box, check the option “All” and click OK.

Eventually, all the labels appear in the Word document.

• Press CTRL + P to open the print option in Word.

• You can see a preview of the labels from the preview window.

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

• Insert a Module where you’ll paste the VBA code.
• 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:

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

Section 2 – Description of Makelabels() sub-routine

• The sub-routine is given a name.
• We execute the sub-routine.
• We specify the cell formatting using the Cells property.

Step 3 – Running the VBA Code to Generate Labels

• 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 4 – Print Labels from Excel

• Go to the Page Layout tab and click the Page Setup arrow at the corner.
• Select the Margins tab and adjust the page margin as shown below.

• Use CTRL + P to open the Print menu.
• Press the No Scaling drop-down and select Fit All Columns on One Page option.

• You’re ready to print the labels.

Things to Remember

• Method 2 only applies if you have a single column in your dataset.
• Format column headers so that they stand out from the rest of the data.
• Ensure there are no empty cells as this may lead to unexpected results.

Download Practice Workbook