Clicky

How to Create Barcodes in Excel

How to Create Barcodes in Excel

What is a Barcode

Barcodes are sets of lines of different widths and sizes representing data that when read help identify encoded pieces of information.

It generally consists of a series of parallel black and white bars that can be read by a barcode scanner.

They are printed on products to quickly identify them and are used for entering data into a computer system.

Barcoding has been in existence since the 1970s and is now found everywhere as part of routine commercial transaction processes.

The barcode system can be applied in many industries such as the ones typically used in retail stores as a part of the purchasing process, in warehouses to track and manage inventory, on invoices to help with accounting, in the medical industries to help identify patients’ sample and information on the laboratory information systems.

The codes are used by grocery stores to obtain price and other data about goods at the point of purchase by the consumer.

The bars on barcodes are typically black on a white background, and their width and quantity vary according to application.

The bars on a barcode usually represent the binary digits 0 and 1, sequences of which in turn can represent numbers from 0 to 9 and be processed by a digital computer

Barcode information is read by an optical scanner with laser technology that is part of a computer system.

With a specially designed barcode scanner or a smartphone, the user can access all the information the manufacturer encoded in the barcode’s parallel lines.

When a handheld scanner or barcode pen is moved across the code, or the code itself is moved by hand across a scanner built into a checkout counter or other surface, the barcodes are scanned and the computer stores or immediately processes the data in the barcode.

One of the most important advantages of barcode systems is that they allow users to process detailed information encoded in the barcodes at the moment the barcode is scanned, rather than simply storing information unprocessed.

What are Barcodes used for?

The use of Barcodes originated in supermarket stores and has since increased its use in healthcare such as in doctor’s offices, medical diagnostic firms.

It can also be used in law firms, post offices, retail stores, security applications, car rental, and many others.

Barcoding technologies have been used in manufacturing companies for shipping and receiving operations.

Although in traditional settings, the use of barcodes has spread throughout so many enterprises including warehousing, accounting, customer service functions, package delivery, as well as assembly line operations.

The motivation to begin barcoding in all these applications was borne out of the need to improve data management and accessibility and reduce costs.

As far back as the 1970s and 1980s, the increased use of computers in commercial and industrial companies propagated the need for improved data capture.

Components of a Barcode

The barcode system generally consists of narrow and wide black bars and white spaces sometimes referred to as parallel bars under which there is a series of alphanumeric characters representing the product information.

The bars and spaces in the barcode represent the barcode height, which is high enough for the scanner to read easily.

A characteristic barcode height should be 15% of the barcode length. A simple barcode symbol consists of five parts namely:

  1. Two quiet zones
  2. Start character
  3. Data characters which may include an optional check character
  4. A stop character

How do Barcodes Work?

Barcodes generally consist of numbers in combination with black and white lines that can be seen as bars and spaces, which usually do not give any specific information to the user by simply looking at the barcode until they are transcribed by a machine reader device into the information encoded in it.

However, the individual components of the barcode system are generally set up to make it possible for an optical scanning device to read them and get the data encoded in it to be translated into information that the user will understand.

The scanning process of a barcode scanner only takes a couple of seconds, making it fast and efficient.

Once the user scans a barcode, they get more details on the product such as price, item description, quantity, etc.

A typical barcode scanner has a light source, lens, photoconductor, and decoder, which are necessary for reading the code and subsequently understanding the information encoded in it.

During the scanning process, the scanner aligns with the barcode to be able to read it.

The light source in the barcode scanner is designed to assist in recognizing and reading the code while the lens scans it.

The photoconductor then transfers the optical impulses into an electrical signal, allowing the decoder to process and analyze the information.

The information is then sent to the system, typically a database.

As soon as the information gets to the barcode scanner database, it shares it with a computer, tablet, or any device connected to it, allowing the user to read the data.

Types of Barcodes and Barcode Fonts

When deciding on the correct type of barcode for a business, owners must consider how much data they want to transfer through the barcode on the item.

The data to be transferred will largely depend on the product, the label size, and the needs of the company or firm in which the barcode will be deployed.

The country of use is also very important to be considered.

There are differences between basic and more advanced barcode types called 1D and 2D codes.

1D Codes

This is known as One-dimensional (1D) or linear codes are basic codes that hold standard information on the product type, product size, and color and that the barcodes are scanned horizontally by a barcode scanner.

The length of the barcode depends on the amount of information encoded in it, but they are nevertheless limited to about 43 characters.

Some common examples of 1D codes are UPC, DataBar, Code93, Code39, EAN, UPC-A, and UPC-E codes.

2D Codes

The Two-dimensional (2D) codes are advanced types of barcode systems that carry both essential and additional information, ranging from the price of goods and inventory to voice data, websites, and images of items purchased.

In addition to parallel lines of 1D Codes, they use different patterns, such as hexagons, dots, and squares, but they remain smaller in length than 1D codes.

Due to their unique characteristic patterns, they can have several times more characters and thus gather more data than 1D codes. These characters can be stored vertically and horizontally as well. This makes the reading with a barcode scanner easier.

Most of the barcodes can be read with 1D scanners and smartphones, thereby simplifying the process.

More information is encoded in the 2D codes to track a product while the codes themselves are smaller and easier to read.

When scanning them you don’t have to worry about their position since they can be read from either the vertical or the horizontal direction, speeding up the process.

One of the most prominent 2D codes for consumers is the quick response (QR) code, consisting of black and white pixels in a small square.

There are several Barcode fonts employed in generating barcodes for items or products of choice. One of the most prominent of them is Code39. In addition to Code39, there are several other barcodes available, including the following:

Code 128: This code is usually used for shipping and supply chain labels. This is similar to Code39 as it can encode the same set of characters just like Code39 (although uppercase and lowercase letters can appear differently). There is usually no length restriction when using this code.

UPC/EAN: This is the barcode most people are familiar with because it is used for product barcodes. UPC stands for Universal Product Code while EAN stands for European Article Numbering. This particular type of barcode is also known as UPC-A. It is usually restricted to about 12 characters.

I2of5: This is a two-row barcode that is used on rolls of 35mm film. Its application is to label some cartons. It is also known as industrial 2 of 5 or interleaved 2 of 5, and it encodes numeric characters.

UPC-E: This is a type of barcode where certain characters for example leading zeros are omitted, to keep the code to within six digits. It is a variation of a UPC barcode.

QR: This particular type of 2D barcode can be used in opening some websites or applications when scanned with a mobile phone camera that has barcode enabled app. These types of barcodes are usually applied for a printed ticket, to provide some form of a discount and in some cases for mobile payment. The barcode can encode numbers and alphabets and binary. The full meaning of QR is Quick Response.

Creating Barcodes in Excel

To create Barcode in Excel, you will need to download a barcode font.

One of the most common fonts and the one we’ll use in this tutorial is code39.

The reason for this choice is not farfetched as we have earlier highlighted its advantages over some barcode fonts available.

Each character display appears as five bars and four spaces; three of the nine bars in the barcode will always be wider.

You can download Code39 from several websites including Dafont, Free Barcode Font, ID Automation, etc.

After downloading the font you want, in this case, Code39, you should follow Microsoft’s instructions on the installation of a font for Office to get it working with any version of your excel.

Note that some operating systems may not be compatible with Code39.

After installing the font, you can easily create barcodes in Excel.

In this tutorial, we will be creating barcodes for the following information listed in columns A, B, and C.

To recreate the above example, simply follow the steps below.

Step 1: Create four columns labeled A, B, C, and D in a blank spreadsheet and title the columns Item, Lot number, Expiry date, and Barcode respectively.

Place the alphanumeric data in the Text column A, B, and C. The barcodes will appear in the column labeled Barcode.

Step 2: Format the cells in the Text column.

Select the Text column to format the cells, then right-click on it. A dialog box appears, click ‘Format Cells’.

You will notice that the column for the Barcode was not highlighted. This is because the barcode column need not be formatted to text.

Once you click on format, another dialog box will appear. Click Number, click Text, and click on OK.

This will prevent larger numbers from displaying in scientific notation as well as preserving numbers or information that start with Zero.

Step 3: Type the information you want to appear on the barcode.

In this case, we will be needing the list of reagents used in a diagnostic laboratory, their lot numbers, and their expiry dates.

The Code39 font can encode numbers from 1-9, alphabets from A-Z, the dash sign (-), dollar sign ($), percent sign (%) period or full stop (.), slash sign (/), plus sign (+), as well as a space. Code39 font codes both the uppercase and lowercase the same.

You can also encode the asterisk (*) sign, which is usually used as an indicator for the beginning and end of the sequence, this is also why the formula in Step four will require us to add an asterisk before and after you type your character.

Step 4: Enter the following formula: =”*”&A2&B2&C2&”*” in the first blank row of the Barcode column.

