Barcoding in Microsoft Excel

Creating barcodes in Microsoft Excel is an easy task with IDAutomation products. IDAutomation offers the Native Barcode Generator for Excel as the recommended product because it does not require the installation of additional components. 

Barcoding on Mac

Native Barcode Generator

The Native Barcode Generator for Microsoft Excel provides barcoding capability to Microsoft Excel Spreadsheets with an embedded VBA macro making it easy to share sheets without needing to distribute additional fonts or other components.

BARCODE SYMBOLOGIES

Code 39, Code 128, PDF417, Data Matrix and QR Code, MSI, GS1-128, Interleaved 2 of 5, Codabar, Postnet and Intelligent Mail IMb.

CROSS PLATFORM COMPATIBLE

Compatible for both Microsoft Windows and Mac OSX, 32 and 64 bit systems, for Microsoft Excel versions with VBA Macro support.

USPS BARCODE SUPPORT

Can generate Intelligent Mail (IMb), USPS-128, Postnet and Planet barcodes. 

EASY TO DISTRIBUTE

This is a complete barcode generator that stays with the spreadsheet, even when distributed.

Using Barcode Fonts in Excel

Self-checking barcode fonts such as Code 39 Fonts may be easily used in Excel. This implementation is cross-platform compatible with both Mac and Windows. To use fonts other than self-checking versions, refer to the VBA Macros.

All self-checking barcode fonts require a start and stop character. IDAutomation’s Code-39 fonts can be easily generated in Excel by (1) using a formula to append the asterisks to the beginning and ending of the field and (2) selecting the font for the field. The example below creates the text for a Code 39 barcode in cell B2 from the data in cell A2.

Append the required asterisks for Code 3 of 9 to data from cell A1...


Combining Multiple Fields into a Single Barcode

Tabs and returns can be added with Code 39 fonts (in extended-39 mode) between fields by inserting “$I” for a tab and “$M” for a return. For example, this formula encodes a tab between cells C3 and D3: =(“!”&C3&”$I”&D3&”!”)

Using Code 39 in the Excel example below enter the formula in cell E3.

In this Microsoft Excel example, we selected the Code39 font and entered the formula in cell E3


Creating Barcodes in an Entire Column in a Spreadsheet

If a large column of data needs to be bar-coded, where copying and pasting the barcode font formula into each cell would be cumbersome, then creating barcodes in an entire column would be essential. In this example, Code 3 of 9 barcodes are created in the Excel using the IDAutomation Code-39 Font. 

1. The barcodes in column B will be created from the data in column A. This assumes the IDAutomation Code-39 Font is installed. Enter the formula in one cell. In this example. the formula  =”*”&A2&”*” is entered in cell B2 to append the required asterisks to data from cell A1:

Append the required asterisks for Code 3 of 9 to data from cell A1...

2. Select that cell and choose Edit – Copy. After that, highlight the entire column by selecting the gray square labeled “B” at the top of the spreadsheet and choose Edit – Paste. The formulas may take a little time to re-calculate.

Pasting the code 39 formula into multiple cells in Microsoft Excel...

3. Change the data in the column to be the Code 39 barcode font. With the entire column that contains the formula highlighted, choose the IDAutomationHC39XS font. The barcodes will appear in the entire column.

With the entire column that contains the formula highlighted, choose the IDA Code39 HR Short font from the Code 39 Font Package or another appropriate font.

4. With the entire column that contains the formula still highlighted, choose the appropriate point size. 12 points is generally compatible with all barcode scanners. With the entire column that contains the formula still highlighted, choose the center text option so the barcode is in the center of the field. Then, make the column wide enough so there is some white space between the beginning and ending of the barcode.

Excel Barcode Example

Link cell to ActiveX Control.

Using the ActiveX Control

After purchasing or downloading the ActiveX Control, it may be dragged, dropped and resized in a spreadsheet.

1. Within Excel, choose View – Toolbars – Control Toolbox, a toolbox will appear.

2. In the toolbox dialog, choose the More Controls button.

3. Then, select the barcode control from the list of available ActiveX Controls. Select the control installed starting with “IDAutomation”, and then drop it on a spreadsheet. After selecting it, the control will appear in the spreadsheet similar to a graphic image, resize the plug-in as necessary. To change the properties of the control such as barcode height and symbology type, right click on the control and choose properties. To link a cell to the data to be encoded in the barcode, enter the cell in the “LinkedCell” property of the control.

4. When finished, exit the design mode by choosing the design mode button.

5. To edit the properties of the control the design mode must be enabled. If there are problems editing the properties of the control, press the Design Mode button.