Inventory management is among the most tedious work. Luckily, you can integrate barcodes to help track and manage your inventory. Tracking products manually is time-consuming and may cost you extra money to hire labor.
Excel is the best office tool to store and manage inventory. Combining these two technologies make the whole process seamless and efficient. In this guide, I will show you how to create barcodes in Excel.
Method 1: Using Barcode font
Excel allows users to add new custom fonts. If you have text or numbers that you would wish to convert into barcodes, then you can take advantage of this method.
You can generate a bar code from text code or a combination of text and numbers.
Assuming our products have the following codes.
ST6785936420
457XYZ346975
TVR875639741
Follow these steps
1. Search for Barcode font on Google and open the website of your choice. Alternatively, you can launch open the following website.
2. Download the barcode font (It will download as a zip file. Therefore, you need to unzip it)
3. Double-click on any file that has a .ttf extension
4. Click on the install button
5. Close and open Excel again for the installation to work correctly.
6. Select or highlight the cells you wish to convert to barcodes
7. Change the font to Barcode font-39
8. The font will automatically convert the product codes to bar codes
How to create barcodes with text underneath in Excel
In the method that we have discussed above, we have seen how to convert text to barcodes. But what if you want more advanced functionality? For instance, code underneath as it is with many products in the supermarkets. To achieve this, we will use another font called Dafont.
Assuming we have the same products as above. Download the font from the Dafont website. Repeat the same procedure as above to unzip and install.
Highlight the product codes and convert them into barcodes by changing the font.
As you can see from the image above, we have an official-looking output. You can use this to manage your inventory by scanning using a barcode reader or your smartphone.
Method 2: Using Addins
Excel addins come in handy when it comes to enhancing existing features or adding new functionality. Therefore, you can use them to generate bulk QR codes for your products.
1. Download and install Kutools addin (Search on Google for the addin to get the latest version)
2. Open the worksheet containing your product codes
3. Navigate to the Kutools Tab and Click on Insert>Insert Barcode
4. A pane will launch on the right side. You can use this pane to set and customize your barcode. For example
Choose the type of barcode that you wish to insert. Kutools offers different types, which include
Code-128
EAN-8
UPC-E
UPC-A
Etc…
In the second section, select the range containing the product codes
Then choose the target range in which you wish the barcodes to appear
Lastly, choose the validation type and set the size of the barcode. Preview the final results, then click on Generate.
Remember to tick the show barcode number to show the text or numbers underneath the barcode.
All the product codes will be converted to bar codes. This method is ideal even for bulk products.
Method 3: Using the image function
Microsoft 365 and Excel 2021 have a new function called image (). This allows users to embed dynamic images in Excel. You can use this function to generate bulk QR codes. The function relies on an API. First, it sends the data on the data cell to the API and returns an image result in the target cell.
1. Open the worksheet containing the product codes you wish to convert into barcodes
2. Copy and paste the following formula on the target cell where you want it to appear
=IMAGE(“https://barcodeapi.org/api/auto/”&B6)
3. Press enters, and a barcode will automatically be generated.
4. You can apply the same formula on other cells by double-clicking on the fill handle or dragging it down to the end.
In the image above, you can see that the image function has produced unique barcodes. Additionally, it has also embedded text underneath.
Download Practice Sheet
Frequently Asked Questions
1. What is the formula to create a barcode in Excel?
The formula to create barcode in Excel is =IMAGE(“https://barcodeapi.org/api/auto/”&B6)
This formula uses API to automatically generate barcodes based on cell values in Excel.
2. Can Excel display barcodes?
Excel can display all types of barcodes, for example, EAN, UPC, Code 39, and Code 128.
3. How do I add a barcode to a cell in Excel?
You can add a barcode by using an add-in or the Image function. Alternatively, you can use an online generator and then embed the image into your preferred cell.
4. How do I convert text to barcode?
Easy, just use an Excel barcode font. All you need to do is install the font, then select the text and change the font to a barcode font.
5. How to generate random barcodes in Excel?
First, generate random numbers by taking advantage of the Randbetween() function. Then use a font, add-in, or image function to convert to barcodes.
Final Thoughts
Barcodes are one of the most used features in wholesale, retail, and all commerce-related business. They help to track individual products and prices. A business owner can quickly pull barcodes to determine how much inventory is in stock. With this information, they can quickly manage, budget, and prepare for restocking. It can also be used in e-commerce to track the shipping process. Excel makes the whole process easier as you can generate and store bulk Barcodes.
Similar Reads
How to Generate Serial numbers in Excel Automatically: Easy Guide
