Barcode Generation Tutorial for Excel

IDAutomation provides a variety of products to implement barcode generation capability into Microsoft Excel for various documents, label printing, inventory control and more. Following are some of the most common implementations of our products.

Using Self-Checking Barcode Fonts in Excel
Combine Multiple Columns Into a Barcode
Create a Column of Barcodes in a Spreadsheet
Using VBA Macro Functions in Excel
Dynamic Barcode Cells With The ActiveX Barcode Control
Linking To Excel or CSV With The Barcode Label Software
Video: How To Create a Barcode From Fonts in Excel
Video: Linking To an External Source With Barcode Label Software

Self-Checking Barcode Fonts in Excel

Self-checking barcode fonts such as Codabar (numbers) and Code 39 (numbers and uppercase letters) are the easiest to use in Excel when encoding numbers and/or uppercase letters. Self-checking fonts have checking code built-in so that manual calculation of check characters is not required. If these self-checking barcode fonts cannot be used, refer to the Excel Barcode Integration Guide for proper product suggestions.

IDAutomation's self-checking barcode fonts may be generated as a calculated field in Excel. The following tutorial demonstrates how this can be accomplished.

To create a proper barcode with a self-checking barcode font, start and stop characters must be added to the beginning and ending of the data being encoded. These start and stop characters are easily added to IDAutomation's Codabar and Code 39 fonts with the asterisk * character.
The start/stop character may be easily added to the beginning and ending of the data being encoded with a simple Excel formula of ="*" & CELL & "*" where CELL is the cell of the data being encoded in the barcode.
After the formula is entered in the cell, select the correct font and a barcode should appear. IDAutomation fonts with XS heights are specifically designed for Excel to automatically adjusts to the correct column height. The width of the column may need to be sized to contain the entire symbol.
Print and scan the barcode to verify the correct data is encoded. If a scanner is needed to verify barcodes, IDAutomation recommends the IDAutomation USB Barcode Scanner.

Combining Multiple Excel Columns Into a Barcode

Tabs and returns may be added with Code 39 fonts (in extended39 mode) between cells by inserting "$I" (for a tab) or "$M" (for a return.) For example, the following formula encodes a tab between cells A1 and B1: =("*"&A1&"$I"&B1&"*")

Learn more about combining multiple cells into a single barcode.

Creating a Column of Barcodes in a Spreadsheet

IDAutomation's barcode fonts provide an easy method of barcoding an entire Excel column. Code 39 barcodes are created in an Excel spreadsheet in this example, with the IDAutomationC39 font that is included in the Code 39 Barcode Font Package. Codabar fonts may also be used to create smaller numeric-only barcodes. For fonts other than Codabar and Code 39, refer to IDAutomation's VBA Macro Tutorial for Microsoft Office or the Barcode Integration Guide for Excel.

In this example, barcodes are created in Column B from data in Column. Enter the formula ="*"&A2&"*" in cell B2 to append the required asterisks to the text from cell A2:
Select the cell containing the formula and choose Edit - Copy. Highlight the entire column by selecting the rectangle labeled "B" at the top of the spreadsheet and choose Edit - Paste. The formulas may take some time to re-calculate if the spreadsheet is large.
With the entire column that contains the formula selected, choose the IDAutomationHC39S or IDAutomationHC39XS font included in the Code 39 Font Advantage Package, or an appropriate font of your choice. IDAutomation fonts with XS heights are designed to automatically adjust to the smallest format and automatically adjust to the correct column height. The barcodes will then appear in the entire column.
With the entire column that contains the formula still selected, choose the appropriate point size. In this example, 12 points is used. With the entire column that contains the formula highlighted, choose the center text option so the barcode is in the center of the field. Size the column so it is wide enough to contain the generated symbol.
Print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, consider the IDAutomation USB Barcode Scanner which dependably scans Code 39 barcodes printed as small as 6 points.

ActiveX Barcode Controls For Dynamic Barcode Cells

IDAutomation's ActiveX Barcode Controls may be used in Excel on Windows systems. The purchased or Demo version may be dragged, dropped and sized on a spreadsheet. This control can only be linked to one cell. If an entire column of cells should be linked, consider using barcode fonts as an alternative.

In Microsoft Excel, select View - Toolbars - Control Toolbox.
On the tool box dialog, select the More Controls icon:
Select the barcode control from the list of available ActiveX Controls. Select the control that was installed starting with "IDAutomation," and drop it onto the spreadsheet.
Draw the image for the control to appear in the spreadsheet like a graphic image.
Size the ActiveX Control as necessary. To modify properties like barcode height and symbology type, right-click on the control and select properties.
To link a cell to the data that will be encoded in the barcode, enter the cell location in the "LinkedCell" property of the control. To link an entire column of cells into one barcode, consider using barcode fonts as an alternative.
You are finished! Exit design mode by selecting the design mode icon:
Print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, consider the IDAutomation USB Barcode Scanner.

Note: Design mode must be used to edit the properties of the control. If problems are encountered when editing the control properties, select the Design Mode icon to enable.

Linking To Excel or CSV With Barcode Label Software

IDAutomation Barcode Label Software is an easy-to-use WYSIWYG barcode label printing application that prints barcodes, text and graphics on standard laser and inkjet printers, as well as thermal transfer and direct thermal printers. This software easily links to columns in Microsoft Excel or a CSV text file as a data source for a text or barcode object, which allows printing of labels from data in Excel.

Download and install the IDAutomation Barcode Label Software.
Select Tools - Data Sets - Link External Data.
Select the data type and choose Search to find the file.
After the source path is found, select the appropriate table if applicable.
Select Link Data and a portion of the linked data will be visible on the screen.
When the data is linked, select Close.
To link a column of data to a barcode or text object, click the down arrow next to Value Type and select External DataSource, then click the source column to link the value. The example window will show what is linked.
Increment the label number in the design screen to verify the data is linked.
After verifying alignment, select All Rows Data Source as the option to print.
When linking to the CSV or text files, the format should be as follows:

CSV: "Column1","Column2","Column3"
TEXT: Column1,Column2,Column3
Print and scan the barcode(s) to verify the correct data is encoded. If a scanner is needed to verify barcodes, consider one of our USB Barcode Scanners.