In this step, the first column we are working with is the Text column A2; in case we decide not that start our barcoding from the first column, we can adjust the formula accordingly.

If there is no text in the first three columns, the barcode column will display three asterisks. Press enter to continue.

Step 5: Fill the Barcode column down by clicking on the first row of the column and dragging it down to the last item.

Step 6: Change the font in the Barcode column.

Navigate to the font menu, select the downloaded and installed barcode font.

The Barcode row will automatically be filled with barcodes.

You can decide to use the same font for the header row as you do for the rest of the spreadsheet, but that will depend on your preference. You can also change the size of your barcode font by selecting your preferred size from the drop-down list.

Advantages and Disadvantages of using Code39

Advantages of using Code39

Code39 can also be referred to as the 3 of 9 Barcode, Code 3 of 9, as well as Barcode39.

The following are some of the reasons Code39 is considered one of the most preferred barcode fonts

  • It is well accepted.
  • It can be downloaded for free
  • It can encode all 26 Alphabets of the English language (A-Z) and the numerals (0-9) which were not possible with previous barcodes.
  • It is much more secure and is not prone to wrong encoding and decoding.
  • It is a self-checking barcode format which means a single print defect cannot lead to misrepresentation of the character into another character.

Limitations of using Code39

Just like any other barcode font, there are limitations in using some codes in which case, code39 is not exempted.

The following can be associated with the use of code39.

  • Code39 barcode can be easily defaced and distorted like most linear barcodes.
  • Code39 is a width-encoding Symbology that can be quickly become unreadable when a slight ink spread on it during printing.
  • Code39 has limited allowable characters to be encoded on it.
  • Code 39’s width is about 30% wider than the same encryption for code128; this results in scanning difficulties.
  • Lowercase characters are not allowed in the code39 format but may also be used with the code39 extended format.
  • Some barcode readers do not accept the extended code 39, it is important to try out your code with your reader before deploying it into use.

Generating Random Barcodes in Excel

You may have wondered how retail stores, firms, and some organizations that use barcodes on their product can generate unique numbers for their products.  The RANDBETWEEN Excel function can generate random numbers for barcodes. You can follow the steps below to automatically create random numbers for barcodes.

Step 1: Create two separate columns and name the first column numbers since we are interested in generating random numbers, and the second column Barcode.

Note that you don’t need to format the column as we did in the previous step.

Step 2: Enter the following formula: =”*”&A2&”*” in the first blank row of the Barcode column.

Step 3: Fill the Barcode column down by clicking on the first row of the column and dragging down to as many random numbers as you require.

Step 4: Enter the RANDBETWEEN function In the first cell of the text column, that is =RANDBETWEEN(X,Y), where X is the lowest value and Y the highest. In this example, we will use, =RANDBETWEEN(3000,50000) to generate random numbers between 3000 and 50000.

Step 5: Fill down the row to generate random barcode numbers. Each cell will have a random number in the specified range.

Step 6: Change the font in the Barcode column by Navigating to the font menu and selecting the downloaded and installed barcode font.

Result:

How to Use Excel Barcode Fonts

Once you’ve created barcodes, you need to deploy them. Barcodes can be printed in catalogs and retrieve the data encoded in the barcode with a scanner. A smartphone that has a designated scanning app can also be used.

How to Use a Barcode Scanner with Excel

The use of scanners to scan barcodes is a great way to reduce human error and facilitate data entry tasks be it in office space or a business.

Scanners work well with any computer program, including Excel.

When barcodes are scanned, the operating system or application (in the case of mobile apps) sees the scanned bar code as just text, and it will insert the text wherever there’s an active cursor.

Barcode scanners can be used with Excel to scan existing barcodes and enter the data into Excel.

For example, when items are received in bulk and information such as the Lot number or unique codes needs to be entered, scanners will be the escape route to this complex task of entering each item one by one.

Conclusion

In this tutorial, we have demonstrated how to create barcodes using excel as well as generating random barcodes using a simple excel function.

No matter your space of endeavor or roles you assume, be it in the retail industry, healthcare, manufacturing, and several other firms, we have been able to establish that you can generate your barcodes using cost-effective, simple means in achieving the objectives of this tutorial

Avatar photo
Ovo is a highly specialized Computer and Networking Expert with experience in Windows, Cisco, Microsoft Office (Excel, Word, etc) and Networking engineering. Ovo is a creative, team player that loves sharing his experience with technology with readers to follow along. He has great attention to detail when discussing various technologies, tutorials and guides.