Address labels from Excel

Keep each part separate: name, company if you use one, street, a second street line or apartment if you have them, then city, state and ZIP code. Do not put a whole address into one cell with line breaks inside it; that reads back as one run of text and you lose control of where the lines fall.

Give the first row plain column names. “Name”, “Address”, “City”, “State” and “ZIP” are enough. The tool recognises those, and common variations such as “First Name” and “Last Name”, “Street Address”, “Zip Code” or “Postal Code”, and builds the label for you: first and last name share the top line, and city, state and ZIP share the bottom one with a comma after the city.

If a row has no apartment or company, that line simply drops out and the rest close up, so the label never shows a gap. If the layout it chooses is not what you want, edit the text in step 2: each line there is a line on the label, and anything in braces is a column.

An apartment, suite or unit number belongs at the end of the street line, as in 310 Alder Street Apt 2B, rather than on a line of its own. If it will not fit there, the line directly above the street is the next best place. Use the two-letter state abbreviation, and a ZIP+4 code if you have it; the extra four digits help sorting but are not required.

Capitals and missing punctuation are what Publication 28 recommends for machine-read mail, and bulk mailers use them. For letters and cards, ordinary mixed case prints and delivers perfectly well. Whatever you choose, keep it consistent down the list.

The Postal Service publishes the standard at pe.usps.com. This tool does not verify addresses against its database; it prints what your list says.

The safest fix is before the data goes in: select the ZIP column, set Format Cells to Text, then type or paste the codes. A custom number format of 00000 also shows the zero on screen, and this tool prints what Excel shows, so that works here too. It does not work for a Word mail merge, which reads the number underneath.

If the zeros are already gone, the tool restores them. In a column named ZIP or ZIP code, a three- or four-digit value is padded back to five digits; in a column named postal code or postcode, only on rows whose State is a US state, because 2000 is a real postcode in Sydney and 0150 in Oslo. A row whose Country names another country is never changed. The summary in step 4 says how many were padded, so nothing happens silently.

If your list has long company names or international addresses, that width starts to bite and the text is shrunk to fit. When that happens, move up rather than accept small type.

Avery 5161 keeps the one-inch height at four inches wide, which absorbs long names without shrinking. The taller 5162 adds a third more height for four or five lines or larger type, and 5163 is the two-by-four inch shipping label. For return addresses, the small 80-per-sheet return address labels are the usual choice.

Load four hundred rows against an Avery 5160 sheet and you get thirteen full sheets and one with ten labels on it; the summary in step 4 says exactly that before you download anything, and the preview pages through every sheet. There is no per-sheet setup and no limit on the list beyond 5,000 rows and 10,000 labels a PDF.

If some recipients get more than one piece, add a column with the number of copies and choose it under “Copies of each row”. Each row then repeats that many times, which saves duplicating rows by hand.

The most wasteful habit in label printing is throwing away most of a sheet because the last job needed seven labels. Open “Printing on a partly used sheet?” in step 3 and pick the first label that is still on the sheet. The picker is drawn like the sheet, so you can count across and down on the real one and click the matching spot.

Labels are unforgiving in a way a screen is not: a blank name or a stray comma becomes a printed mistake stuck to an envelope. Sorting brings empty cells and shifted columns to the top or bottom where you will see them.

Rows with nothing to print are skipped rather than printed as blank labels, so a trailing empty row costs nothing. Rows with some content still print, deliberately: a partly filled row is more likely real data than an accident. Excel’s Data, Remove Duplicates removes repeated households before you start.