OmniDocHow to generate barcodes in Excel: fonts and function methods
Excel does not have a built-in barcode function. This article talks about the general method: install the barcode font, use formulas to spell out text that conforms to the encoding rules, then set the font size and print size, and check the scanning effect.
Key points of this article
- Excel does not have a built-in function to directly generate barcodes. A common approach is to install barcode fonts and then use formulas to convert the data into the encoded text required by the font.
- Determine the barcode type first. Code 39 and Code 128 are suitable for internal numbers. Product codes need to calculate check digits according to rules.
- The character set is limited. Code 39 does not support lowercase letters. The data must be converted into format first and then encoded.
- After generation, use a scanning device to actually test it, and check the font size, white space, and print clarity.
When making barcodes in an Excel spreadsheet, a common scenario is to number materials, assets or samples, print them and stick them on labels for scanning with barcode scanners. Many people search for "how to generate barcodes in excel", and the results are often looking for plug-ins or online conversion websites. These methods can be used, but they require an extra step of file upload and format conversion.
OmniDoc does not provide the function of generating barcodes. The following is a common method in Excel: using barcode fonts and cell formulas. After reading, readers can batch generate Code 39 or Code 128 barcodes in one table, and can also determine when to switch to special tools.
The entire process is divided into three parts: first confirm the barcode type and purpose, then install the font and use formulas to generate encoded text, and finally set the size and test scanning. The following is expanded in this order.
Excel generates barcodes, how to do it? One sentence conclusion
Use barcode fonts to add auxiliary column formulas to save trouble: the font is responsible for drawing the text into stripes, and the formula is responsible for adding the start character, end character or check digit according to the encoding rules of the font. Use this method when the quantity is small and only internal numbering is required; when strict compliance with product code specifications is required, confirm the rules first and then decide whether to use special tools.
| your situation | Recommended practices | reason |
|---|---|---|
| Internal number, numbers plus capital letters, small quantity | Code 39 font + asterisk formula | The coding rules are simple, the formula only needs to be wrapped with asterisks |
| Requires higher density, or contains lowercase letters, symbols | Code 128 font + encoding formula in font manual | The character set is larger, but the start character and check character need to be spliced according to rules |
| To be printed on product packaging, a standard product code is required | First calculate the check digit according to the rules, and then select the corresponding symbol system | Incorrect number of bits and check digits can cause scan failure or misreading |
| Only generate one or two, I don’t want to install fonts | Use online barcode generation website or browser tools, export pictures and insert them into Excel | There is no need to change the system font, but you must pay attention to data upload and privacy. |
| Finally, the PDF version of the label must be submitted. | Layout in Excel and export to PDF | Barcodes are stable in PDF and easy to print |
Before you start: Confirm these points first
The barcode type must be determined first. Code 39 and Code 128 are common internal barcodes. Product codes (such as 13-digit EAN codes) have fixed number of digits and verification rules. If the type is wrong, the scanning device may not be able to read it, or it may read the wrong number.
Confirm the types supported by the scanning device. QR code scanners or mobile phone QR code scanning software usually support multiple types, but some types may be turned off by default in the settings. Test with a known readable barcode before deciding which one to use.
The data must be cleaned first. Extra spaces, full-width characters, and mixed lowercase letters in the barcode content will cause encoding errors. Code 39 does not support lowercase letters. You can use the UPPER function in the formula to uniformly convert them to uppercase letters.
The font will only display properly on the computer it is installed on. If you send the form to someone else, and the other person's computer does not have the same barcode font installed, the stripes will become ordinary characters. You can export the barcode area as PDF or picture before sending, or ask the other party to install fonts together.
Leave enough space for printing. A blank area (commonly known as a quiet zone) is required on both sides of the barcode. If the font size is too small or the column width is too narrow, scanning may fail. Test with real paper and printer before printing.
Steps to generate barcode using Code 39 font in Excel
Take Code 39 as an example below. Code 128 has the same idea, but the formula is different.
- Prepare data columns. Enter the number in column A, for example, fill in
AB2024-001in A2, with one barcode content per line. - Install barcode fonts. Install the Code 39 font file in the system, restart Excel after installation, and make sure it can be found in the font list.
- Write the formula in column B. Enter
=""&UPPER(A2)&""in B2, convert the content to uppercase and add asterisks before and after. The asterisk is the starting and ending character of Code 39, which is required by the font to draw the complete barcode. - Fill the formula downward. Drag the fill handle of B2 to the required number of lines, and all barcode contents will be generated uniformly.
- Set font. Select column B and change the font to the newly installed barcode font. The font size can be set to about 36 first. The actual size is subject to the scanning effect.
- Adjust alignment and column width. Center the cell horizontally and the column width to ensure that the barcode is fully displayed and not truncated or displayed as a pound sign.
- Check the barcode content. Use a formula or manual check in column C to confirm that the stripes displayed in column B correspond to the numbers in column A, without spaces or extra symbols.
- Export and test. Print or export to PDF, scan them one by one with a QR code scanning device, and confirm that the read content is consistent with column A.
The method of Code 128 is: first check the encoding rules in the font manual, use formulas to splice the starting characters, data characters and check characters in the auxiliary column, and then apply the barcode font to this column. If one of the rules is wrong, the scan results will be inconsistent, so it is recommended to verify with a few samples first.
Check each item after completion
After the generation is completed, do not print in batches directly, first check according to the form below.
| Check item | What do you think? | What if there's a problem? |
|---|---|---|
| Scan result | Scan one by one with the code scanning equipment, and compare the readout with the original data of column A. | Check formulas, case and extra spaces in case of inconsistency |
| Fringe integrity | Zoom in and look at both ends of the bar code to see if there is a gap or truncation | Increase the column width or font size and check that the font is fully installed |
| Blank area | Is there enough space on the left and right sides of the bar code? | Adjust cell alignment or leave blank columns on both sides of the bar code |
| Print clarity | Print with the actual printer and paper to see if the lines are virtual | Improve print quality and avoid scaling leading to thinning of stripes |
| Font compatibility | Open the file on another computer to see if the bar code displays properly | Export to PDF or picture and send again |
If the scan fails, priority should be given to three reasons: incorrect data format, inappropriate font or font size, and insufficient printing space. Eliminating them one by one is faster than repeating them over and over again.
# Core comparison
| Practice | Suitable for the scene | Advantages | Matters needing attention |
|---|---|---|---|
| Bar code font + formula | Batch generation of internal numbers | The data comes directly from the cell and can be updated automatically after modification. | Fonts need to be installed and the coding rules should be correct |
| Generate website in line code + insert picture | Generate a small amount, do not want to install fonts | Easy to operate, no need to remember the rules | The efficiency of generating one by one is low, so we should pay attention to data privacy. |
| Add-ins for desktop office software | Often generated, requiring multiple types | There are many bar code types supported. | Additional installation is required and compatibility depends on the specific version |
# frequently asked questions (FAQ)
1. Can the bar code generated by Excel be read directly with the code sweep gun?
Yes, provided that the bar code type is consistent with the setting of the scanning equipment, and the font size and print clarity are sufficient. Before formal use, it is recommended to actually scan it with several samples.
2. Do you want to calculate the check bit by yourself?
Yes. The last bit of the 13-bit commodity code is the check bit, which can be calculated in the formula. Assuming that there are 12 digits in A2, you can write =MOD(10-MOD(SUMPRODUCT(--MID(A2,{1,3,5,7,9,11},1))+3*SUMPRODUCT(--MID(A2,{2,4,6,8,10,12},1)),10),10) in B2, and the result is the parity bit. The formal use of commodity codes should also check the coding specifications of the industry.
3. What if there are lowercase letters in the bar code?
Code 39 only supports uppercase letters, numbers and some symbols, and the formula can be converted uniformly by adding the UPPER function. If you must keep lowercase letters, use Code 128, which supports a larger character set.
4. The generated bar code is displayed normally in Excel, but it can't be scanned when it is printed. Why?
Common reasons are too small font size, thinning lines caused by scaling, insufficient white space on both sides, or low printer resolution. It is recommended to turn off zooming, print with the original size, and leave a blank space on both sides of the bar code.
5. Is there a way to do it without fonts?
Yes. You can use the line code generation website or browser tool to generate pictures one by one, and then insert Excel. This method is suitable for a small number of requirements. It is not recommended to upload bulk data to unknown websites, and privacy should be paid attention to when internal numbering is involved.
# Summary
Excel itself does not generate the bar code directly, but through the bar code font plus formula, the batch production of Code 39 or Code 128can be completed. The key is to first determine the type of bar code, then use the formula to spell out the correct encoded text, and finally use the scanning equipment and the actual printing results to check.
