Looking to manage your inventory more efficiently? Generating barcodes in Microsoft Excel can be a game-changer for inventory management, making it easier to keep track of products. Whether you manually assign item numbers to your products or let Excel do it for you, this guide will show you how to quickly create barcodes using the Code 39 barcode font.

This post will take you through the steps of formatting item numbers, installing a barcode font, and generating both static and random barcodes for your products in Excel.

Watch the video below for a step-by-step walkthrough before diving into the instructions!

Why Create Barcodes in Excel?

If you’re already using Microsoft Excel as your inventory management system, barcode generation can be a huge time-saver. Whether you have product item numbers or you need Excel to randomly generate them, you’ll be able to quickly create product barcodes that can be scanned with a handheld scanner or a mobile app. Here’s how!

Step 1: Check and Format Your Item Numbers

Before creating barcodes, you need to ensure your item numbers are formatted correctly. Excel, by default, uses General format for cells, which may strip leading zeros or reformat long numbers. This can mess up your barcodes.

Here’s how to fix it:

  1. Select the item number column in your Excel sheet.
  2. Go to the format drop-down (above where the font selection is) and choose Text. This will ensure Microsoft Excel keeps any leading zeros and doesn’t switch to scientific notation for longer numbers.

By doing this, your zeros won’t get dropped, and your item numbers won’t be incorrectly formatted.

Step 2: Install Barcode Font

You need a special font to display barcodes in Microsoft Excel. One of the most popular is the Code 39 Barcode Font.

Here’s how you can install it:

  1. Download the barcode font from a trusted source. Melissa uses ID Automation for her fonts, and you can find the Code 39 Barcode Font easily.
  2. Unzip the downloaded file and locate the TrueType Font (ttf) file.
  3. Double-click on the file and choose Install. If prompted, let the system replace any existing version of the font.

Once installed, make sure to restart Excel so that the new barcode font becomes available.

RELATED TOPIC: How to Use the IF Function in Excel

Step 3: Create Barcodes Using a Formula

Now that everything’s set up, it’s time to create the barcodes in Microsoft Excel. Here’s the formula you need to use to generate readable barcodes in Excel.

  1. In the cell where you want the barcode to appear, enter the following formula:
="(" & [ItemNumberCell] & ")"

Replace [ItemNumberCell] with the actual cell reference for your item number. For example, if your item number is located in cell B2, the formula would look like this:

="(" & B2 & ")"

The parentheses are used like “start” and “stop” indicators for the barcode scanner.

  1. After entering the formula, hit Enter. Microsoft Excel will give you the barcode in raw text form.
  2. Now, simply go to the Font dropdown, find your installed ID Automation font, and apply it to the formula cell. This will turn the text into a scannable barcode.

Step 4: Generate Random Item Numbers

If you have new products without item numbers, Excel can randomly generate them for you. Here’s how you can generate random barcodes:

  1. Select the cell where you want the random item number to appear.
  2. Use the formula:
=RANDBETWEEN([LowerLimit], [UpperLimit])

For example, to generate random item numbers between 100,000 and 500,000, the formula would look like:

=RANDBETWEEN(100000, 500000)
  1. Hit Enter, and Excel will randomly generate an item number.
  2. Apply the same barcode formula from Step 3 to turn the random item number into a barcode. Then, drag the formula down for additional random barcodes.

RELATED TOPIC: How to Perform Basic Calculations in Word

Step 5: Apply to Your Entire List

Want to generate barcodes for multiple items at once? Follow these steps:

  1. Once you’ve created your first barcode, simply double-click the bottom-right corner of the cell with the formula. Excel will auto-fill the remaining cells in the column, generating barcodes for all your items.
  2. To update the font for the entire column in Microsoft Excel, highlight all the cells with the barcodes and apply the Code 39 Barcode Font.

Extra Tips

  • Check Font Compatibility: If you’re working in an office or corporate environment, ensure that you are allowed to install fonts. Your IT department may have restrictions in place.
  • Save Your Work: Always save your Excel document after formatting and generating barcodes, especially if you’re working with random item numbers, so nothing is accidentally regenerated.

Conclusion

Generating barcodes in Microsoft Excel is a powerful way to streamline your inventory management. Whether you’re working with existing product numbers or need Excel to generate random item numbers, these steps make it easy to create scannable barcodes. Remember, once set up, you have an efficient system that can grow with your inventory.

Found this helpful? Be sure to give the video a thumbs up and subscribe for more content like this! Check out my website for written instructions and more Excel tutorials.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR