Legacy Technology Services Blog

Press Releases, Company News, Information and Tips

How to Create a Scannable Barcode in Excel

These instructions will teach you how to create your own scannable barcodes in Microsoft Excel or any other program where you can use TrueType fonts for the text and it will work for Macintosh (Apple), Windows and Linux systems, without the need for special software.  You can use this tutorial for creating barcodes for the entry of serial numbers, part numbers, bin locations, order numbers, document tracking, order tracking, etc., which helps in eliminating keyboard entry errors and speeds up data entry.

For this tutorial we will use Code 39 symbology (also known as 3 of 9, USD-3 or Type 39) which is a variable length barcode.  The main reason for using this bar code is because this symbology does not require the calculation or generation of a “check digit”.  Instead, all that is required is to add a start and end digit to the code.  The Code 39 bar code is a symbology which is readable by virtually all barcode scanners and barcode apps available in the market today.  Some scanners may require additional configuration settings (see manufacturer documentation) in order to read the barcode, but most will have it available by default.

There are some limitations to the use of this symbology.  Code 39 is limited to 43 characters in length.   it only allows uppercase letters A through Z, digits 0 through 9, and the following special characters: - . $ / + and space (dash, period, dollar sign, forward slash, plus sign, space).  The asterisk (*) is used as the stop and start character for the barcode.

General Overview of using 3 of 9 barcode TrueType font:

Download and install the 3 of 9 Barcode TrueType Font from this page.  The download page contains instructions for how to install the font on Windows, Macintosh and Linux systems.  This font allows you to change plain text into a barcode by just changing the text font type.  A nice feature of this font, as opposed to other ones available online, is that it automatically converts lower-case text to upper-case text in the barcode (other ones available out there will skip lower case characters and thus not create the proper barcode desired).

There are also 3 of 9 Extended barcode fonts available online, which do accept lower-case text as valid.  However, these fonts will encode a lower-case letter as an upper-case letter with a plus (+) preceding it, for example, a lower-case “e” will be encoded in the barcode as “+E”.  Most scanners and scanner apps will not have Code 39 Full ASCII enabled by default, so a scanner will recognize the barcode as standard Code 39.  In these instances, the scanner will output the results incorrectly, substituting “+E” in place of “e” which will make for issues between data sets.  However, a properly configured scanner will convert and output the lower-case text properly, but this requires additional steps and may create additional issues in reading and/or data output.  In order to avoid these issues and others, it is recommended you just use the font available above. 

Place an asterisk (*) at the beginning and end of the text you wish to convert to a Code 39 barcode.  For example, the text string “SN12345” would be “*SN12345*” (leave out the quotes.)

Highlight the text and change the text to the 3 of 9 barcode font.  For best scanning results, it is recommended that you use barcode font size of 14 points or larger.

The barcode is now generated from the selected text and can be scanned by a laser scanner by printing it out, or in the case of some imagers or camera-based scanners (such as an Android or iPhone with a barcode scanner app) the barcode can read directly from the computer screen.

Create Barcodes in Excel - Step-By-Step Example

For this example, we will use a serial number in column “A” and generate barcode text in column “B”.

First, set up column “A” to be left-justified and column “B” to be center-justified to give enough white-space on both ends of the barcode.

Second, use the following formula in column “B”:  =“*”&A1&”*” this puts an asterisk and then copies the value from column “A” and then adds another asterisk to the end of the text (the start and stop character required for Code 39).  It is easier to use a formula then to manually edited each cell to put an asterisk at the beginning and end.  This also allows you to look at the results to see if it looks correct before you change it to a barcode.  You should see something like in the picture below.

Third, select the text contents of column “B” and change the font to the 3 of 9 barcode font and set the point size to 16, or whatever you need.  Please note that a smaller than 14 point font size may create difficulties for the scanner, so keep the text larger.  You should now see something like in the following picture:

Lastly, you can print out the resulting serial numbers and their corresponding barcodes, using a minimum of 300 DPI for the printer settings (anything less and the lines in the barcode may not be “crisp” enough for some scanners to read), and test it with the scanner you plan to use.  A couple things to keep in mind: firstly, its best to print it without grid-lines and secondly, there should be enough space between columns and rows to make it easier to scan the correct barcode (putting rows too close together may result in user-errors in scanning the incorrect code).

Common Problems and Issues

Barcode is unable to be scanned

Possible Reasons:

  • Barcode is missing asterisk at beginning and/or end
  • Font size is too small
  • Printer is not at least 300 DPI
  • Not enough white-space in front and behind the barcode
  • Too long of text (depending on the make and model of the scanner, it may not be able to read at the range required to read the entire barcode)
  • Padding between letters is not fixed (some word processors and spreadsheet programs will vary the spacing between letters, which in the case of a barcode, will make it unreadable to the scanner, as this barcode symbology uses white and dark mark spacing to determine each character)
  • Scanner does not have Code 39 enabled or is unable to read Code 39 barcodes.

More details on Code 39 on Wikipedia, for the curious ones.

Pingbacks and trackbacks (1)+