In the beginning, it might be hard to justify some of the costs while just getting started. That’s why many businesses turn to inventory management in spreadsheets. However, spreadsheets require a lot of manual work and can only do so much.
Using this article, you’ll learn how to make a barcode in Excel to help you manage your business’ inventory.
Barcodes allow you to represent data in a visual format that can be easily scanned and read by machines You must have seen barcodes hundreds of times on products – from groceries to clothes and everything else,
In this detailed guide, I will explain how you can add barcodes in Excel to represent your product data or inventory lists.
While Excel does not have a built-in feature to generate barcodes, you can easily create them using fonts or formulas. I will show you a couple of easy ways to make barcodes in Excel.
Why Use Barcodes in Excel?
But before I get into the step-by-step instructions, let me quickly explain why you may need barcodes in Excel
Some scenarios where barcodes can be useful in Excel
- Create a product catalog with barcodes for all items
- Generate barcode labels for shipping or inventory tracking
- Print product UPC codes for point-of-sale systems
- Represent product data visually for easy scanning
So if you deal with product inventory or large datasets, barcodes can make it easier to track and manage the records.
The most commonly used barcode fonts are Code 39 and Code 128. I will show you how to use both of these to create barcodes in Excel.
Method 1: Using Barcode Fonts in Excel
The easiest way to generate barcodes in Excel is by using a barcode font.
A barcode font has the barcode images integrated, so all you need to do is format the text as the barcode font and it will show the scannable bars.
Let me take you through the step-by-step process:
Step 1: Download and Install the Barcode Font
First, you need to download a barcode font and install it on your system. Both Code 39 and Code 128 barcode fonts can be easily downloaded for free online.
I would recommend Code 39 as it can encode both numbers and letters (Code 128 only allows numbers).
Go to any font downloading website such as 1001 Fonts and search for “code 39 barcode font”. Download the .ttf file.
Once downloaded, double click on the .ttf file. This will open the font preview. Click on the Install button to install it on your system.
Step 2: Enter the Data in Excel
Now open the Excel file in which you want to create barcodes.
Enter the product code or barcode value in a column. Make sure the cell formatting is ‘Text’ so that Excel does not change the numbers.
To change the format, select the column and go to Home > Number Format > Text.
Step 3: Apply the Barcode Font
Select the cells in which you entered the barcode data.
Go to Home > Font > Code 39
This will change the font and instantly convert the text into barcodes as shown below:
Now you can increase the font size to make it more prominent and readable when printed.
That’s it! The barcode font gives you an easy way to generate barcodes from product codes or IDs in Excel.
The only thing is that this barcode will be lost when you share the file with someone who does not have Code 39 font installed. For a more shareable solution, read on!
Method 2: Using Formulas to Generate Barcode Images
Another way is to use a barcode API that generates an image file for the barcode. You can then insert this image in Excel using a formula.
While slightly complex, the benefit of this method is you can share the Excel file as the barcode rendering is done externally.
Let me take you through the step-by-step process:
Step 1: Enter Product Codes in a Table
Create a table with a list of product codes that you want to create barcodes for.
Make sure to give it a name using Table Design > Table Name. Let’s call it ProductList.
Step 2: Reference the Barcode API
Use this formula in the adjacent column:=CONCATENATE("https://barcode.tec-it.com/barcode.ashx?data=",[@ProductCode])
This combines the product code with the URL of the TEC-IT Barcode Generator API.
For each product code, this generates the direct image URL of the barcode.
Step 3: Convert the Formulas into Images
Select this formula column and change the number format to Image URL.
This converts the API URLs into actual images in Excel.
Now when you share this Excel file, anyone can see the barcodes!
How to Generate Random Barcodes in Excel
Apart from converting a list of product codes into barcodes, you can also use Excel to generate random, unique barcodes.
This can be done using the RANDBETWEEN function.
Follow these steps:
-
Use
=RANDBETWEEN(10000,99999)to generate a random 5 digit number. -
Copy this formula for as many rows as needed.
-
Copy the column and Paste Special Values to convert into static numbers.
-
Use either of the above two methods to transform these random numbers into barcodes.
This gives you an easy way to create any number of unique barcodes in Excel!
Limitations of Excel Barcodes
While Excel provides a quick way to generate barcodes, there are some limitations:
- Limited barcode types – Only simpler 1D barcodes such as Code 39, Code 128, EAN, UPC can be created.
- Static barcodes – Any change to source data will need the barcode to be regenerated.
- Font & API dependency – Barcodes get lost when you remove the font or image API reference.
For more robust enterprise-grade barcode generation, you may need dedicated barcode software or label design tools. But for simple everyday barcode needs, Excel does the job pretty well!
Generating barcodes in Excel is super easy and straightforward.
Whether you use the barcode font method or the barcode API formula, you can create scannable codes for your inventory management and shipping processes.
Barcodes make it easy to visualize data and track items or shipments. And the ability to add them directly within Excel gives you flexibility to integrate barcodes into your business workflows.
How to generate barcodes in Excel?
Another popular method is to use Excel barcode add-ins.
These Excel add-ins and barcode add-ins will generate barcodes using special formulas. And because they don’t require additional fonts or formatted cells, they’re generally straightforward to use.
Finally, there are several online tools that can generate barcodes as s.
This can be useful if you need to print the barcodes or use them in an application that doesn’t support font-based barcodes. Once you’ve generated your barcode, you’ll need to test it to ensure it’s working correctly. The best way to do this is to scan the barcode using barcode scanners.
You can find handheld readers for around $50 or less. Or, if you have a smartphone, several free apps will turn your phone into a barcode reader.
With these methods, you can generate barcodes in Excel for any purpose. Excel makes it easy to get started, whether you’re creating labels for products, tracking inventory, or simply printing barcodes for a fun project. So, try out one of these methods and see how it goes.
Once you have downloaded a font, it’s time to install barcode font in Microsoft, head over to your device’s settings, search for Font settings, and select it. Then, simply head over to your barcode font folder, find the file ending with TTF (TrueType Font), drag and drop it into the add fonts section, and hey presto.
If you’re a Mac user, you need to use the Font Book app.
Spreadsheets are notoriously inefficient
There’s no straightforward solution to quickly generate a lot of barcodes in Excel.
You will need to use an add-in or set up a system yourself to insert barcodes into your Excel spreadsheets. This entails setting up the right fonts in Excel, converting cell format, and getting all your product information into the spreadsheet. This takes quite a bit of time and effort, made worse because Excel spreadsheets are inefficient in data management.
According to studies, 88% of spreadsheets have errors and mistakes in them.
If the reliability of spreadsheets is up in the air, is there an alternative solution for generating barcodes for your manufacturing business?
