Tanjima Hossain
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.
Download Workbook
2 Ways to Print Address Labels in Excel
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.
Now, we have to set a name for this table.
➤ Go to Formulas Tab >> Defined Names Dropdown >> Defined Names Option.
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.
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.
Afterward, the Label Options dialog box will appear.
➤ 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.
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.
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.
For selecting the data source, at first, select the Excel file which is Address Labels.xlsm for this article and then click Open.
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.
Then, we will see the word <<Next Record>> in each label box except for the first one.
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).
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.
➤ In the next line to enter the next field go to Mailings Tab >> Insert Merge Field Dropdown >> Phone_No Option (the second column name).
Afterward, you will get the Phone_No field in the second line.
Similarly, insert the remaining fields in the consecutive lines.
To insert the fields in the rest of the labels select the Update Labels option under the Mailings Tab.
Afterward, we can see that the fields have been entered in all of the label boxes.
To insert the values of every field in different label boxes we have to select the Preview Results option under the Mailings tab.
Finally, we can see the data of each of the employees on different 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.
Afterward, we will have the following bordered and separated labels.
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.
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.
For printing out these labels go to the File tab.
After that, we have selected the Print option.
Then, we changed the Settings according to our needs and pressed the Print option.
Finally, we have got the following printed labels in a PDF file.
Read More: How to Print Labels in Excel (Step-by-Step Guideline)
Similar Readings
- How to Print Gridlines in Excel (2 Ways)
- Excel VBA Debug Print: How to Do It?
- How to Print Landscape in Excel (3 Easy Methods)
- Print Sheet on One Page in Excel (9 Easy Ways)
- How to Print Excel Sheet in A4 Size (4 Ways)
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.
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.
Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.
After that, a Module will be created.
➤ 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 SubHere, 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.
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.
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.
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.
In this way, we will get the following labels in 3 rows.
To get a border surrounding each label, select the labels and go to Home Tab >> Border Dropdown >> All Borders Group.
Then, we are getting borders surrounding each label.
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.
Then, we can see the faded grey color borderline surrounding the print area.
Now, you can print out these labels like the previous method, and then you will get the following printed PDF file.
Read More: VBA Code for Print Button in Excel (5 Examples)
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.
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.
