How to Print Labels in Excel Without Word (with Easy Steps)

The usual label printing process from Microsoft Excel involves mail merging spreadsheets with Microsoft Word. But some people prefer completing all the chores within Excel. Luckily, there is a way to print the labels in Excel without getting involved with Word. This tutorial will focus on how to accomplish that.


Download Practice Workbook

You can download the workbook with the dataset and the macro used for the demonstration from the link below and use it as your template.

This is the file containing printable labels.


Step-by-Step Procedure to Print Labels in Excel Without Word

To print labels directly from Excel without using Microsoft word, we just have to print out the Excel spreadsheet with the appropriate label size. We can transform cell size that fits the label size. We are going to use VBA code to perform the task for us.

First, letโ€™s assume we have the following dataset.

how to print labels in excel without word

We are going to first convert each data into labels and then print them in Excel without using any help from Word.

For the purpose of using Microsoft Visual Basic for Applications (VBA) in Excel, you first need the Developer tab on your ribbon. Click here to see how you can show the Developer tab on your ribbon. Once you have that, keep following the steps we have demonstrated to print these labels in Excel without Word.


Step 1: Copy Data to New Sheet

The VBA codeโ€™s selection here can only work properly if the cell entries start from cell A1. So, we first have to arrange our dataset containing all the labels in such a way. In case, your dataset starts anywhere other than cell A1, like ours, which started at cell B5, first copy them into a new spreadsheet and put them at the very beginning. It should look something like this.

Now it should be ready to work with the VBA code.


Step 2: Insert VBA Code

Next, we need to insert the VBA code to adjust labels to our desired size and shape. To insert VBA code-

  • First, go to the Developers tab on your ribbon.
  • Then select Visual Basic from the Codeย group.

  • As a result, the VBA window will open up. Now select the Insert tab in it and select Module from the drop-down menu.

how to print labels in excel without word

  • After that, go to the module and write down the following code.
'This Code Will Create Labels in Excel
Sub Createlabels()

    Application.Run "AskForColumn"
    
    Cells.Select
    Selection.RowHeight = 75.75
    Selection.ColumnWidth = 34.14
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    
End Sub
Sub AskForColumn()

Dim refrg As Range
Dim vrb As Long
Dim dat As Long

Set refrg = Cells(Rows.Count, 1).End(xlUp)
dat = 1
On Error Resume Next

incolno = InputBox("Enter Number of Columns Desired")

For vrb = 1 To refrg.Row Step incolno

Cells(dat, "A").Resize(1, incolno).Value = _
Application.Transpose(Cells(vrb, "A").Resize(incolno, 1))
dat = dat + 1
Next

Range(Cells(dat, "A"), Cells(refrg.Row, "A")).ClearContents
End Sub

๐Ÿ”Ž Code Explanation

There are two parts or subs in this VBA code- the CreateLabel sub and the AskForColumn sub. First, we are going to explain what is going on in the AskForColumn sub and then move on to the CreateLabel sub to explain how the code works.

Part 1:ย 

For a better understanding, we have divided the portion into different sections. Look at the figure at the end of the discussion.

๐Ÿ‘‰ Section 1: This section declares the name of the sub AskForColumn.

๐Ÿ‘‰ Section 2: We have used this section to declare three variables- refrg, vrb, and data.

๐Ÿ‘‰ Section 3: In this section, we have set values for refrg and data.

๐Ÿ‘‰ Section 4: At this point, the code shows an input box on the spreadsheet.

๐Ÿ‘‰ Section 5: In this section, a For loop is run for the number entered in the input box.

๐Ÿ‘‰ Section 6: This section of the code now resizes the cells.

๐Ÿ‘‰ Section 7: Finally, this section clears the extra contents.

how to print labels in excel without word

Part 2:ย 

Similar to the previous part, we have divided this sub into different sections too. Follow the figure at the end of the discussion for the visual part.

๐Ÿ‘‰ Section 1: This portion of the code declares the sub name Createlabels.

๐Ÿ‘‰ Section 2: This command runs the previous sub at this point of the code.

๐Ÿ‘‰ Section 3: This portion formats each cell using the VBA Cells property.

how to print labels in excel without word


Step 3: Run VBA Code

Once you have entered the code, close the VBA window. To run the code now follow these steps.

  • First, go to the Developer tab on your ribbon.
  • Second, select Macros from the Codeย group.

  • Now in the Macro box, select Createlables under the Macro name.

how to print labels in excel without word

  • Then click on Run.
  • Next, select the number of columns you desire. We are selecting 3 for the demonstration. Then click on OK.

The spreadsheet will now look like this automatically.

how to print labels in excel without word

The labels are now ready to print in Excel without any use of Word.

Read More: How to Print Labels from Excel in Word (with Easy Steps)


Step 4: Set Custom Margins

To print the labels, we need to set the correct margins for the printed page. For that, we should choose the proper size in such a way that it doesnโ€™t affect the label position or compromise any labels on the sheet. To set custom margins, follow these steps.

  • First of all, go to the Page Layout tab on your ribbon.
  • Then select the Page Setup button as shown in the figure. You can find it in the bottom right of each group.

how to print labels in excel without word

  • As a result, the Page Setup box will pop up. Now go to the Margins tab in it.
  • Then select the desired margin lengths for your printed page. We have selected the followings.

  • Once you are done, click on OK.

Step 5: Select Scaling Options for Printing

Proper scaling is important too for printing labels. For example, what we have done up to now will print the page like this.

Which certainly is not our goal. So we need to fit the sheet on one page. To do that-

  • First, go to the print preview section by pressing Ctrl+P on your keyboard.
  • On the bottom left of the view, you can find Settings. Under that, you will find the Scaling options at the end.

  • Now click on the scaling option and select Fit Sheet on One Page from the drop-down menu.

how to print labels in excel without word

The scaling for the labels will be complete at this point.

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


Step 6: Print Spreadsheet

While you are still on the print preview screen, click on Print on the top left of the view.

how to print labels in excel without word

Click on it and this will print all the labels in Excel without any help from Word.

how to print labels in excel without word


Things to Remember

๐Ÿ‘‰ Before running the VBA code, make sure all your labels start at cell A1.

๐Ÿ‘‰ Select proper margin and scaling before printing so that all the labels fit on the page. Otherwise, some might get cut off.

๐Ÿ‘‰ VBA code actions are irreversible. So make sure you have your necessary data backed up before running one.


Conclusion

These were the steps to follow to print labels in Excel without using or mail merging Microsoft Word. Hopefully, you are now able to print labels in Excel without Word. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

5 Excel Tips
You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo