Barcode In Excel

What Is Barcode In Excel?

A Barcode in Excel encodes characters entered in a spreadsheet and stores the information in a form that machines can read. It contains a series of black bars of varying widths and white spaces.

Barcodes make it quicker and easier to identify items, ensuring accuracy and cost-effectiveness. Thus, users can use barcodes to track stock items and develop product lists, making the Excel option useful in distribution chains and logistic processes. For example, the table below shows a list of items and their respective codes. Scanning such codes might take time. Instead, the process will be relatively quick once we create barcode in Excel for each item listed in the table.

However, we would have to download fonts to generate barcodes in Excel since they are not available by default. For example, the above table contains barcodes generated using the downloaded barcode font, Code39.

Install Font For Barcode In Excel

We can find several websites offering free and paid barcode fonts, and dafont.com is one such site where we can download various fonts for free.

Suppose we want to use Code 128 to create barcode in Excel. But, first, let us see the steps to install the barcode font Code 128 from the above website.

Step 1: To begin with, open the site and enter the barcode font Code128 in the search box.

Step 2: Next, the search result will show the Code 128 font software. Click on Download (highlighted in the above image) to download the package on our computer.

We will see a .zip file in our Downloads folder immediately after downloading.

Step 3: The code_128.zip file will have a .ttf file.

Double-click on it to run the setup file for installing the particular barcode font on our computer.

Step 4: Then, click the Install button in Code 128 (TrueType) window (highlighted in the above image).

And once installed, the button will grey out.

We can now close the window.

Likewise, we can download and install the required barcode fonts from the website.

Click the link pointed out in the image below. It will show us the different barcode fonts we can use in our Excel applications.

Once installed, we will have to save and close our workbook, if it is open, and then open it again to view the barcode fonts in the font list in the Home tab. We can now use the barcode in Excel feature.

How To Generate/Create A Barcode In Excel?

After we install the specific barcode fonts on our computer, here is how we can generate a barcode in Excel.

• When we create the source data, ensure the codes as barcodes are in Text
• Select them and choose the required barcode font from the font list in the Home

Thus, when scanning, the machine can read barcode in Excel we generated for each code in our source table.

For example, consider the below table containing a list of products and their unique IDs or codes.

Suppose we want to create the barcodes for the product IDs in column B. Then the steps are:

Step 1: First, select the codes in column B and check their format using the Number Format option in the Home tab. They should be in Text format.

If they are not, select them and set the Number Format as Text using the Text option from its drop-down list.

Please Note: The Text format ensures large numbers are not displayed in scientific notation, and the values containing leading zeros remain unchanged.

Alternatively, we can select the required data, right-click to open the context menu and choose the Format Cells option.

In the Format Cells window, choose the Category as Text in the Number tab.

Step 2: Next, let us show the barcodes in column C. So, copy the product IDs into column C by first selecting the cell C2 and entering the below formula:

=B2

Then, using the fill handle, copy the formula in cells C3:C11.

Step 3: Then, select the cell range C2:C11 and choose the required barcode font from the font list in the Home tab. Let us select Code 128 for the example table.

Once we choose the specific barcode and increase the font size, the output will be:

We can also use the barcode in Excel formula to generate a barcode.

Step 1: To begin with, ensure the codes in column B have a text format and then enter the formula in cell C2 as

=”(“&B2&”)”

Alternatively, we can enter the formula as “*” &B2&”*”.

Press Enter.

Step 2: Next, drag the fill handle to copy the formulas in cells C3:C11.

Step 3: Then, choose the cell range C2:C11 and select the required barcode font from the font list in the Home tab.

The resulting barcodes will be:

Please Note: The barcodes generated using the above formula differ from the ones created previously for the same product IDs, though we use the same barcode font, Code 128. The reason is that the barcode font encodes the product IDs and the parentheses in the second scenario.

Types And Fonts Of Barcode In Excel

Below are the barcode fonts and types we will find on websites. Each font helps us use the barcode in Excel feature in specific scenarios.

