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.
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.
- 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.
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.
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.
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.
- 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.
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.
- 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.
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.
Click on it and this will print all the labels in Excel without any help from 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.
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.