Print labels from a spreadsheet on a Mac (CSV mail merge)

If your labels are on sheets, Word's mail merge or Avery's free web tool will print a spreadsheet of names for nothing extra. If they are on a roll in a thermal printer, you want something that makes one label per row at the roll's size. Either way, most of the problems come from the spreadsheet rather than the label, so the section on preparing the CSV is the one worth reading first.

Preparing the spreadsheet

Every mail merge works the same way: the first row holds column names, and every row after it becomes one label. The rest is avoiding the ways spreadsheets quietly change your data.

• One header row, short names. name, street, city, sku. No merged cells, no title rows above the headers, no blank columns in the middle.
• Format number-like text as text. ZIP codes, phone numbers, SKUs and barcode numbers lose their leading zeros if the column is a number column, and a 13-digit EAN can turn into 4.00638E+12. Set those columns to text before you paste the data in.
• Delete empty rows at the end. A row that looks blank but has a space in it can become a blank label.
• Keep each field on one line. Line breaks inside a cell are legal in CSV, but they are the most common cause of columns shifting in a merge. Use separate columns for address lines.
• Check the longest value. Sort by length, or just scroll, and find the longest name or address. Design the label for that one, not for the typical row.

To export: in Excel, File → Save As and choose CSV UTF-8. In Numbers, File → Export To → CSV. In Google Sheets, File → Download → Comma-separated values. Word and Avery can also read an Excel file directly.

The free route: Word and Avery, on sheets

Word for Mac has done label mail merges for years. Under Mailings, choose Start Mail Merge → Labels and pick the product number printed on your label box. Then Select Recipients and use your Excel file or CSV, insert the merge fields into the first label, copy that layout to the rest of the sheet, preview, and finish the merge. Print one sheet on plain paper and hold it against a label sheet to check alignment before using the real stock.

Avery Design & Print Onlineis free, and its spreadsheet import is easier to get right than Word's. It can also fill barcodes and QR codes from a column. If you buy Avery sheets, try it before anything else.

Pages can merge from Numbers or Contacts, and it is fine for letters or one label per page. For a sheet of different labels it is unreliable, so use one of the two above.

DYMO Connect and Brother P-touch Editor both import data for their own printers. On a DYMO, DYMO Connect is the natural choice. Brother documents its database features mainly for the Windows version, so check the Mac version first.

When the labels are on a roll

Word and Avery think in sheets. A thermal printer takes one label at a time from a roll, so each row should become one page at the label's exact size, not one cell of a grid. You can force Word into that with a custom paper size and a one-label template, and it is fragile. This is the case where a label designer that merges directly is worth having.

Printing from a CSV in LabelPrint

The designer in LabelPrint handles this on the Mac and on the iPhone. The steps:

1. Design one label. Use placeholder text that is as long as your longest real value, so you see whether it fits.
2. Mark what changes. Select a text, barcode or QR element, turn on Auto-fill from CSV, and give it a field name. If you name it the same as a column header, such as name or sku, it will be matched for you. Anything you do not mark stays the same on every label.
3. Choose the CSV. Fields whose names match a column (case does not matter) are filled in automatically; pick the column by hand for the rest. Comma, semicolon and tab-separated files all work, including the encodings Excel writes.
4. Send it. Every row becomes one label in the print queue, ready to print with the printer and size of the preset you choose. On the Mac you can instead write one PDF per row to a folder.

Because a barcode can be filled from a column, the same merge makes product labels where each one carries its own SKU barcode, or asset tags with a QR code linking to each item's record. See barcode labels on a Mac for choosing the barcode type.

If what changes is only a number, you do not need a spreadsheet at all: numbered labels covers printing a series from a starting number.

Limits worth knowing

• CSV batches, PDF export and custom label sizes need Unlimited. Pricing.
• Each row prints as its own label at the label's size, which suits rolls. For a sheet of 30 addresses on Letter paper, Word or Avery lays out the sheet better.
• It reads CSV, not Excel files or Google Sheets directly. Export first.
• Fit is your job. A field longer than the box will not rearrange the label for you, which is why step one says to design for the longest value. Print one or two rows before the whole file.

Address labels on a 4x6 printer

A common use: a list of addresses printed one per 4x6 or 100 × 70 mm label for parcels you post yourself. That works well with the built-in address and shipping templates. If you are printing labels a carrier generated, though, you are not merging anything, and printing 4x6 labels on a Mac is the guide you want.