1. Code 39: The simplest to use and includes numbers, letters, and alphanumeric characters. Typically, we would use it to scan barcode in Excel.
2. Code 128: A denser code than Code 39 and can utilize the entire ASCII character set. It finds application in the shipping industry and supply chain.
3. UPC/EAN: UPC and EAN expand as Universal Product Code and European Article Numbering. UPC is 12 characters, and these are mostly used for items in stores.
4. UPC-E: It is six characters in length and used for small packages.
5. QR: QR expands as Quick Response. Typically, users can scan the QR code to open apps or websites and complete online payments when it is in printed form.
6. I2of5: I2of5 or Interleaved 2 of 5 is a highly-dense two-row barcode and applies to numeric characters. We will find these on cartons.
7. Postnet: It expands as Postal Numeric Encoding Technique. The U.S. Postal Services uses this barcode to encode numerical characters.
8. Intelligent Mail: It encodes a maximum of 65 numeric characters, and the U.S. Postal Services uses it.

Examples

Here are a few popular fonts we can pick when we use the barcode in Excel feature.

Creating Barcode Using The “Code39” Barcode Font

If we need to use barcodes for our inventory identification, we can pick the font Code39 while utilizing the barcode in Excel option. It encodes numerical values, upper case letters, a few special characters, and the space character.

For example, the below table shows product codes to visualize as barcodes.

If we use the respective Product Code to scan each item, the machine might not understand it. So instead, we can create a barcode corresponding to each product code in the worksheet. The scanning machine can then successfully read barcode in Excel.

Step 1: To begin with, download the Code 39 font software package online and install it on the computer, as explained earlier.

Step 2: Next, open the workbook containing the above Excel table. Change the format of product codes in cells B3 and B11 to Text.

Step 3: Then, enter the formula in cell B5 as:

=B3

And enter the formula in cell B13 as:

=B11

Step 4: Choose cells B5 and B13. And then set Code39 as the font in the Home tab.

Step 5: Choose cells B5 and B13 and increase the font size to get clear barcodes.

Please Note: The barcode length increases with the number of characters it encodes.

Creating Barcode Using The “Barcode” Font

The table below lists the subjects and their corresponding textbook editions and codes.

Let us see how to update the Barcode column using the Barcode font.

Step 1: Download the Barcode font software package online and install it on the computer, as explained earlier.

Step 2: Open the workbook containing the above Excel table. Change the format of textbook codes in cell range C2:C6 to Text. The reason is that we need to create barcode in Excel for each of the textbook codes in column C.

Step 3: Enter the formula in cell D2 as:

=C2

Drag the fill handle downwards to copy the formulas in cells D3:D6.

Step 4: Choose cells D2:D6 and set the font as Barcode in the Home tab.

Step 5: Choose cells D2:D6 and increase the font size to increase the size of each barcode in Excel.

The Barcode font includes the text codes in the generated barcodes. We can see them below the bars.

Creating Barcode Using The “Code128” Font

Code128 encodes numerical values, text, and 128 ASCII characters.

For example, we need to create and scan barcode in Excel, using Code 128 font, for each of the below smartphone models, based on the model codes provided in column C.

Step 1: First, download the Code 128 font software package online and install it on our computer, as explained earlier.

Step 2: Next, open the workbook containing the above Excel table. Change the format of the smartphone model codes in cell range C2:C7 to Text, as we need to generate the barcodes from them.

Step 3: Then, let us use the barcode in Excel formula in column D cells. So, enter the expression in cell D2 as:

=”(“&C2&”)”

Drag the fill handle downwards to copy the formulas in cells D3:D7.

Step 4: Choose cell range D2:D7 and set the font as Code 128 in the Home tab.

Step 5: Choose cells D2:D7 and increase the font size to increase the size of each barcode in Excel.

Important Things To Note

• We can generate a barcode in Excel using barcode fonts. However, by default, Excel does not provide barcode fonts; we need to download them from websites and install them on our computers.
• Once we download and install the barcode fonts on our computer, ensure to save and close our active worksheet and reopen it. Only then we can view the barcode fonts in the font list present in the Home.
• Ensure the codes we convert into barcodes are in text format to retain their meaning.

Frequently Asked Questions

Download Template

This article must be helpful to understand the Barcode In Excel, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to Barcode In Excel. Here we discuss how to install and create barcodes with examples and downloadable excel template. You can learn more from the following articles – 

• Convert Excel To CSV
• XML in Excel
• Header And Footer In Excel