How to make a barcode generator in Excel?

Download the ultimate guide to inventory management
A comprehensive ebook with everything you need to know about inventory management.

It can cost a pretty penny to get your business up and running.

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.

So, is there a way to introduce automation into your business while using Excel spreadsheets?

Using this article, you’ll learn how to make a barcode in Excel to help you manage your business’ inventory.

What is a barcode in Excel?

Closeup of a screen listing Microsoft Office apps

A barcode is a machine-readable code that is used to store data.

Barcodes can be found on product packages in supermarkets, on book covers, and on identification cards. You can read barcodes by scanning them with a barcode reader, which converts the code into readable information stored in a database. Barcodes have been around since the early 1970s, and their use has become increasingly common as businesses look for ways to speed up transactions and track inventory.

So, want to know how to create a barcode inventory system in Excel?

Microsoft Excel is a spreadsheet program that is often used for data entry and analysis.

You can set up an Excel barcode generator system using barcode fonts. But, when you create a barcode in Excel, you must change the font to one that supports barcodes. There are many free and commercial fonts available that support barcodes.

Once you have selected and changed the font, you can enter data into the cell content in MS Excel, and the barcode will be generated automatically.  

Barcodes are a convenient way to store data, and they can be used for various purposes. You can use barcodes to track inventory, speed up transactions, or simply store data. Creating barcodes in Excel is a simple process, and there are many fonts available that support barcodes. 

With a little planning, you can easily create barcodes that meet your needs.  

Different types of barcodes

1D barcodes: 

  • EAN-13 and EAN-8  
  • UPC-A and UPC-E  
  • Code128  
  • ITF-14  
  • Code39 

2D barcodes:  

  • QR code 
  • Aztec code 
  • Data matrix  
  • PDF417  
  • MaxiCode  
  • GS1 Composite code 

Do you need a beefier system with more potential for manufacturing process automation and inventory management? Be sure to check out the best inventory management software systems. 

How to generate barcodes in Excel?

White barcode on a black background

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 images.

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.

How to install barcode fonts to Excel on Windows?

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.

How to create barcodes in Excel manually?

Closeup of Excel cells with "9934" typed in to one cell and a barcode generated after

First, you need to find a barcode font to use. As already mentioned, many options are available online, from free to paid.

Though, you should take the time to understand the type of barcodes you need before committing. Some barcodes are better suited for certain tasks than others, as the amount of information they can store will differ. However, if the barcodes are going to be used internally, then the 3 of 9 barcode font will suffice. 

Once you have found a barcode font, you can get started.

Barcode generator Excel can be printed on labels, tags, or directly on products themselves.

1. In a blank spreadsheet, create two columns

To keep it simple, you can start with two columns labeled Text and Barcode.

Create as many columns as your heart desires if you want to add descriptions and other information.

How to create a barcode in Excel? To generate barcodes in Excel, you must download and install a barcode font.

2. Format the cell for the Text column

Now you need to format the cells to ensure that larger numbers won’t be displayed in a scientific notation and that you can retain any leading zeros.

So, highlight the Text column, right-click and select Format Cells.

How to create a barcode inventory system in Excel

A menu will appear with options for formatting your cells.

Simply select Number from the top of the menu and Text from the sub-menu. If you input data with leading zeros, it won’t automatically be converted into something else.

To create a barcode generator Excel, type the data you want to encode into a cell in Excel and then format it as a barcode using the barcode font.

3. Inputting barcode formula in Excel

Now the exciting part ⁠— imputing the formula into the Barcode column.

Enter the formula as follows:

=”*”&A2&”*”

Adjust the Excel formulas accordingly if you’re not using the Text in the A column. Once you have done that, you can click the cell, which will now be populated with ** and select the bottom right of the highlighted cell, drag it down to populate the rest of the barcode column.

How to create a barcode in Excel? Barcodes can be scanned using several devices, including scanners and smartphone apps.

4. Changing the font

Highlight the Barcode column cells with ** in the field.

Head to the font menu and select your barcode font. Remember, Excel does not have barcode fonts installed, so you will need to download and install a version, either paid or free.

How to create a barcode inventory system in Excel? When scanning a barcode, the encoded data will be displayed on the screen of the device.

5. Go to town and start generating barcodes

If you’re using 3 of 9 barcode fonts, as we suggested earlier, it can read letters, numbers, and some special characters.

And that’s it.

After you’ve finished the download and installation, you can generate rudimentary barcodes with Excel. Great if you’re a micro-business owner and manufacturer looking into automating inventory tracking. However, it’s not the best solution if an SMB company needs barcodes for purchase management and registered barcodes.

Generating random barcodes in Excel

Excel also offers several other features that can be used with barcode generator Excel, such as formulas and conditional formatting.

The RANDBETWEEN function is a built-in function in Excel that returns a random number between two numbers you’ve inputted.

You specify the two numbers in the function’s arguments. The RANDBETWEEN function is a great way to generate random numbers or dates in Excel. You can use it to generate random numbers for games, simulations, or other purposes, such as generating random barcodes:

  1. Start by opening a new Excel spreadsheet 
  2. In cell A1, enter the following formula: =RANDBETWEEN(1,999999999) 
  3. Press Enter. This will generate a random number between 1 and 999999999
  4. To create additional random numbers, copy and paste the formula in Step 2 into other cells
  5. Once you have a column of random numbers, you can use a barcode generator Excel tool to create barcodes for each number. You’ll be able to find free tools for that online.
  6. Simply enter each number into your barcode generator Excel, and it will create a barcode that you can save and print 

Use the RANDBETWEEN Excel function for generating random barcodes in Excel quickly. This is a helpful function for creating large quantities of barcodes. Simply follow the steps above to get started.

Benefits of creating barcodes in Excel

How to create a barcode in Excel? You can print barcodes in Excel using special barcode fonts.

There are many benefits to creating barcodes in Excel.  

Perhaps the most obvious is that it is cost-effective to create barcodes. Additionally, barcodes created in Excel can be easily customized and adjusted to meet the specific needs of your business. Finally, Excel barcodes can be quickly and easily read by barcode scanners, making them ideal for inventory management or other data-tracking applications.  

Disadvantages of creating barcodes in Excel

Employee at the wood workshop using Katana's software running on a laptop

While creating barcodes in Excel will work well for many, doing so may not be scalable.   

Using a dedicated barcode generator Excel tool may be more efficient if you need to generate many barcodes. Additionally, some barcode generator Excel tools can create codes that cannot be created using Excel alone.  

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?

Katana’s cloud inventory software for generating barcodes

Katana’s cloud inventory software allows businesses to manage their sales, production, and fulfillment from one place.

Katana has features to help you manage and create barcodes without using an Excel barcode generator. This allows companies to easily automate their purchase orders and inventory control with a point and a click. So, not only will you have fewer errors in your workflow and make your business more efficient, but using Katana’s barcodes allows you to:

Katana has the capabilities of different barcodes, such as supplier item codes, barcodes for internal use, or registered GS1 barcodes.   

Using Excel for barcodes can be great at first, but as your business grows and you have to handle more sales and manufacturing orders — nothing beats the real thing.

Want to see it all in action? Request a demo today and see how you can start using barcodes in your business operations.

Get a demo and discover the power of real-time insights