People who track stock, sales, and purchases in Excel often ask me “how can we generate a barcode in Excel?”. Well, in Excel there is no default option to generate a barcode.
And, the bad news is: There is no plan of Microsoft to add any option like this. But here is the good news: You can generate a barcode in Excel by installing a font.
So today, just for you, I’d like to reveal the method to generate a barcode in Excel by using 39 barcodes. So let’s get started.
What is Code 39?
Code 39 is known as Code 3 of 9 which is the most used barcode and able to scan by every barcode scanner. In this code, every single character has 5 bars and 4 spaces in total 9 characters. Out of total 9 characters, 3 characters are always wide. It can encode 26 uppercase letters, 10 digits and 7 special characters.
Quick Note: It’s one of those Excel Tricks that can help to get better at Basic Excel Skills
Steps to Add a Barcode Font/Generate in Excel
Just follow these simple steps.
- First of all, you have to download this free barcode font from idautomation.
- Once you download this font, next you need to install it on your system.
Important Note: This is a free version of the font. You can only use it for educational purpose.
- Now, from your worksheet, select the cell in which you want to create a barcode.
- Enter the value for which you want to create the barcode.
- Just change the font style from your normal font style to “IDAutomationHC39M Free Version”.
As soon as you change your font style text will convert into a barcode.
This font style will simply convert your text into 3 of 9 barcodes. And, you can use it anywhere you want.
Steps to Create a Barcode in Word
You can create a 3 of 9 barcode in word as well by using following steps.
- Once you install the font in your system, you can call it from any of your office app.
- Just type the text for which you want to create a code.
- Select that text and change the font style to “IDAutomationHC39M Free Version”.
Conclusion
That’s the whole story about barcode. And, you know this is one of the easiest ways to create barcode in Excel/Word.
You can generate as well as print these barcodes and you can also create a separate template for this as well. I hope this tip will help you track your stock effectively.
More Tutorials
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
wwww
hi,
i don’t know how to install Barcode Font? would you mind to explain for me?
Thank you very much for the information, this is a great help for work.
PDA scanner is a tool specially used for barcode scanning, which can provides strong support for enterprise digital operation and big data analysis which promotes more efficient management and higher productivity.
Great Article!
I appreciate you.
Thanks for sharing.
Thank YOU
Welcome.
You seen like a really smart guy.
I would like to be able to use my scanner and scan items into Excel and have its barcode in a column right next to it. The problem is that there are different formats like ean upc/a 39 and more.
I want Excel to recognize the data I got with my scanner and make the displayed barcode in Excel to be exactly like that on the box I scanned.
Do you have any ideas?
Thank you.
how to create date barcode label?
eg,the label cant be read if i type in 1/1/2019.
Just saw your query post from the beginning of the year (by accident than deliberate looking). Hope this comment gets back to you. I’ve been a bit verbose but hopefully it will explain what I think your question is asking. Here goes …….
You have to be careful which date format you pick for dates when using them in a barcode.
If A1 contains your date, either using a formula or merely entered as a date, and you need to choose a suitable format for excel to show that date. But Excel stores your date as a number.
Possible Issue #1:
——————–
When using barcode (at least ‘3 of 9’) you need to start and end it with an asterisk (*) character. If you don’t do this, the barcode won’t work. So when you assign a cell a value which you set the font to “FREE 3 of 9″ barcode font (or the extended version), the formula you would have is –
=”*” & A1 & “*”.
Possible Issue #2:
——————–
For dates, the above formula results in output like *43466* instead of *1/1/2019*.
The way get the date format into the barcode is to use the TEXT function to show the date in the format you want.
So for this example, you would need to use the formula –
=”*” & TEXT( A1 , “dd/mm/yyyy” ) & “*”
Bonus comment about displaying (or barcoding) dates:
—————————————————————-
You can of course choose whatever date format you wish. I usually avoid any date format with “dd/mm” in it because when people see a date is it “dd/mm” or “mm/dd” you’re looking at? Unless dd is > 12 I cannot be sure if it’s showing me e.g. 1st of April or 4th of January unless I see one of the dates in the column (which could be many many rows long, most being off screen) meeting this condition.
So I usually use “ddMMMyy” or similar form, ie displaying the month portion in alpha form e.g. “Apr” rather than “4” or “04” e.g. –
=”*” & TEXT( A1 , “dd-MMM-yyyy” ) & “*”
Fortunately the limited special characters supported by “3 of 9” includes the dash (-) and slash (/) characters, so dates should barcode nicely.
Free font from idautomation webiste has been discontinued…..
very interestingand valuable post.
If you use the font at URL https://www.barcodesinc.com/free-barcode-font/ then it’s totally free, not just “for educational use only”.
The barcode is just the latest in your long line of useful posts. Just as a heads up for other readers, none of the installation schemes that involved the Windows Control Panel (including the one on the ID Automation site) would
work for me. Finally, noticing that the installation file had an .exe extension, I double-clicked it and it installed the font with a couple of brief clicks.