The article will show you how to format address labels in Excel. It is a common thing to do if you want to store mailing information about some people in a Word Document from an Excel File. Because it’s convenient to print address data from a Word Document. So it’s important to know how to format these address labels from Excel to Word Document.
Download Practice Workbook
3 Steps to Format Address Labels in Excel
In the dataset, we have information about some people with their names, address, states and ZIP codes. We will show you how to format these labels step by step.
Step-1: Inserting Data of Address Labels in Excel
When you want to format address labels in Excel, you have to maintain some procedures. Let’s go through the discussion below.
- Start your data from the A1 Otherwise, Microsoft Word won’t format your address labels. Notice that the Sheet name for the edited dataset is ‘address label’.
- Next, format the ZIP codes as ZIP from the Number Group. Although it is not necessary, it’s a good practice. Sometimes, Word may return some unnecessary errors while working with this data.
- To do that, select the ZIP codes, click on the drop-down icon of the Number Group and select More Number Formats…
- Later, in the Format Cells window, select Special >> ZIP Code and then click OK. As the addresses of this dataset are in the USA, we set the location as English (United States).
- Now your Excel sheet is ready for formatting address labels. Press CTRL+S to save your Excel Workbook.
Read More: How to Make Address Labels in Word from Excel (With Easy Steps)
- Separate Address Number from Street Name in Excel (6 Ways)
- How to Split Inconsistent Address in Excel (2 Effective Ways)
- Create Email Address with First Initial and Last Name Using Excel Formula
- How to Separate City State and Zip from Address Using Excel Formula
Step-2: Processing in Microsoft Word
In this section, we will be discussing how we will format the address labels in a Word Document. Please go through the discussion below for a better understanding.
- First, open a Microsoft Word Document.
- After that, select Mailings >> Start Mail Merge >> Labels…
- Later, the Label Options window will show up. Keep the Page printers Tray as Default tray.
- After that, select Label vendors of your choosing. In my case, I selected Avery US Letter as my Label vendor.
- Thereafter, select any of the Address Labels from the Product number: section marked in the following image. You can select any other Product number But as we are formatting address labels, it’s convenient to select Address Labels. In my case, I selected the 5159 Address Label.
- Next, just click OK.
- After that, select Mailings >> Use an Existing List… As we already made a list in our Excel Workbook.
- This command will open the Select data Source Browse your Excel Workbook, select the Excel Workbook that contains the addresses and click Open.
- You will get a warning message which tells you to confirm the data source. Just click OK.
- After that, a dialog box will appear which will tell you to select the Sheet of your Excel Workbook. Remember, our addresses were stored in the ‘address label’ Sheet. So we select it and click OK.
Step-3: Formatting Excel Address Labels in Word
This is the final step of formatting the address labels from our Excel Workbook to Microsoft Word. Let’s follow the instructions below.
- Next, you will see the following labels in your Word Document.
- After that, select the first cell and insert the headers one by one from Insert Merge Field.
- Next, select Update Labels. You can format the labels if you want. I changed the font of the names to size 14 and made it bold. Also, I change the font size of the other labels to 12.
You will see the following image after this operation. This means your address labels data from the Excel Workbook will be stored in the following format.
- Select Preview Results after that.
Finally, you will see the address labels from the Excel Workbook nice and clean.
Thus you can format Excel address labels in a Word Document.
Here, I’m giving you the dataset of this article so that you can practice these steps on your own.
Suffice to say, you will learn the basic methods on how to format address labels in Excel. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.
- How to Separate Address in Excel with Comma (3 Easy Methods)
- Format a Column for Email Addresses in Excel (2 Easy Ways)
- How to Separate City and State in Excel (3 Effective Methods)
- Formula to Create Email Address in Excel (2 Suitable Examples)
- How to Make an Address Book in Excel (An Ultimate Guide)
- Format Addresses in Excel (4 Easy Methods)