How to create a barcode in Excel and print labels

Excel stores product codes but cannot draw bars on its own. You add a barcode font, an encoder that prepares text for it, or a Word mail merge that draws each symbol from the sheet. Code 39 is the easiest type. Code 128 needs a calculated check character, and retail UPC and EAN numbers come from GS1, not your spreadsheet. Whichever route you take, scan a printed test page before you print the batch.

Does Excel have a barcode generator?

No. Excel has no barcode function, chart type or ribbon button.

An internal code should follow a scheme like the one in SKU numbers, and 1D vs 2D barcodes compares linear symbols with QR codes.

Use a code 39 barcode font

Code 39 encodes uppercase letters, digits and seven symbols: hyphen, period, space, dollar sign, slash, plus and percent. Each character has one fixed pattern, and an asterisk marks the start and stop. That one-to-one mapping is why a barcode font for Excel works best with this type. Vendors such as IDAutomation and Azalea Software sell Code 39 fonts, and the open source Libre Barcode family on Google Fonts includes one; these are examples, not a ranking.

1. Install the font in Windows and restart Excel.
2. Enter each code in column A as text. In this fictional sheet, A2 holds TEA-020.
3. In B2, enter =CHAR(42)&UPPER(A2)&CHAR(42) and fill down. CHAR(42) is the asterisk; scanners drop it from the result.
4. Set column B to the barcode font at a large size, with rows tall enough for the bars.

Install the font on every computer that opens or prints the workbook, or Excel substitutes another font and prints plain text. Code 39 symbols are wide, so keep codes short.

Code 128 in Excel needs an encoder

Code 128 makes a shorter symbol, but its bars are not a one-to-one copy of the text. Each symbol carries a start character, the encoded data, a check character calculated from every character's value and position, and a stop character. A SKU typed in a Code 128 font gives bars that look right and do not scan.

The encoder, a worksheet formula, VBA function or add-in, usually comes from the font's vendor, because fonts map characters differently. Apply the font to the encoder's output in a second column. If your workbook cannot run macros, use an add-in or let Word draw the symbol during the merge below.

A UPC barcode in Excel starts with a GS1 number

UPC-A and EAN-13 numbers identify one product at checkout in any store, so they are licensed, not made up. GS1 US explains how to get a UPC, as a single GTIN or through a company prefix, and each product variation needs its own number. Do not invent one or copy it from a similar item, because it may belong to another company's product. For goods you resell, record the number on the manufacturer's packaging.

Format that column as Text before you paste. Excel's General format drops leading zeros and shows numbers of 12 or more digits in scientific notation. The last digit is a check digit, so a mismatch means a typing error. UPC and EAN fonts need an encoder, like Code 128.

Print barcode labels from Excel with a Word mail merge

Word can read the sheet and draw each symbol with a field code, so mail merge barcode labels need no font or encoder. Microsoft's guide to adding barcodes to labels says Word no longer has a built-in barcode tool but supports the DisplayBarcode and MergeBarcode field codes for UPC, Code 39, Code 128 and other types. These steps follow it in Word for Windows.

1. Give the sheet a header row, such as Product, SKU and Price, and one row per label. Repeat a row for extra copies.
2. In Word, choose Mailings, Start Mail Merge, Labels, and pick your label sheet's vendor and product number.
3. Choose Select Recipients, Use an Existing List, and open the workbook.
4. On the first label, insert the Product and Price merge fields.
5. Press Ctrl+F9 for field braces, since typed braces do not work. Build DisplayBarcode "{ MergeField SKU }" CODE128 \t, using a second Ctrl+F9 for the inner braces. The \t switch adds the code as text.
6. Choose Update Labels, then Preview Results.
7. Print one page on plain paper, hold it against a label sheet to check alignment, and test-scan it.

Why is my Excel barcode not scanning?

• Missing asterisks. A Code 39 cell without its start and stop characters will not scan.
• An unencoded font. Code 128, UPC and EAN fonts need the encoder's output.
• Unsupported characters. Standard Code 39 has no lowercase letters or underscores.
• No margin around the bars. The barcode quiet zone is the blank space on each side of the symbol. A cell border, neighboring text or a label edge inside it stops the read. GS1 US placement and printing guidelines call for clear space left and right, dark bars on a light background, no red bars and full bar height.
• Shrunk bars. Shrink to fit, Fit Sheet on One Page and scaling below 100% thin the narrowest bars. Print at actual size and set the size with the font.
• Scanner settings. Some scanners ship with certain symbologies off; the manual shows how to enable them.

Scan every label on the test page with the shop's own scanner and software, and compare each result with its cell before printing the rest.

Skip the font with a free label tool

The free shelf label maker draws barcoded price labels without a font. It renders Code 128 from your SKU and accepts an existing 12-digit UPC-A or 13-digit EAN-13 number after checking its length and check digit. Copy rows from Excel, header included, into its Import from a spreadsheet panel. It reads columns such as Product, SKU, Price, Barcode Type, Barcode and Copies. A batch holds up to 20 products and 60 labels at 90 × 45 mm, 60 × 40 mm or 180 × 60 mm on plain A4 or Letter paper. It needs no account, and it does not assign product numbers. The shelf labels guide covers what each label should show.

← All posts