How to Print Address Labels in Excel
This article describes how to use Excel to generate small runs of mailing labels without setting up a mail merge routine.
Things You'll Need
- Microsoft Excel 2003 2007
- Avery brand 5160 labels, or another brand of the same size
It's common to use data in an Excel workbook in tandem with address label forms in Word using a process called mail merge. Working exclusively in Excel requires more formatting and a bit of trial-and-error with printing. However, this is a one-time setup, and your workbook can be saved and used as a template for short runs of manually entered address labels any time. This example uses labels compatible with Avery's 5160 label layout.
Set Up the Label Page
Select the range from cell A1 to cell C10. This highlights a grid of 30 cells, corresponding to the 30 cells on a 5160-style sheet of labels.
Set the margins by first clicking the Page Layout tab on the ribbon then Margins. Enter the following values:
- Top and Bottom: 0.5 inches
- Left and Right: 0.19 inches
- Center on page: check both boxes
Click OK when you're done.
Change Excel's ruler to Imperial units if needed by clicking the File tab on the ribbon, Options, Advanced and then navigating down to Display. Change Ruler units to Inches.
Click column A in the column headers and drag to column C to select the three columns of labels. Right-click the highlighted columns and select Column Width. Enter a value of 32 and click OK.
Click row 1 in the row headers and drag to row 10 to select the 10 rows of labels. Right-click the highlighted rows and select Row Height. Enter a value of 73 and click OK.
Your 30 labels should now appear within dotted lines between columns C and D and rows 10 and 11. The dotted lines represent printed page boundaries.
Select all label cells by clicking cell A1 and dragging to C10. Right-click the highlighted cells and select Format Cells.
Select the Alignment tab and set both horizontal and vertical text alignment to Center. Check the Wrap text box. The workbook is now ready for data entry. Save the blank workbook to use as a template by clicking File and Save As. Select a location and a name for your mailing label template.
Use the template by entering name and address data directly in each of the 30 cells. Move between cells by clicking Enter. To format name and address within a cell, add spaces to bump data to the next line.
- Data is centered in each cell to improve the accuracy of text printing on each label. Avoid formatting text close to any cell edge to ensure it prints as you wish.
- Templates can be made in the same fashion for other label formats as well. Adjust the number of cells, margin sizes, column width and row height to match the label format.
Click File on the ribbon and click Print. The print screen shows your labels as they will be printed. Check that the formatting for each label is correct. There is no need to change default printer settings. Load 5160-compatible labels into your printer. Select it on the Print menu and click Print.