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.

Barcode In Excel Intro

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.

Key Takeaways
  • A barcode in Excel consists of a series of bars, with the bars representing encoded characters. Barcodes are useful for identifying items quickly in domains such as inventory management.
  • We can use Code39, Barcode, and Code 128 to generate barcodes in Excel. However, according to our requirements, we can also pick other barcode fonts, such as UPC/EAN, UPC-E, and I2of5.
  • Code39 and Code 128 are the fonts typically used to create barcodes that any barcode scanner can scan.
  • The barcode Excel formula uses parentheses and the cell reference to the text product code to generate a barcode.

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.

Barcode In Excel Install Font

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.

Barcode In Excel Install Font.1

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

Barcode In Excel Install Font.2

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

Install Font.3

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.

Barcode In Excel Install Font.4

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.

Install Font.5
Barcode In Excel Install Font.6

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.

Barcode In Excel Generate.1

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.

Generate.2
Generate.3

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.

Generate.4

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

Generate.5

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

Barcode In Excel Generate 2

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

Barcode In Excel Generate 3

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.

Barcode In Excel Generate 3

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

Barcode In Excel Generate 3-1

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&”)”

Generate - 2

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

Press Enter.

Barcode In Excel Generate - 2.1

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

Barcode In Excel Generate - 2.2

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

Barcode In Excel Generate 3

The resulting barcodes will be:

Barcode In Excel Generate 3-1

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.

Barcode In Excel Example 1

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.

Example 1.1

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

Example 1.2

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

=B3

Example 1.3-1

And enter the formula in cell B13 as:

=B11

Example 1.3-2

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

Example 1.4

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

Example 1.5

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.

Example 2

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.

Example 2.1

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.

Example 2.2

Step 3: Enter the formula in cell D2 as:

=C2

Barcode In Excel Example 2.3

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

Example 2.3-1

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

Barcode In Excel Example 2.4

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

Barcode In Excel Example 2.5

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.

Example 3

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

Barcode In Excel Example 3.1

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.

Example 3.2

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

=”(“&C2&”)”

Barcode In Excel Example 3.3

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

Barcode In Excel Example 3.3-1

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

Example 3.4

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

Example 3.5

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

Where is barcode in Excel?


Barcode in Excel is not available by default. We need to:

1. First, download the required barcode fonts online and install them on our computer.
2. Then open our workbook to view the barcode fonts in the font list in the Home tab.

How to add barcode font in Excel?


We can add barcode font in Excel using the following steps:

Step 1: First, download the required barcode font from a trusted website to install it on our computer.
For example, let us download the Barcode font from the website dafont.com.

Example 2.1

Step 2: Next, in the downloaded .zip file, double-click on the BarcodeFont.ttf file.

Barcode In Excel FAQ 1

Step 3: The barcode font (TrueType) window opens. Click Install.

FAQ 1.1

Once installation is complete, the Install button will grey out. Close the window.

Step 4: Now, if we open our workbook, we will see the barcode font in the font list, present in the Home tab.

Barcode In Excel FAQ 1.2

Please Note: If we had our workbook open while installing the font, we would have to save and close our workbook and then reopen it after the font installation to view it in the font list.

What is the formula for barcode in Excel?


The formula for barcode in Excel contains an opening parenthesis in double-quotes, ‘&‘, the text code cell reference, ‘&‘, and a closing parenthesis in double-quotes. For example, if the text code is in cell B2, the expression in the target cell will be:

=”(“&B2&”)”

Alternatively, we can use ‘*’ instead of the opening and closing parentheses.

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.

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 – 

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *