How to Use Excel as a Barcode Generator Without Add-ins

 

Barcodes are essential for inventory, retail, asset tracking, and many other business processes. Excel can generate barcodes without any additional plugins or add-ins by using special barcode fonts and formulas.

In this article, we will show you how to use Excel as a barcode generator without add-ins.

Method 1: Generate Real Barcodes Using a Barcode Font

This is the most reliable and professional approach. It’s fast, free, and requires no coding or add-ins.

Step 1: Download and Install a Barcode Font

• The most popular free options are ‘Free 3 of 9’ (for Code 39) and various Code 128 fonts.
• Search for “Code 128 barcode font free download” in your web browser.
• Download the zip file.
• To extract the zip file:
  • Right-click >> select Extract All.
  • Click OK.

• Right-click the code128.ttf file >> click Install.

Note: You may need to restart Excel for the font to appear.

Step 2: Set Up Your Data

• Open a new Excel workbook.
• In column A, enter the data that you want to convert to barcodes.
• In column B, we’ll create the barcode representations.

Step 3: Create the Barcode Formula

Code 128 requires specific start and stop characters.

• Select cell B2 and insert the following formula.
• Drag the fill handle down to apply the formula to all rows.

=CHAR(204)&A2&CHAR(205)

• CHAR(204) is the start character.
• A2 is the data from the list.
• CHAR(205) is the stop character.

Step 4: Apply the Barcode Font

• Select the cell range (e.g., B2:B4).
• Apply the Code 128 font.
• Increase the font size (24+ works well).
• Now, the codes in column B will appear as barcodes.

Step 5: Test the Barcode

• Print the Excel sheet or display it on your screen.
• Use a barcode scanner (or a smartphone app) to test the scanning.
• It should read the underlying product code (e.g., Laptop).

Method 2: Barcode-like Visualization Using Formulas & Conditional Formatting

Excel can’t create true barcodes with formulas alone, but you can simulate barcodes visually for demonstration or fun projects.

Binary Barcode Visualization:

This method shows a number as a black-and-white stripe pattern.

• Select cell A2 and type a number (e.g., 45).
• Select cell B2 and insert the following formula.

=DEC2BIN(A2, 8)

• Select cell C2 and insert the following formula.
• Drag the fill handle right to cell J2.

=MID($B2, COLUMN()-2, 1)

Apply Conditional Formatting:

• Select the cells C2:J2 (your 8 binary digits).
• Go to the Home tab >> select Conditional Formatting >> select New Rule.
• Choose Use a formula to determine which cells to format.
• Enter the following formula:

=C2="1"

• Click Format >> choose a black fill.
• Click OK.

• Add another rule for white spaces.
• Enter the following formula.

=C2="0"

• Set a white fill and a white font.
• Click OK.

The range C2:J2 now shows a barcode-like visual (black = 1, white = 0).

• Adjust the cell size for a better barcode look.
• Set columns C:J to be narrow (e.g., width 0.5-1).
• Increase the row height.

You’ll see a barcode-like pattern of black and white cells based on the binary representation.

Method 3: Using the REPT Function for Bar-like Patterns

You can also simulate a barcode by repeating the pipe character.

• Insert the product code list in column A.
• Select cell B2 and insert the following formula.

=REPT("| ", LEN(A2))

• This displays a row of vertical bars, one for each character in the cell.

Tips and Troubleshooting

• Formula not working: Make sure there are no extra spaces or special characters in your source data.
• Barcode not scanning:
  • Confirm that the barcode formula includes the correct start and stop characters.
  • Increase the font size. A larger, clearer barcode is easier to scan.
  • Ensure the scanner is set to read Code 128.
• Character issues: Some barcode fonts might have character limitations. Ensure your data (e.g., in cell A2) contains characters supported by your specific Code 128 font.

Limitations of Formula-Based Barcodes

• Not scannable: Visualizations made with formulas, such as the REPT function or conditional formatting, cannot be read by barcode scanners.
• For illustration only: These methods are for learning, data art, or other non-critical uses.
• Professional use: Always use the barcode font method for real inventory, retail, or business tasks.

Conclusion

You don’t need any paid add-ins to generate barcodes in Excel. By installing a free barcode font and using a simple formula, you can generate scannable barcodes directly in Excel. If you’re looking for visual or educational projects, Excel’s formulas and conditional formatting can help you create ‘barcode-like’ displays. While these creative tricks aren’t suitable for scanning, they’re perfect for demos, teaching, or just for fun.