Barcodes in Excel and Google Sheets: Fonts and Formulas

How a barcode font works

A barcode font draws bars instead of letters. The cell still holds ordinary text; only its appearance changes. That has two consequences.

First, the font has to be installed on every computer that opens or prints the file. Microsoft notes that text in a font that isn't installed on a computer displays in a default font instead (Microsoft Support). Your barcodes quietly turn back into plain text on someone else's machine.

Second, the cell text must already be in the exact form the barcode needs, including any start, stop and check characters. A font only draws; it doesn't calculate. In Excel you install the font in your operating system. In Google Sheets, the font menu's "More fonts" option adds fonts from Google's library, which includes the free Libre Barcode fonts (Google Docs Editors Help). [VERIFY]

Code 39: wrap the value in asterisks

Code 39 is the font-friendly barcode. Each character has one fixed pattern, and the asterisk serves as the start and stop character. To display a SKU from A2 as Code 39, put this in another cell and apply a Code 39 font:

="*"&A2&"*"

Plain Code 39 supports digits, uppercase A to Z, space, and the symbols - . $ / + % (Libre Barcode Project). Keep your SKUs uppercase. "Extended" Code 39 fonts can show lowercase, but they do it by encoding character pairs, and a scanner set to plain Code 39 may return "Hello" as "H+E+L+L+O".

Give the barcode room. Set the font large enough that the bars print cleanly, and widen the column so the bars don't touch neighboring cells, since scanners need a blank margin on both sides.

Code 128: why the font alone isn't enough

Typing "BX-104" into a cell formatted with a Code 128 font draws bars, but no scanner will read them. A Code 128 barcode needs a start character that selects a code set, the data, a check character and a stop character. The Libre Barcode documentation says plainly that its Code 128 fonts must be used with an encoder, which chooses code sets and calculates the check character (Libre Barcode Project).

Here is the check character for "BX-104" in Code Set B, worked by hand:

1. Start B has the value 104.
2. Each character's value is its ASCII code minus 32: B = 34, X = 56, - = 13, 1 = 17, 0 = 16, 4 = 20.
3. Multiply each value by its position and add: 34 + 112 + 39 + 68 + 80 + 120 = 453.
4. Add the start value: 453 + 104 = 557.
5. Divide by 103 and keep the remainder: 557 − (5 × 103) = 42. Value 42 is the letter "J".

So the cell must contain the start-B character, then BX-104, then J, then the stop character. Fonts don't all map the start, stop and high-value characters to the same keys, which is why encoders are written for a specific font. Your options are the font maker's encoder (often a macro or add-in), an encoder web page whose output you paste in, or skipping fonts altogether. Code 128 explained covers the code sets in more detail.

A check-digit formula for an 11-digit UPC body

If you have the first 11 digits of a UPC-A and need the 12th, a formula can do it. GS1's method multiplies the digits by 3 and 1 alternately, starting with ×3 on the rightmost digit, adds them up, and subtracts the sum from the next multiple of ten (GS1 General Specifications, section 7.9).

Put the 11-digit body in A2 and this formula in B2. It works the same in Excel and Google Sheets:

=MOD(10-MOD(SUMPRODUCT(MID(TEXT(A2,"00000000000"),{1,2,3,4,5,6,7,8,9,10,11},1)*{3,1,3,1,3,1,3,1,3,1,3}),10),10)

Each piece has a job:

• TEXT(A2,"00000000000") turns the value into exactly 11 characters and restores any leading zeros the spreadsheet dropped. Microsoft recommends this approach for restoring leading zeros (Microsoft Support), and Google's version pads with zeros the same way (Google Docs Editors Help).
• MID(...,{1,...,11},1) splits the text into single digits (Microsoft, Google).
• *{3,1,...} applies the weights. The multiplication also turns each digit from text into a number.
• SUMPRODUCT adds the results (Microsoft, Google).
• The inner MOD(...,10) keeps the last digit of the sum. The outer MOD turns a result of 10 into 0 when the sum is already a multiple of ten (Microsoft, Google).

Keep the *. If you write SUMPRODUCT(MID(...),{3,1,...}) with a comma instead, Excel treats the text digits as zeros, and the formula returns 0 for every row without any error.

Here's the math on 61414112345, the example UPC body used in GS1's specifications:

The products add up to 78. MOD(78,10) is 8, and 10 − 8 is 2, so the full UPC is 614141123452. A body with a leading zero, 01234567890, sums to 85 and gets check digit 5. If the spreadsheet stored it as the number 1234567890, the formula still works, because TEXT puts the zero back.

If you'd rather avoid array constants, put =TEXT(A2,"00000000000") in B2 and this longer formula in C2. It adds the odd and even positions separately:

=MOD(10-MOD(3*(MID(B2,1,1)+MID(B2,3,1)+MID(B2,5,1)+MID(B2,7,1)+MID(B2,9,1)+MID(B2,11,1))+MID(B2,2,1)+MID(B2,4,1)+MID(B2,6,1)+MID(B2,8,1)+MID(B2,10,1),10),10)

To validate a complete 12-digit UPC in A2, extend the pattern to 12 positions with weights ending in 1 and test whether the total is a multiple of ten:

=MOD(SUMPRODUCT(MID(TEXT(A2,"000000000000"),{1,2,3,4,5,6,7,8,9,10,11,12},1)*{3,1,3,1,3,1,3,1,3,1,3,1}),10)=0

For 614141123452, the total is 78 + 2 = 80, so the formula returns TRUE. The same idea covers other GTIN lengths as long as the rightmost digit before the check digit gets ×3. How check digits work explains why the weights catch typing errors.

To keep leading zeros from disappearing in the first place, format the UPC column as text before you type or paste numbers into it.

When to use a generator instead

Fonts and formulas are good for internal lists and quick Code 39 labels. Use a barcode image instead when:

• The label is for retail. UPC-A and EAN-13 symbols need correct proportions, quiet zones and human-readable digits, and a font scaled up or down by a font size setting is easy to get wrong.
• You need Code 128 and don't have an encoder. An image has the start, check and stop characters built in.
• You need GS1-128. It needs a special FNC1 character after the start character, and another after some variable-length fields, so the encoder has to support FNC1.
• Other people will open the file. An image prints the same on every computer, with no font to install.

Our barcode generator makes Code 39, Code 128, GS1-128, UPC-A, EAN-13 and other symbols as SVG or PNG files in your browser. Keep the data in the spreadsheet and let the generator draw the bars.

Next step

Paste the check-digit formula next to your list, then spot-check the first few results in the check digit calculator. If any row disagrees, look for a missing leading zero first.