logo
logo
Inventory

How to Create a Barcode Inventory in Excel

Ngoc Lee
How to Create a Barcode Inventory in Excel
Share this:

Using Excel as converting numbers and characters into barcodes can help with inventory management and barcode management activities in your organization. This step-by-step guide will teach you how to create a barcode inventory in Excel, how to create random barcodes, different types of barcodes and barcode fonts, and which versions of Excel allow barcode generation. 

How to Create a Barcode Inventory in Excel
 How to Create a Barcode Inventory in Excel

How to Create a Barcode Inventory in Excel

Downloading barcode fonts is the first thing you will have to do if you want to create barcodes on Microsoft Excel. The font that is often used by many beginners is code 39. It is not difficult to download the font, you can download it in several places like Free Barcode Fonts, Dafont, Square Gear, or Automation ID. Once the font download is complete, follow Microsoft's instructions on how to install fonts for Office. This will make the code work with your desktop version of Excel. Here are a number more tips for installing fonts:

  • You can exit or restart Excel if the font displayed in the list is not the font you want.
  • Not all typefaces are fully compatible with all operating systems and versions of Excel. If one doesn't work properly, delete it and try again.

- Read More: Order Management System: Definition, Process And Value 

- Read More: Order management system for Ecommerce: Definition, Key Effect, Benefit

Using Excel to Create a Barcode 

It's simple to produce barcodes in Excel once you've installed the font. Simply follow the instructions outlined below.

Step 1: In a blank spreadsheet, make two columns

Make-two-columns
Make two columns

In a blank spreadsheet, make two columns Fill in the Text column with alphanumeric input. The barcode will be displayed in the Barcode column. Note: Create a table-like structure with additional columns if you want to provide detailed descriptions of each product or item related to the barcode.

Step 2: Format the cells of the Text column

format cells
Format cells
choose-the-option-of-formatting-cells
Choose the option of formatting cells
Right-click-the-Text-column-to-select-it
Right-click the Text column to select it

Right-click the Text column to select it. Click Format Cells, then Numbers, and finally Text. This prevents larger numbers from being displayed in scientific notation while preserving leading zeros.

Step 3: Enter the following formula in the first blank column in the Barcode column: = "*" & A2 & "*"

Enter the following formula in the first blank column in the Barcode column: = "*" & A2 & "*" 

Enter-in-the-first-blank-column
Enter in the first blank column

In this phase, we assume that the cell in the Text column is A2; if not, change the calculation appropriately to your wishes. If there is no text in the first column, two asterisks will appear in the second column.

Step 4: Fill out the Barcode column

To change the cell format to Text, select the Code row, right-click the row, and then choose Format Cells. Select the Text format from the Format Cells chat box, and then click OK. Note: This step is done to avoid Excel removing trailing characters from your code. 

👉 Read More: How To Create Inventory Management System In Excel [Pre-made Template] 

Step 5: Modify the font in the Barcode column

Choose-the-font-from-the-font-menu
Choose the font from the font menu
install-barcode-font-from-the-font-menu
Install barcode font from the font menu

You first select the previously downloaded and installed barcode font from the font menu. Ideally, you should use the same font type for the header row as you would for the following columns. This will help the bar code table become transparent and scientific.

Step 6: Fill the Text column with alphanumeric input

Fill-the-Text-column-with-alphanumeric-input
 Fill the Text column with alphanumeric input

Code 39 font can encode letters 1-9, A-Z (uppercase and lowercase are considered the same), period (.), dash, plus sign (+), horizontal (-), dollar sign la ($), percent sign (percent) slash (/ ), and space. The typeface also encodes the asterisk (*), which is used to denote the start and end of a string. The Barcode row will be automatically filled with the barcode.

How to count inventory using Excel & Barcode scanner

To get started, download the Inventory Counting List, you will get a template that looks like this: Then delete all columns of information by "Select" and "Delete"

Inventory-Counting-List
Inventory Counting List

You are sure to have a table of your inventory prepared. Note that the information sheet should include information such as Product Name, Description, Department Name, Price, and Instock. Here is a spreadsheet that you can refer to. 

Your-Inventory-List-You-Created
 Your Inventory List You Created

Then Select All and "Control + T". Next is to click OK to create the table Select all the information on the table and then "Copy". Go to the Item List and then Save the copied information.

Open-the-Item-List
 Open the Item List

The next step you may need is Barcode and Barcode Scanner. First set the Tab key jump count by going to "options" then "advanced". In the section "Direction" choose the "Right" option and then OK. 

Set-the-Tab-key-jump-count
Set the Tab key jump count

 And now it's time to start counting. Click "Name Box" then select "Count_Here" Add items and Quantities. If UPC matches UPC in Item List, Status Check Color GREEN and Duplicate UPC Color YELLOW.

Status-Check-Color-GREEN
Status Check Color GREEN

HUPC not found in Item List, state check color RED

State-check-color-RED
State check color RED

 If you enter the wrong information, just delete it and start over Select Last Cell Then Hold Shift Click on the Name box, Select Count_Here After filling in the information, check the Item List

Check-the-Item-List
Check the Item List

 Filters cell to get data needed

