Creating Barcodes in ExcelStep by Step Instructions with Screenshots

Written By:
Adiste Mae
Reviewed By:
FundsNet Staff

In our time, machines can easily read data by using Barcodes.

Barcodes are commonly used in the available products in our market, whether it is clothing, groceries, appliances, etc.

This short tutorial will show you how barcodes are utilized in Excel, and how to add barcodes to your worksheet.

Here are some sample barcodes used in Excel:

This can be very beneficial in your inventory management.

Excel allows formatting any cell as your customizable barcode.

It can also be beneficial for you to create product listings with all these barcodes, UPCs, or in tracking any item.

However, there is no built-in function in Excel to convert values to usable barcodes. This is remedied by using the Barcode font.

Let us explore the ways to do this. 

Using the Code 39 Barcode Font (Download and Install)

First, you need to download and install the barcode font in Excel.

Code 39 is the most commonly used font, and we are going to use this in our sample problem.

Here is the site to download the font: https://www.dafont.com/search.php?q=code+39

Simply click the link and click the download button beside Code 39 font. The format of the downloaded file is in .TTF file (TrueType font), and is in a ZIP folder.

Here is the step-by-step process for installing the Barcode font in Excel:

  1. Open the ZIP folder (downloaded from the link given above) by performing Double click action.
  2. Now, Double-click the .TTF file.
  3. Click on Install from the opened window. This action will install the Code 39 font in your Excel system.
  4. Lastly, close the dialogue box.

When you have now installed the Code 39 font on your computer, you will now be able to use the font in Excel.

If you already have the font on your computer, skip the step-by-step tutorial above.

Creating Barcodes in Excel

Let us now look at how to create barcodes in MS Excel since you have already installed the font.

Converting Cell Format to Text

You need to remember important things before converting numbers or alphanumeric characters into barcodes.

The cells containing numbers (cells you need to convert to barcodes) should be in text format.

Why this is important?

Excel has automatic number formatting enabled for you to work easily. In this case, if you enter 0001 in a certain cell, it will be converted to 1 (for Excel, the zeros are useless) as a whole number.

If you type a large number, for example, 1234567654316, Excel will convert it to 1.234567E+11.

But in creating barcodes, you need all the characters to successfully generate a barcode. You don’t want the automatic formatting enabled. Thus, you need the format of the cells to text, because this ensures the numbers will not be converted.

Here is how you can change the format of the cells to text:

  1. First, select the cells where you want to place your barcodes (column A).
  2. Go to the ‘Home’ tab, and click on ‘General’ (Numbers).
  3. Lastly, click ‘Text’.

Now, the format of the cell is in text. You can now enter the values you want to convert to barcodes.

If you are copying the values, try pasting using ‘Values only.

Entering Values for Barcodes

Now that you already have the column formatted as Text, start entering the values you want to convert to a barcode.

In our scenario, we don’t want to directly convert the value, but instead, we shall be converting it to the next cell beside the value.

This can be easily done.

Here is the step-by-step process on how to copy values from columns A to B.

  1. Enter the formula in cell B2: =A2;
  2. Now, draw down the formula in column B up to the last cell in column A.

Excel will automatically copy all the formats in column A to column B, including the conversion of numbers to text.

Formatting the Values to Convert into Barcodes

If you are finished copying the values in column B, you can now start formatting the values in column B to Barcodes (Code 39).

Here is the step-by-step process:

  1.  In column B, select the rest of the values of the cell.
  2. Go to the ‘Home’ tab, and then click the Font menu.
  3. Now, click on Code39.

The barcodes are now present in column B. For more visibility, you can make the font size smaller or bigger, depending on what you prefer.

Here is how you can change the font:

  1.       In your ‘Home’ tab, click the font size menu.
  2.       For our sample, we will choose font size 26.

Here is your result in column B, corresponding to column A.

In the example given above, the barcode may change depending on the value in column A. Thus, if you change the value on each cell, the barcode would automatically update.

Note:

If you install the Code 39 font on your computer, it will not only work in Excel. It will also work in other MS applications (MS Word and MS PowerPoint).

Barcodes Types

Code 39 font is the most used and common barcode (including numbers and letters).

This is the type of font that is characterized by having five bars and four spaces, whereas three bars are wider than the others. That is why it’s called the name 39.

Other barcodes exist such as Code 128. This is used for shipping similar to Code 39.

These barcodes can also be installed in your system.

Here are the other types of barcodes:

  • Code 128: used for supply chain labels and shipping. This uses the full ASCII character set.
  • UPC/EAN: UPC is also known as Universal Product Code and EAN is European Article Numbering. This is a commonly used barcode in items in store and at the point of sale. UPC is limited to 12 characters.
  • UPC-E: UPC’s simple version, this is created for small packages (12 characters barcode doesn’t fit). This is limited to 6-digit characters only.
  • QR: QR is also known as Quick Response, this is a commonly used barcode in apps or in websites that is scanned by a mobile phone camera.
  • I2of5: it is also known as (Interleaved 2 of 5), whereas 2 out of each five bars are wide. This supports numerical characters and high-density barcodes. It is used on 35mm film rolls, and also labeled in cartons. (Two-row barcode).
  • Postnet: it is also known as Postal Numeric Encoding Technique and is commonly used by U.S. Postal Service (Numeric characters only).
  • Intelligent Mail: also used in the U.S. Postal Service. It uses 65 characters and numeric characters only. It uses the width of the bars and spaces, and even the height and vertical setting can be read and altered to information.

How to Generate Random Barcodes in Excel

You can now create random barcodes in Excel by using the methods mentioned above.

Assuming that you need to create a random list of 20 barcodes, you can do this by simply using the RANDBETWEEN formula. Here you can state the upper and lower limit and this will generate a random number between the chosen limit.

Say you want to generate random numbers between 1 and 100. By using RANDBETWEEN(1,100), and entering it into your chosen 20 cells, this will give you 20 random values.

Here is the step-by-step process of how to generate random barcodes:

  1. Choose any cell, and enter this formula: RANDBETWEEN(1,100).
  2. Now, in the 20 cells you choose, copy the formula and paste it.
  3. Select and copy all the randomly generated numbers in that cell.
  4. Lastly, paste it as Value.

The steps above would now generate random values (20 random numbers) and then convert them to values.

Note: this is only applicable in numeric only. It is hard to use alphanumeric characters.

After doing the steps above, apply these steps:

  1. First, select all the copied numbers.
  2. Go to the ‘Home’ tab.
  3. Now, select the Code 39 font.

If you want the numbers and barcodes in a separate cell, copy and paste the numbers next to them. Convert the copied number to a barcode font (Code 39). This will give you a serial number and barcode next to each other.

Using Third-Party Tools and Excel Barcode Add-Ins

There are other ways of generating barcodes in Excel.

You can also use web-based tools and other applications in generating barcodes.

Here are some known barcode generators:

  • Cognex
  • Barcode.tec
  • POSGuys
  • Bartender Software

In this tutorial, we tackle how to generate a barcode easily by using the Code 39 font in Excel.

Hope this helps you a lot with your project.