Barcode is a machine-readable code that can store information. Barcode is widely used in business to identify and track products, inventory, assets, etc. We can create barcode in Excel quite easily.
In this free Excel tutorial, we will create & use barcodes in Excel based on the model value of a product.
In this blog post, you will learn how to create-
– a random barcode
– barcodes from random numbers
– barcodes without downloading fonts
We will also learn how to create a QR code in Excel which is a popular type of two-dimensional barcode. Besides, we will explain the use of a barcode scanner for inventory management in Excel.
⏷What Is a Barcode in Excel?
⏷What Is a Barcode Font?
⏷How Many Types of Barcodes and Barcode Fonts Are Available in Excel?
⏵What Are the Differences Between Code 39 and Code 128 Barcode Fonts?
⏷Which Excel Versions Allow to Create Barcodes?
⏷How to Install Barcode Font in Excel?
⏷How to Create Barcode in Excel?
⏵1. Create a Random Barcode in Excel
⏵2. Create a Barcode from Random Numbers
⏵3. Create Barcodes Without Downloading Fonts
⏵4. Create Excel Barcodes with Mac, Android, and iOS
⏷Which Websites Provide Free Barcodes?
⏷How to Use Barcode Scanner in Excel?
⏵How to Use Barcode Scanner for Inventory in Excel?
⏷How to Print Excel Barcodes?
⏷What Are the Advantages and Disadvantages of Creating Barcode in Excel?
⏷What Are QR Codes and How to Create Them in Excel?
Barcode is a series of parallel black and white bars of different widths that represent numbers and symbols. Only machines can read barcodes and extract information encoded in them. Barcodes are mainly used in business industries regarding manufacturing, logistics, transportation, healthcare, etc.
A barcode font is a specially customized font to generate a barcode. It directly converts standard text characters into black-and-white bars of different widths. The black and white bars of different widths can encode a huge huge amount of information within a small space. As a result, it ensures better safety and lessens workloads.
There are a lot of barcodes and barcode fonts are available. Some of them are:
- Code 39: Code 39 barcode font for Excel can encode both numbers and letters. It also encodes characters like the dash (-), dollar sign ($), percent sign (%), slash (/), plus (+), a space, and the asterisk (*). It doesn’t recognize lowercase characters. So there is an alternative Code 39 font. It is the IDAHC39M Code 39 barcode font. We use it mostly to create barcodes.
- Barcode: This is another popular font. It is the same as the Code 39 font.
- Code 128: Code 128 barcode font for Excel is mostly used in shipping and supply change labels. It can encode the same characters as Code 39.
- UPC/EAN: We use this as a product barcode. Here, UPC is Universal Product Code and EAN is European Article Numbering. It is also called as UPC-A. This is restricted to 12 characters.
- I2of5: This means industrial 2 of 5 or interleaved 2 of 5. It can encode numeric characters only.
- UPC-E: This is another UPC barcode. It omits certain characters like leading zeros. It can encode six-digit codes only.
- QR: QR means Quick Response. This is a 2D barcode. We can use it to open a website or app after scanning with a mobile phone camera.
- Postnet: The U.S. Postal Service used this. It can encode numeric characters.
- Intelligent Mail: The U.S. Postal Service uses this now. It can encode up to 65 characters.
Though Code 39 and Code 128 are two commonly used barcode fonts to convert standard text characters into barcodes, there are some major differences between them. They are listed below.
|Features||Code 39||Code 128|
|Data Capacity||Limited, up to 43 characters||Entire ASCII character set|
First of all, there is no built-in font to create barcode in Excel. But Excel provides limited capabilities to create one-dimensional barcodes such as Code 39 and Code 128 from the Excel 2007 version to the later ones. In order to create more sophisticated two-dimensional barcodes like QR Code, we are in need to install third-party add-ins or external barcode generator software or websites.
There is no built-in barcode font in Excel. So, we need to add a barcode font in Excel. For this, we can install a preferred barcode font and use it to create a barcode from standard text characters.
- Go to the Dafont site to download the IDAHC39M Code 39 barcode font. The font is named IDAutomationHC39M.
- Now, right-click on the downloaded file and click on Extract Here to unzip the font file at the same location.
- Open the file with the .ttf extension.
- Click on Install to install the font.
Now, restart Excel to have the font in Excel.
Similarly, you can download other barcode fonts like Code 128, Barcode Font, etc.
We can take a text-formatted number and change its font to create a barcode in Excel. We can also generate a random number within a certain limit with the RANDBETWEEN function and change its font into a barcode font. With the help of the IMAGE function and a barcode generator website’s link, we can automatically generate barcodes. You can use any of the above methods to generate barcodes in bulk amounts too.
To explain all the barcode generation methods, we will consider the following dataset. The dataset contains the product information in the ‘Model’, ‘Device’, ‘Brand’, and ‘Price’ columns. We will use the values in the ‘Model’ column to generate barcodes.
One of the easiest ways to create a barcode in Excel is to take a random number and link barcodes to that Excel cell.
- Apply the following formula in cell F6 to have the model number of cell B6 in text format.
- Use the Fill Handle tool to apply the formula till cell F15.
- Now, we have model numbers of all products in text format in the ‘Barcode’ column.
- Select all the cells in ‘Barcode’.
- Choose the IDAHC39M Code 39 font from the Font group under the Home tab.
- Finally, we have barcodes from the model numbers.
We can use the RANDBETWEEN function to generate random numbers and use them to create barcodes in Excel.
- Use the following formula in cell F6 to generate a random number between 1000000 and 9999999.
- Drag Fill Handle to autofill till cell F15.
- Now, select all the random numbers.
- Copy the numbers by clicking on Copy from the Home tab.
- Select cell F6 and paste the copied numbers as values from the Home tab.
- After that, apply the following formula in cell G6 to have the number of cell F6 in text format.
- Use Fill Handle to apply the formula till cell G15.
- Select all the cells in ‘Barcode’ and choose the Code 128 font from the Font group under the Home tab.
- Thus, we can convert numbers to barcodes in Excel.
- Insert the following formula in cell F6 and drag till F15 to auto-generate barcodes.
For Macs, we can generate barcodes in Excel just like a normal PC. Android supports barcode fonts. So, you can generate barcodes quite easily. You can also get help from barcode generator apps and websites. Due to security issues, iOS does not support random apps and websites to generate barcodes. You can download authorized barcode generator apps from Apple Store and create barcodes.
There are quite a few authentic websites that provide free barcodes. Some of them are listed below:
A Barcode Scanner is a handheld or stationary input device. It consists of a lens, a light source, and a light sensor for translating optical impulses into electrical signals. It is used to capture and read the information contained in barcodes.
In order to use a barcode scanner in Excel–
- Choose a barcode scanner that is appropriate for your computer.
- Connect the scanner to your computer through Bluetooth or USB connectivity. In most cases, there is no need to install any additional software or add-ins to connect them. Read the manufacturer’s instructions carefully if there is any need for any special configuration steps.
- Now, put the cursor on an Excel cell and scan the barcode of a product. You will have all the related information with the barcode in the Excel cells.
- Repeat the scanning procedure to store data in an Excel worksheet.
Nowadays, barcode scanners are very commonly used in inventory to identify and keep track of the inventory. The process of using a barcode scanner for inventory in Excel is also very simple.
- Connect the barcode scanner to your computer through USB or Bluetooth connectivity.
- To have a trial of scanning, open a text editor (i.e. Notepad) and scan a barcode with the barcode scanner. We will have the related information with the barcode in the text editor as if it is typed manually.
- Now, create a dataset with the column headers according to the information that we have in the trial portion in the text editor.
- Put the cursor in a cell and scan the barcode with a scanner.
- All the related information will be inserted in the Excel cells automatically.
- Repeat the same procedures for all the products.
We might face the necessity to print Excel barcodes to have them as a hardcopy.
- Go to the File tab.
- Click on the Print tab to have the print preview in a small window.
- Finally, click on the Print option to print the Excel barcodes.
The advantages of barcodes in Excel are:
- Barcodes reduce data entry errors as entering data manually in spreadsheets is prone to errors.
- They enable quick and efficient data collection.
- They improve data security.
- They are cost-effective.
- You can easily customize them.
- Barcode scanners can quickly read them.
The disadvantages of barcodes in Excel are:
- You have to download the barcode fonts from other sites.
- It is time-consuming. You can’t generate a lot of barcodes quickly.
- Sometimes, you need to use some add-ins to create a barcode.
- We need to implement a barcode scanner to read barcodes which requires a significant amount of initial cost.
- Barcodes need proper physical maintenance to ensure their readability.
QR Codes are one kind of two-dimensional barcodes. They are encrypted squares that can store content, links, event information, and other information based on user preference.
We can easily create QR codes in Excel with the help of the IMAGE function.
- To create QR codes in Excel, just apply the following formula in cell D6 and drag to cell D10.
Download Practice Workbook
You can download the practice workbook and try it yourself.
In this article, we have learned how to create a barcode by taking a text-formatted number and changing its font; generating a random number within a certain limit with the RANDBETWEEN function and changing its font; combining the IMAGE function and a barcode generator website’s link. We have also learned the use of barcode scanner, printing of Excel barcodes, and the advantages and disadvantages of barcode in Excel.