GTIN Check Digit in Excel and Google Sheets: One Formula for Every Length

A single SUMPRODUCT formula that computes the GS1 check digit for UPC-A, EAN-13, GTIN-14 and EAN-8 — plus a TRUE/FALSE validator you can drag down a whole column. Every formula on this page was verified against known-valid barcodes.

What we are computing

Every GTIN — UPC-A (12 digits), EAN-13, GTIN-14 and EAN-8 — ends with a check digit computed by the GS1 mod-10 algorithm: starting from the rightmost digit of the body, multiply digits alternately by 3 and 1, sum the products, and the check digit is whatever brings the total to the next multiple of 10. The full algorithm, and why it catches typos, is walked through in how check digits work; how each tool applies it is on the methodology page. For a one-off code, the GTIN check digit calculator is faster than any spreadsheet — this guide is for when the codes live in a column and you want the math next to them.

The trap with spreadsheet versions of this algorithm is the alternating weights. Because the 3-1-3-1 pattern is anchored to the right end of the number, a formula that hardcodes “odd positions × 3” from the left works for even-length codes (UPC-A, GTIN-14) and silently breaks on EAN-13. The formulas below derive each weight from the cell’s own length, so one formula covers every GTIN format.

Formula 1: calculate the check digit

Put the code body — the digits without the final check digit (7, 11, 12 or 13 of them) — in cell A2, formatted as Text. Then:

=MOD(10-MOD(SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
  3^MOD(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2)))+1,2)),10),10)

Piece by piece:

ROW(INDIRECT("1:"&LEN(A2))) builds the array of positions 1, 2, … up to the length of the cell. It is the classic trick for making a formula loop without VBA.
MID(A2,…,1) splits the code into individual characters, and the double minus -- converts each one from text to a number.
3^MOD(LEN(A2)-position+1,2) is the weight: it evaluates to 3 when the digit sits an even distance from the right end and 1 otherwise, which is exactly the GS1 pattern — and because it is measured from the right, it self-adjusts to any code length.
SUMPRODUCT multiplies digits by weights and sums them, and the outer MOD(10-MOD(…,10),10) turns that sum into the check digit (the final MOD(…,10) maps the “sum already ends in 0” case to 0 instead of 10).

Worked example: 03600029145

With 03600029145 (an 11-digit UPC-A body) in A2, the formula weights the digits 3,1,3,1… from the right: 0×3 + 3×1 + 6×3 + 0×1 + 0×3 + 0×1 + 2×3 + 9×1 + 1×3 + 4×1 + 5×3 = 58. The next multiple of 10 is 60, so the formula returns 2, and the complete barcode is 036000291452. To append it in one cell: =A2&B2 if the formula lives in B2.

Formula 2: validate a complete code (TRUE/FALSE)

To check codes that already include their final digit, use the fact that a valid GTIN’s full weighted sum — check digit included — is always a multiple of 10. With the complete code in A2:

=MOD(SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
  3^MOD(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),2)),10)=0

The only change from Formula 1 is the weight exponent (no +1, because the rightmost digit is now the check digit itself, which always carries weight 1). Tested against known codes: 036000291452 returns TRUE, 036000291453 returns FALSE, and the EAN-13 4006381333931 returns TRUE — the same formula, no edits, across both lengths.

The formula happily computes on any string of digits, so a 9-digit typo could still come back TRUE by coincidence. To also enforce a legal GTIN length, wrap it:

=AND(ISNUMBER(MATCH(LEN(A2),{8,12,13,14},0)),
  MOD(SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),
  3^MOD(LEN(A2)-ROW(INDIRECT("1:"&LEN(A2))),2)),10)=0)

Filling a whole column

Both formulas use plain relative references, so select the formula cell and double-click the fill handle (the small square at the cell’s corner) to copy it down as far as your data goes. Two practical notes:

Empty rows error out. INDIRECT("1:"&0) is not a valid row range, so a blank A-cell produces #REF!. Wrap the formula in =IF(A2="","",…) to keep the column clean.
Spaces and hyphens produce #VALUE!. The -- coercion chokes on anything that is not a digit. If your export has separators, feed the formula SUBSTITUTE(SUBSTITUTE(A2," ",""),"-","") instead of A2.

