Want to create barcodes for your products but don’t know how? Don’t worry in this excel tutorial we will see the whole process how to make standard barcodes in excel which you can use for your products and can scan easily by any barcode scanners.
How to Generate Standard Barcodes in Excel
In order to create standard barcodes we need a special font name “Code 39” which is available on internet and can be downloaded and installed easily (How to install barcode font). With this method you can use numbers as well as letters to generate barcodes. Let’s see the below examples which can help you to understand it better.
Let’s say you have product names and product codes as shown in below image and you want to create barcodes based on product codes which contains numbers and letters both.
First of all make sure the column contains product codes formatted as a text.
How to format Column as a text
- Select the whole column contains product codes
- Right click and select the format cells
- Select text and click ok
Now you need to add a formula where you want to generate barcode. Enter the following formula into cell ref. D3, In this case cell is D3 where we want to show barcode, you might have another cell so adjust it accordingly.
- Formula: =”(“&C3&”)”
C3 is the cell where we have product code, based on product code we are creating barcodes so you have to refer it in the above formula. You can adjust the formula accordingly when you’ll try it to different data.
Once you added the formula, just drag it down to apply on other’s cells as well.
Now just select all the cells where you want to show barcodes, go to fonts and select the font name “IDAutomationHC39M”. Once you selected this font it will convert your product codes into barcodes. These barcodes are fully dynamic, you can even modify the product codes later on. The barcodes will be regenerated automatically. (Watch the below video to understand better).
You might not found this font as it isn’t preinstalled in excel, you need to install it from internet which is free of cost. (Check-out below instructions how to download and install this font)
How to Install Barcode Font in Excel
First go to this Website and download the font from here. Once downloaded you will get a zip file which needs to be extracted, so simply extract that file.
Right click on zip file and just select extract here
After that open the file you extracted “IDAutomation_Code39FreeFont” and click on next
After it choose the location where you want to install this font or let it be the default location and press next.
So once installed, just restart your excel and after it you will find this font in excel font’s list and can be used for barcodes.
How to Generate Random Barcodes
If you want to create barcodes based on random numbers, you can use RANDBETWEEN excel function to get random value between two values. Let’s understand it with some examples.
Let’s suppose you want to create any random numbers which will be used as a product code. All you need is to write formula =RANDBETWEEN(A,B) where A means the lowest value and B the highest. Let’s say you want the random values between 5,000 to 10,000 so you will write the formula =RANDBETWEEN(5000,10000).
Enter this formula in cell C3 and drag it down to apply on other cells as well.
As you can see in the above video the random barcodes has been generated easily. So this is how you can easily create barcodes for your products in excel.
I hope you will find this article useful, if you have any question in this regard do let me know in comments section. I would happy to assist you any further.
You might be interested to learn more:
could you customize a proforma and commercial invoice for our company