GUIDE · EXCEL

How to create EAN-13 barcodes in Excel without VBA or macros

Turn a column of EAN/GTIN numbers into EAN-13 and EAN-8 barcodes with one formula and a barcode font. No VBA, no macros, no add-in and no macro-enabled workbook.

What you need

• Excel for Windows. The barcode is drawn by the font, so Excel needs no add-in. Try the free demo in your own Excel version before you buy.
• Complete EAN numbers: 13 digits for EAN-13 (GTIN-13) or 8 digits for EAN-8 (GTIN-8), including the check digit. Barcodes on retail products need numbers that GS1 has assigned to your company.
• The AlphaEAN font. The free demo is enough to follow this guide; its barcodes carry a DEMO watermark.

Why most Excel barcode methods need VBA

An EAN-13 barcode is not the digits drawn as bars one by one. The first digit decides which pattern each of the next six digits uses, and guard bars mark the start, centre and end of the code. A typical EAN barcode font cannot work that out, so the number first goes through an encoder, usually a VBA function, and the font then draws the encoded string. That means a macro-enabled .xlsm workbook, macro security prompts and code to maintain, and many company policies block macros altogether.

AlphaEAN does the encoding inside the font with OpenType rules. Excel only shows the number with the font applied, which turns Excel into an EAN-13 barcode generator for your whole product list.

Step 1: Keep the EAN numbers as text

Put your EAN numbers in column A, starting from A2. Before you paste or type them, select column A and set its number format to Text (Home › Number Format › Text).

Excel treats long digit strings as numbers: it can show a 13-digit EAN as 1.23457E+12, and it drops leading zeros, so 0012345678905 becomes 12345678905. With Text format, every digit stays exactly as entered.

Step 2: Check the check digits

A barcode with a wrong check digit will not scan as the intended product. To check a whole list of EAN-13 codes, enter this formula next to the first code and copy it down:

=IF(MOD(10-MOD(SUMPRODUCT(--MID(A2,{1,2,3,4,5,6,7,8,9,10,11,12},1),{1,3,1,3,1,3,1,3,1,3,1,3}),10),10)=--RIGHT(A2,1),"OK","Wrong check digit")

The formula multiplies the first 12 digits alternately by 1 and 3, adds the results and compares the expected check digit with the last digit. It uses English (US) separators; if your Excel uses semicolons between arguments, adjust the separators. You can also paste the whole list into the free check digit calculator, which handles EAN-8 and GTIN-14 too.

Step 3: Add the delimiters with one formula

AlphaEAN draws an EAN-13 written between asterisks and an EAN-8 written between percent signs. In B2, enter the formula for your code type:

Copy the formula down column B for every product. Column A keeps the readable numbers, and column B holds the text that becomes the barcode. When a number in column A changes, its barcode changes with it.

Step 4: Apply the AlphaEAN font

Select the formula cells in column B and set the font to AlphaEAN-Standard and the size to 20 pt. The text turns into barcodes with the digits printed below the bars.

• Widen column B and increase the row height until every barcode is fully visible.
• Centre the cells horizontally so each barcode keeps clear space on both sides.
• Use AlphaEAN-Low, -Medium or -High if your labels need a shorter or taller barcode.

Step 5: Print and test

• Print at 100% size. In File › Print, set scaling to No Scaling: options such as Fit Sheet on One Page shrink the barcodes, and barcodes that are too small may not scan.
• Keep cell borders and other content away from the bars.
• Laser scanners cannot read barcodes from a screen. A phone app such as Google Lens can check the number on screen; for the final test, scan a printed page with the scanner used in production.

Troubleshooting

I see the number with asterisks instead of a barcode

The cell does not use the AlphaEAN font, or the font is not installed. Install the font, restart Excel and apply the font to the cells again.

The leading zero is missing

The EAN was stored as a number. Format column A as Text and enter the number again.

The barcode does not look right

Check that the cell contains only the digits between the delimiters: 13 digits between asterisks or 8 digits between percent signs, with no spaces.

The scanner reads nothing or the wrong number

Check the check digit (step 2), print at 100% and test with the scanner used in production.