Spreadsheets and files
Barcode generator for Excel
One formula shows the barcode, or the error sentence when the code is not valid.
Three steps, once
-
Create a picture key
Sign in and open the Account page. The key is shown once, so copy it.
-
Paste it in Z1
Z is out of the way of your data. The formula reads the key from there, so you paste it once per workbook.
-
Put the formula in B2, fill down
Excel draws one barcode per row and redraws it when the code in column A changes.
You create a barcode in Excel with one IMAGE formula. Put the code in column A, your picture key in Z1, and the formula next to the code. Excel asks Beepable for the picture and shows it in the cell. A valid GTIN becomes a retail barcode, a web address becomes a QR code, and a code that is not valid shows a short sentence that explains what is wrong. There is no add-in and nothing to install.
Put a picture key in Z1. Create it on the Account page. Never put an All routes key in a sheet: anyone who can see the formula can copy it.
How do I create a barcode in Excel?
You need a free account and a picture key. The key is shown once, so copy it as soon as you create it.
- Sign in, open the Account page, and create a picture key. Copy it.
- Paste the key into Z1. Z is out of the way of your data.
- Format column A as text, then type or paste the first GTIN or URL in A2. Text keeps a leading zero.
- Put this formula in B2:
=IMAGE("https://beepable.app/i/auto.png?v="&ENCODEURL(A2)&"&k="&$Z$1) - Fill B2 down the column. The dollar signs in $Z$1 keep every row on the same key.
- Make the rows taller until the bars are clear, then scan one picture with your phone or scanner before you print.
The word auto in the formula lets Beepable pick the barcode type from the value. You can replace it with a fixed type, such as ean13 or code128, when the whole column must be one type.
What does the cell show for a real value?
| Cell A2 | What appears in B2 |
|---|---|
| 4006381333931 | An EAN-13. Valid codes of 8, 12, and 14 digits become EAN-8, UPC-A, and ITF-14. |
| 4006381333932 | A card with the sentence: Check digit is invalid for this GTIN. Name code128 to encode it anyway. |
| https://example.com/item | A QR code. A GS1 Digital Link is a QR code too. Build one from a GTIN on the Digital Link page. |
| SKU 44 | A card with the sentence: This is neither a valid GTIN, an Amazon FNSKU, nor a URL. Name a symbology, such as code128. Put code128 in the formula to draw it. |
To print the digits under an EAN-13, EAN-8, or UPC-A, add hrt=1 to the address:
=IMAGE("https://beepable.app/i/auto.png?v="&ENCODEURL(A2)&"&hrt=1&k="&$Z$1)What if a cell shows a card instead of a barcode?
A card is white, with a green bar and the words Not a barcode. It tells you what to fix. Do not print it.
| The cell shows | Why | What to do |
|---|---|---|
| Check digit is invalid for this GTIN. Name code128 to encode it anyway. | The last digit does not match the other digits. | Copy the code again from its source. Beepable never makes up a check digit. |
| A retail barcode needs 8, 12, 13, or 14 digits. This has 11. Name code128 to encode it anyway. | The cell has too few or too many digits, often because a leading zero was dropped. | Format the column as text and type the code again. |
| This is neither a valid GTIN, an Amazon FNSKU, nor a URL. Name a symbology, such as code128. | The value is text, such as a SKU, and the formula says auto. | Put code128 in the formula instead of auto. |
| This barcode needs an API key. | Z1 is empty, or the formula does not point at it. | Paste your picture key in Z1, and keep both dollar signs in $Z$1. |
| This API key has been revoked. | The key was revoked on the Account page. | Create a new picture key and paste it in Z1. |
| A broken picture, with no card | Too many new barcodes were requested at the same moment. | Wait a minute, then recalculate the sheet. |
Fix the cell, then recalculate. Cards are never saved, so the barcode appears as soon as the value or the key is right.
What if my Excel has no IMAGE?
Older desktop Excel cannot show a picture from a formula. WEBSERVICE can still fetch the image address, or the error sentence, as text. That text formula needs a second key: an All routes key in Z3. The address it returns has no key, so the formula adds the picture key from Z1 before IMAGE draws it. The All routes key never appears in the picture address.
=LET(r,WEBSERVICE("https://beepable.app/excel?v="&ENCODEURL(A2)&"&s=auto&k="&$Z$3),IF(LEFT(r,4)="http",IMAGE(r&"&k="&$Z$1),r))Use this only when you need the sentence as text you can filter. For a catalog, the short IMAGE formula above is simpler and needs only the picture key.
What should stay out of the sheet?
The value and the key are both part of the picture address. Anyone who can see the formula can copy them. Do not encode a private supplier link, a link with a password or token, or an unreleased price page. If a workbook was shared too widely, revoke its picture key on the Account page and paste a new one in Z1. The old barcodes stop drawing on the next recalculation.
The barcode form on the homepage shows the same formula for the type you pick. If you saved your picture key on this device from the Account page, the copied address already contains it.
Frequently asked questions
Does Excel have a built-in barcode generator?
No. Excel cannot draw a scannable barcode by itself. The IMAGE formula on this page fetches the picture from Beepable. There is no add-in to install.
Which Excel versions can show the picture?
Excel for Microsoft 365 and Excel for the web have IMAGE. Older desktop versions do not. They can still get the address as text with WEBSERVICE, as shown on this page.
How do I make barcodes for a whole column?
Fill the formula down. Each row draws its own barcode, and $Z$1 keeps every row on the same key. A barcode that was drawn once is saved for a year, so reopening the workbook costs nothing.
Can I print the digits under the bars?
Yes, for EAN-8, EAN-13, and UPC-A. Add &hrt=1 before &k= in the formula. ITF-14, Code 128, and QR codes have no printed line.
What does a wrong GTIN show?
A white card with the sentence: Check digit is invalid for this GTIN. Name code128 to encode it anyway. The card says Not a barcode. Do not print it.
Why does the key go in the formula?
Excel fetches the picture itself and cannot sign in or send a header. The key travels in the address, which is why it must be a picture key and never an All routes key.
Keep going