Filters-cell-to-get-data-needed
Filters cell to get data needed

 Add New Item In Item List

Add-New-Item-In-Item-List
Add New Item In Item List

Note: you should add it right away while you are counting And that's the whole process you'll have to do to count your inventory using Excel and barcode scanners.

Advantages of creating barcode inventory in Excel

While barcode generation in Excel works well for many people, it is not scalable. However, you will easily and not take much time to do things like:

  • Print UPC code or product number
  • Create a product list or product category
  • Inventory tracking

All these utilities will make your inventory management more convenient and easy.

In Excel, how do you generate random barcodes?

For barcodes that require random numbers, RANDBETWEEN Excel will be a good choice for you. To generate random numbers for barcodes, follow the procedures below. Note: Change the column back to General if you converted it to text format

  1. Enter the Excel function RANDBETWEEN. Enter =RANDBETWEEN(X,Y) in the first cell of the Text column, where X is the lowest number and Y is the highest. =RANDBETWEEN(100,500), for example, will create random data between 100 and 500.
  2. Fill in the blanks in the row to produce random barcode numbers. Each cell will be assigned a number at random from the specified range.

What Excel versions can generate barcodes?

Microsoft Excel 2010, 2013, and 2016 can generate barcodes. Some older versions such as the 2003 and 2007 versions also have similar capabilities because of some built-in plugins.

Barcode Fonts and Types of Barcodes

In addition to Code 39, you can also choose from the following available barcodes:

  • Code 128: This is the generic code for the supply chain or shipping label. It is capable of encoding the same character set as Code 39. (although there will be upper and lower case differences). Code 128 also does not limit the number of words used.
  • UPC/EAN: This is the most popular barcode because it is optimized for product barcodes. UPC is an abbreviation for Universal Product Code, whereas EAN is an abbreviation for European Article Numbering. The barcode, often known as UPC-A, has a character limit of 12 characters.
  • I2of5: Double-row barcode seen on 35mm film reels and on some boxes. It encodes numeric characters and is also known as industrial 2 in 5 or 2 in 5.
  • UPC-E: This is a variant of the UPC barcode in which some characters (e.g. leading zeros) are removed to ensure a digit after the barcode.
  • QR: When scanned with a mobile phone camera, this 2D barcode opens a web page or app. These barcodes are commonly used for printed tickets, discounts, or mobile payments. Barcodes are capable of encoding alphabet, binary, and kanji data. QR is an acronym for Quick Response.
  • Postnet: This barcode is capable of encoding numeric characters and was previously used by the United States Postal Service.
Barcode-Fonts-and-Types-of-Barcodes
How to create a Barcode Inventory Fonts and Types of Barcodes In Excel

What is the value of code 39?

This option, commonly known as Barcode 3/9 and Barcode39, is popular for the following reasons:

  • It is optimally designed for the user
  • It encodes numbers as well as characters
  • It's easily accessible for free
  • Each letter is represented by four spaces and five bars; three of the nine bars in a barcode are always enlarged to facilitate workflow

Create barcodes with Word and other software

Barcodes can also be created with Word, TextEdit, Wordpad, PowerPoint, and many other features that assist you in changing the typeface. Once you've downloaded and installed the fonts, type the text into your favorite apps and change them to barcode fonts.

How to Make Use of Excel Barcode Fonts

You must deploy barcodes after you have developed them. You can scan or print barcodes in a catalog to access the data embedded in the barcode. Scanners can be custom-built or purchased off-the-shelf, and users can even use a smartphone with a scanning app.

How to create barcode in Excel 2007, 2010, 2013, 2016

Get and download barcode fonts. In the Excel spreadsheet, add two columns (Content and Barcode). In the Barcode row, write the following formula: ="*" & A2 & "*" in the first empty row on the column of the Barcode row. Then enter the formulas in the remaining columns. The numbers/letters you provide in the Text row will be converted to barcodes in the Barcode field.

Excel Barcode Makers for Free

A lot of websites exist that will convert alphabetic text into barcodes. If you don't want to use Excel or simply need to have a barcode once in a while, the sites listed below are suitable alternatives:

  • Bartender Software
  • POSGuys
  • Barcode.tec
  • Cognex

How to create barcodes in bulk in Excel

If you require a large number of barcodes, you can produce them individually by following these instructions, although this can be time-consuming. A barcode generator plug-in would be another reasonable alternative for you. You can also use POSGuys' online batch barcode generator and then upload them to your spreadsheet.

👉 Read More: QR Code Inventory Management in Excel: A Complete Guide To Create 

👉 Read More: How Can You Disposal Of Obsolete Inventory?

Conclusion

How to create a barcode in Excel will become easy if you know the above information and follow the instructions. Hope you have done what you want after reading this article.

Resource

avatar
Ngoc LeeNgoc Lee is an Content Creator Manager at EFEX. She wields her long-term expertise in Logistics and Supply Chain, harnessing her top-notch writing and research skills to bring incredibly valuable content. Whether you're a small startup or a well-established enterprise, Ngoc Lee is here to equip you with the essential knowledge of e-commerce, fulfillment, and all things business-related.