The edge case that ruins everything: leading zeros

If you type or paste 036000291452 into a General-formatted cell, Excel stores the number 36000291452 — the leading zero is gone before any formula runs. The check-digit formula then sees 11 digits instead of 12, computes weights against the wrong positions, and returns an answer that looks plausible and is wrong. Long codes may also display as scientific notation (3.6E+10), though the stored value is still intact in that case.

The fix is to format the column as Text (Ctrl+1 → Text) before entering data, or to mark the column as Text in the import wizard when opening a CSV. Reformatting after the fact does not bring lost zeros back. This problem is common enough with barcodes that it has its own guide: how to stop Excel eating leading zeros. If a batch of codes has already been mangled, the fix pages and the bulk barcode validator can help you triage which rows are still valid.

Google Sheets: same formulas, two differences

Both formulas above work in Google Sheets unchanged — SUMPRODUCT, MID, ROW and INDIRECT behave identically. The differences worth knowing:

Text format is under Format → Number → Plain text (not “Text”), and on import Sheets is just as happy to strip leading zeros as Excel is.
SEQUENCE is available everywhere in Sheets (and in Excel 365/2021), so you can write the position array more readably: SEQUENCE(LEN(A2)) replaces both ROW(INDIRECT(…)) calls.

One general caveat for both programs: if your locale uses commas as decimal separators (much of Europe), argument separators in every formula become semicolons — MID(A2;1;1) — but the logic is byte-for-byte the same.

TRUE means consistent, not registered

A passing checksum tells you the number was not mistyped — nothing more. Any 11 digits you invent can be given a valid twelfth; that does not make it a UPC that GS1 ever issued to a real product. If you are vetting supplier data or marketplace listings, treat the spreadsheet check as the cheap first gate and confirm ownership against GS1’s registry separately (see data sources for what each check does and does not consult).

Validating at scale?

Spreadsheet formulas are fine for a few thousand rows. If validation needs to happen inside a pipeline — on every product feed, before every import — the CodeClassify API does the same GS1 math server-side. Post a body and get its check digit back:

curl -X POST "https://codeclassify-api.rosariovitale0096.workers.dev/v1/gtin/check-digit" \
  -H "Content-Type: application/json" \
  -H "X-Api-Key: ccl_your_key" \
  -d '{"data":"03600029145"}'

{"ok":true,"data":"03600029145","check_digit":2,"full_code":"036000291452","format":"UPC-A"}

The free tier includes 10 calls per month with no card required — enough to wire up and test — and there is a batch /v1/gtin/validate endpoint that checks up to 100 codes per call for whole files (see the CSV bulk-validation guide).

FAQ

Does the same Excel formula work for UPC-A, EAN-13 and GTIN-14?

Yes, as long as the formula counts digit positions from the right, not the left. The GS1 algorithm always gives the rightmost body digit a weight of 3, so a formula that derives each weight from LEN(A2) minus the position adapts automatically to 8, 12, 13 or 14 digits. Formulas that hardcode “odd positions × 3” from the left work for 12- and 14-digit codes but silently fail on EAN-13, because 13 is odd and the parity flips.

Why does the formula return #VALUE! or the wrong check digit?

The two usual causes are formatting and stray characters. If the cell is stored as a number, Excel has already dropped any leading zero, so the formula computes on an 11-digit string instead of the 12-digit UPC you pasted — format the column as Text before entering codes. If the cell contains spaces or hyphens, the double-unary coercion fails with #VALUE!; strip them with SUBSTITUTE first.

If the validation formula returns TRUE, is the barcode real?

No. TRUE only means the number is internally consistent under the GS1 mod-10 algorithm — it was not obviously mistyped. It does not prove the GTIN was ever assigned by GS1 to a real product. Checksum validation in a spreadsheet is a first filter; confirming that a code is actually registered requires a GS1 registry lookup, which is a separate step.

Skip the formula for one-off codes

Paste any UPC, EAN or GTIN-14 body into the GTIN check digit calculator and get the verified final digit instantly — or run a whole list through the bulk barcode validator.

This guide is for general information only. Check digit validation confirms internal consistency of a number, not that a code is officially registered, assigned, or in use. For barcode assignment and registry confirmation consult GS1.