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

Get FREE Advanced Excel Exercises with Solutions!

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.


How to Print Labels in Excel Without Word: Step-by-Step Procedures

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 the 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 Avery Labels from Excel


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


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


Download Practice Workbook

This is the file containing printable labels.


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.


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.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo