How to Print Address Labels in Excel (2 Quick Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some of the easiest ways to print address labels in Excel, then you will find this article useful. By following any of the two methods of this article you can print your desired address labels easily.


How to Print Address Labels in Excel: 2 Ways

Here, we will demonstrate 2 ways for printing address labels easily, one of them involves the use of Microsoft Excel and Microsoft Word and by this method, you can use as many columns as you need, and the other way needs a VBA code but there is a limitation that you can use only one column here.

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using the Combination of Excel and Word to Print Address Labels in Excel

In this method, we will need Microsoft Excel and Microsoft Word to print out the address labels.


Step-1: Creating a Dataset and Defining Name of This Range to Print Address Labels in Excel

Suppose we want to create the address labels for the employees of the XYZ Company and so, firstly we have created a dataset containing the Name, Phone No., Street Address, City, State, and ZIP code of the employees.

how to print address labels in Excel

Now, we have to set a name for this table.
➤ Go to Formulas Tab >> Defined Names Dropdown >> Defined Names Option.

creating dataset and defining names

Then, the New Name wizard will open up.
➤ Write down Employee_List (or whatever you want) in the Name box and select Workbook as Scope.
➤ After clicking the Refers to box, select the data range and then you will see the following formula in this box and finally press OK.

='Excel+Word'!$B$4:$G$13

Here, Excel+Word is the sheet name and $B$4:$G$13 is the data range.

creating dataset and defining names


Step-2: Selection of Labels in Microsoft Word to Print Address Labels in Excel

In this step, we will select our desired labels in which we will put the information of the employees. For this purpose, we have to open a blank Word document at first.
➤ Then go to Mailings Tab >> Start Mail Merge Dropdown >> Labels Option.

how to print address labels in Excel

Afterward, the Label Options dialog box will appear.

selection of labels

➤ Select the Page printers option and Default tray (Virtual Bin) option in the Tray box.
➤ Choose Avery A4/A5 or your preferred one from the list of the Label vendors and then select the corresponding Product number L7160.

selection of labels

If you want to explore the details of your selected label then click on the Details option and then you will see a Preview from where you can change the Margin, Pitch, or Page size.

how to print address labels in Excel

Read More: How to Print Avery Labels from Excel


Step-3: Creating a Connection of the Dataset with the Labels

Now, we will proceed with making the connection between the labels of the Word document and the dataset of the Excel document.
➤ Go to Mailings Tab >> Select Recipients Dropdown >> Use an Existing List Option.

how to print address labels in Excel

For selecting the data source, at first, select the Excel file which is Address Labels.xlsm for this article and then click Open.

connection of the dataset with labels

After that, the Select Table wizard will appear.
➤ Select the named range Employee_list as we created in Step-1 and make sure that you have checked the box saying the First row of data contains column headers and finally press OK.

how to print address labels in Excel

Then, we will see the word <<Next Record>> in each label box except for the first one.

connection of the dataset with labels


Step-4: Inserting Fields to the Labels to Print Address Labels in Excel

Here, we will assign the data of each employee in each label by inserting fields in each label.
➤ After clicking on the first label, go to Mailings Tab >> Insert Merge Field Dropdown >> Name Option (the first column name).

how to print address labels in Excel

In this way, the Name field will appear in the label box, and for entering the next field in the next line press SHIFT+ENTER.

inserting fields to the labels

➤ In the next line to enter the next field go to Mailings Tab >> Insert Merge Field Dropdown >> Phone_No Option (the second column name).

how to print address labels in Excel

Afterward, you will get the Phone_No field in the second line.

inserting fields to the labels

Similarly, insert the remaining fields in the consecutive lines.

inserting fields to the labels

To insert the fields in the rest of the labels select the Update Labels option under the Mailings Tab.

how to print address labels in Excel

Afterward, we can see that the fields have been entered in all of the label boxes.

inserting fields to the labels

To insert the values of every field in different label boxes we have to select the Preview Results option under the Mailings tab.

inserting fields to the labels

Finally, we can see the data of each of the employees on different labels.

inserting fields to the labels

For separating each label we can add borders by selecting all of the values of the labels and then following Home Tab >> Border Dropdown >> All Borders Option.

inserting fields to the labels

Afterward, we will have the following bordered and separated labels.

how to print address labels in Excel


Step-5: Printing out the Labels from Word

Before printing out the labels, go to Mailings Tab >> Finish Group >> Finish & Merge Dropdown >> Edit Individual Documents Option.

how to print address labels in Excel

Then the Merge to New Document wizard will open up.
➤ Write down 1 in the From box and 9 (total row) in the To box and press OK.

printing out the labels

For printing out these labels go to the File tab.

printing out the labels

After that, we have selected the Print option.

printing out the labels

Then, we changed the Settings according to our needs and pressed the Print option.

printing out the labels

Finally, we have got the following printed labels in a PDF file.

how to print address labels in Excel

Read More: How to Print Labels from Excel in Word


Method-2: Using a VBA Code for a Single Column to Print Address Labels in Excel

By using a VBA code, we can print address labels for only one column and so, here we are using the Street Address column.

how to print address labels in Excel


Step-1: Writing a VBA Code to Print Address Labels in Excel

Firstly, we have to write a VBA code.
➤ Go to Developer Tab >> Visual Basic Option.

how to print address labels in Excel

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

VBA Code

➤ Write the following code

Sub PrintLabels()

Dim rng As Range
Dim InputRng As Range
Dim OutRng As Range
Dim xRow As Integer
Dim xCol As Integer
Dim xArr As Variant

xTitleId = "InputBox"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
xRow = Application.InputBox("Rows :", xTitleId)
Set OutRng = Application.InputBox("Output to (single cell):", xTitleId, Type:=8)
Set InputRng = InputRng.Columns(1)
xCol = InputRng.Cells.Count / xRow

ReDim xArr(1 To xRow, 1 To xCol + 1)
   For i = 0 To InputRng.Cells.Count - 1
        xValue = InputRng.Cells(i + 1)
        iRow = i Mod xRow
        iCol = VBA.Int(i / xRow)
        xArr(iRow + 1, iCol + 1) = xValue
   Next

OutRng.Resize(UBound(xArr, 1), UBound(xArr, 2)).Value = xArr
Range("B14:D17").Select
Selection.RowHeight = 50
Selection.ColumnWidth = 30

    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

End Sub

Here, we have declared rng, InputRng, OutRng as Range, and xRow, xCol as Integer and xArr as Variant, then InputRng will store the address of the selected range, xRow will store the given row number on the basis of which the selected range will be split and xCol will be the resultant value of the division of the input range and row numbers.

The FOR loop will execute the operation for each cell of the range and the WITH statement helps us from the repetitive typing of the sheet name or range name or object name etc. within this statement, we have defined the text styles of the labels.

We have selected the range B14:D17 as the output range and so defined the row height and column width of the cells of this range according to our needs.

VBA Code


Step-2: Running the VBA Code to Print Address Labels in Excel

After pressing F5, we will get the following InputBox where we selected the range of the Street Address column without header and pressed OK.

how to print address labels in Excel

Then, another InputBox will appear, where you have to enter the number of rows by which you want to split the column (here, we have chosen it as 3) and press OK.

Running VBA Code

Afterward, we will have the final InputBox and here we have selected the output cell $B$14 in which we will have our split columns and finally press OK.

Running VBA Code

In this way, we will get the following labels in 3 rows.

how to print address labels in Excel

To get a border surrounding each label, select the labels and go to Home Tab >> Border Dropdown >> All Borders Group.

Running VBA Code

Then, we are getting borders surrounding each label.

Running VBA Code


Step-3: Printing the Labels from Excel

For printing out our created labels, we have to set a print area.
➤ Select the labels and go to Page Layout Tab >> Print Area Dropdown >> Set Print Area Option.

how to print address labels in Excel

Then, we can see the faded grey color borderline surrounding the print area.

printing labels

Now, you can print out these labels like the previous method, and then you will get the following printed PDF file.

how to print address labels in Excel

Read More: How to Print Avery 5160 Labels from Excel


Things to Remember

🔺 Create individual columns with headers for separate rows of the address labels.

🔺 Select the correct column name while inserting fields to get the corrected label formats.


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice


Download Workbook


Conclusion

In this article, we tried to cover the ways to print address labels in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo