Guide

Barcode labels from a spreadsheet

One row per product, one label each: your code as a barcode with its name above it, on label sheets or a roll.

Updated

Make barcode labels

Get the list ready

Any list works: an Excel or CSV file, a Numbers or OpenDocument file, or rows pasted from Excel or Google Sheets. Put the headings in the first row and one product, part or item in each row under them, for example:

SKUNamePriceLabels
MUG-1001Stoneware mug, blue14.002
MUG-1002Stoneware mug, sand14.002
CUP-1101Espresso cup, white9.504
BWL-2001Cereal bowl, blue12.00

Only the column of codes is needed. The Barcode layout finds it under a heading such as SKU, Code, Item number, Part number or Barcode, and puts a Name, Product or Description column above the barcode. Capitals, spaces and underscores in the headings don’t matter. A price and a number of labels are optional: both are below.

Your list is read in your browser and isn’t sent anywhere to make the labels.

Leading zeros and long numbers

A spreadsheet treats a cell of digits as a number. Microsoft’s own page says Excel removes leading zeros, shows large numbers in a form like 1.23E+15, and keeps only 15 digits: any digit after the 15th becomes a zero (Microsoft: Keeping leading zeros and large numbers). Other spreadsheet programs can do the same. This is what EncodeLabel does with what it gets:

  • An Excel file (.xlsx or .xls): each cell is read as the spreadsheet shows it, so a column formatted to show 00123 gives 00123. A long number shown as 8.12346E+11 is read in full, because the file still holds the whole number.
  • A CSV or text file, or rows you paste: read exactly as written. Nothing is turned into a number, so 00123 stays 00123.
  • UPC and EAN numbers that lost their first zero get it back where the check digit proves it, and the check after you add the list says how many.
  • What isn’t in the file can’t come back: a code of your own that lost its zeros, digits past the 15th, or a CSV saved with 1.23457E+11 in it. If a label shows a code like that, put it right in the spreadsheet and add the list again.

Keep the zeros in the spreadsheet

  • Excel: set the column’s number format to Text, then type or paste the codes again. A format added later doesn’t bring back zeros that are already gone. For one cell, type an apostrophe first ('00123) and Excel treats it as text.
  • Google Sheets: Format, Number, Plain text, then type or paste the codes.
  • Numbers: choose Text as the cells’ format before you type the codes.
  • A CSV file: add it to EncodeLabel as it is. Opening it in Excel and saving it again can take the zeros out.

If you have both, add the Excel file rather than a CSV saved from it: a number the CSV holds as 1.23457E+11 is still whole in the Excel file.

Choose the barcode

The layout starts with Code 128. For codes of your own there is no rule about which barcode to use; what matters is what the code holds and what will read it:

BarcodeUse it forHolds
Code 128Your own codes: SKUs, part, bin and stock numbers. It is compact and nearly every scanner reads it.Letters, digits and keyboard symbols
UPC-A or EAN-13Products sold in shops, with numbers issued by GS112 or 13 digits, the last one a check digit
QR codeWeb addresses, and anything people scan with a phone cameraAny text

To change it, pick the barcode on the label and press Change beside Type. Code 128 can’t hold accented letters or emoji; a QR code can.

EncodeLabel doesn’t issue UPC numbers. It draws the numbers you already have and checks their last digit. UPC barcodes for small sellers explains where they come from. If your products have no GS1 numbers, use Code 128 with codes of your own.

Make the labels

  1. Start the layout

    The button below opens the designer with the Barcode layout on 1 × 2⅝ inch labels, 30 per sheet: a line of text, a Code 128 barcode under it and the code in plain letters under the bars. A label you were working on stays one Undo away.

  2. Add your list

    Click Add a datalist and choose your file, or paste the rows. A CSV, an Excel file or a tab-separated text file can be read, up to 15 MB and 20,000 rows. Until you change the label, the layout fills itself in: the barcode from your column of codes, the line above it from the name column. If you’ve already changed it, choose Layouts, then Barcode again.

  3. Add the price

    Add a Text box and choose Price under Insert a field: the box then holds {{Price}}, and each label shows its own row’s price. {{Price|$0.00}} prints 12.5 as $12.50. If the barcode took the wrong column, pick it, clear its Value and choose the right one the same way.

  4. Check the labels

    Step through the rows with the arrows under the label. Check every row draws every label and lists any code that can’t become a barcode and any text that doesn’t fit, with advice on barcodes that print small.

  5. Print

    Open Print. Under How many, Every row of your datalist prints one label for each row. Print one sheet first and scan a few labels before you print the rest.

Make barcode labels On a 2.25 × 1.25 in roll On A4 (21 per sheet)

The top half of a US Letter sheet of 1 by 2⅝ inch labels, each with a product name such as Stoneware mug, blue, a Code 128 barcode and its SKU, from MUG-1001 to CAN-9101
Fifteen products from a made-up list on 1 × 2⅝ in labels, as the Barcode layout fills itself in: the name above, the SKU as a Code 128 barcode.

Want a large price as well? Choose Layouts, then Price label: the name, a large price and the barcode. It starts as UPC-A, so for codes of your own change its type to Code 128.

How many labels of each

Under How many, Every row of your datalist comes with a number “of each”: 2 makes two labels of every row.

For a different number on each row, add a column such as Labels or Qty and choose Use a quantity column. An empty cell counts as 1 and a 0 leaves the row out, so the example list above makes 2, 2, 4 and 1 labels.

A column called Qty, Quantity, Copies or Labels is chosen there for you. Check that it counts labels: in a stock list, Qty often counts what’s on the shelf.

Using a part-used sheet? Under Start at, tap the first unused label on the picture of the sheet. Only the first sheet starts there. With several labels of each row, Start each item on a new row starts each row of your list on a new row of labels, so they’re easier to peel off in order.

Label paper and printers

The barcode fills the width of the label, so a wider label gives wider bars, and a longer code gives finer ones. These are the narrowest bars of the 8-character code MUG-1001 with the Barcode layout, measured with the site’s own engine:

PaperPer sheetNarrowest bar
1 × 2⅝ in labels, compatible with Avery® 5160300.47 mm
2 × 4 in labels, compatible with Avery® 5163100.71 mm
63.5 × 38.1 mm labels, compatible with Avery® L716021 (A4)0.46 mm
½ × 1¾ in labels, compatible with Avery® 5167800.32 mm
2.25 × 1.25 in labels on a rollA roll, one at a time0.38 mm at 203 dpi
2 × 1 in labels on a rollA roll, one at a time0.25 mm at 203 dpi
1 × 1 in square labels630.17 mm, too fine

The site’s size advice is 0.25 mm or more. Finer bars need a sharp printer and a good scanner, and below about 0.17 mm it calls them too fine to print and scan reliably. So a code like MUG-1001 is too long for a Code 128 on 1 × 1 inch squares: a QR code suits a square label better. On a label printer, set its dots per inch under Your label printer in Print: the bars are put on whole dots, so they scan well. Thermal printer labels has more, and Barcode and QR code sizes gives the smallest sizes of other codes.

Before the first real sheet, open Print, then Line it up, and choose Print a test page. Print it on plain paper and hold it against a sheet of labels, up to a window. It never counts toward your labels.

Picture files instead of labels

For a catalog, a package design or a web page you may want each code as a picture rather than on a label. The barcode type pages have a Many at once… button: on the Code 128 page, for example, paste a column from your spreadsheet (one code per line), choose PNG or SVG, and download one ZIP with a picture for each line, named after what it holds. Up to 1,000 lines at a time. Each picture counts as 1 label, and the same list again within 24 hours, as PNG or SVG, isn’t counted twice.

Try it

Free for up to 100 labels a month. No account needed. Your labels are made in your browser.

Make barcode labels Printing help

Feedback

Suggestions and feedback

What would make EncodeLabel better, or what isn’t working? It goes straight to the people who run the site.

Only if you’d like a reply.

We use your email only to reply to you. Please don’t include passwords or private details. Privacy Policy

Add EncodeLabel to your Home Screen

One tap to your labels, like an app, with nothing to download from an app store.