How to Auto-Generate a Barcode in Excel [Tutorial]

There’s nothing more versatile than a spreadsheet. From managing your business’s finances to scheduling the content you’ll use on your blog, spreadsheets can do it all. But did you know that you can use your Excel spreadsheet to generate barcodes? 

This makes it ideal for inventory management and your budget. So without further ado, it’s time to show you how to auto-generate a barcode in Excel!

Auto-Generating a Barcode in Excel

Your graphic designer was right – fonts do matter. 

Before diving into Excel, we’ll need to find a font that we can use to generate our barcode. There are several of these fonts available online, and the one you use will depend on what you want to use your barcodes for.

In this example, though, we’ll use the 3 of 9 barcode font that you can download here

Once we’ve downloaded the font, we’ll install it on our system to make it visible in Excel. 

Step 1: Set Up Your Spreadsheet to Auto-Generate Barcodes in Excel

We’ll open a new Excel workbook and create two columns, one for Text and the other for Barcode

You can create as many columns as you need to add descriptions and other information to every row. 

how to format columns in excel to auto generate barcodes

Step 2: Formatting the Text Column

We’ll need to format the cells in the Text column to make sure we keep any leading zeros in our numbers. This formatting also prevents large numbers from displaying in scientific notation. 

To format the Text column, we’ll select it in the top bar, right-click, and select Format Cells

In the dialog box that opens, we’ll select Number in the top bar and Text as our Category

how to format excel spreadsheets to auto generate barcodes

Step 3: Creating Your Formulas

With our Text column formatted, we’ll also need to input the formula that will copy the information from our Text column over to our Barcode column. 

Here, we’ll use =”*”&B3&”*” as our formula. 

(We’re using B3 in our formula because we used the B column as our Text column. So, your mileage may vary.)

formula for automatically generating barcodes in excel

Once we’ve entered the formula, our cell will now display **.

formula for auto-generating excel barcodes

We can now select the bottom-right of the cell and drag it down to copy our formula to the cells below. 

how to create barcodes in excel

Step 4: How to Auto-Generate Your Barcodes in Excel

Once we’ve copied the formula to a few cells below, we can select all these cells and change the font to the 3 of 9 Barcode font we downloaded and installed earlier. This will then show a barcode in every cell. 

generate barcodes in excel spreadsheets

With the font changed, we can now enter numbers or text into our Text column. 

As we enter the information, the Barcode column will automatically display its barcode. 

how to generate barcodes in excel spreadsheets

Other Options for Generating Barcodes in Excel

Even though this method works, it still requires a fair amount of manual work before you can generate barcodes. 

You can use the RANDBETWEEN function to eliminate some of the manual work. This function generates a random number between two numbers. This is the ideal option if, for instance, you need to generate many SKUs.  

Another option would be to use one of the many available Office Add-ins that can generate barcodes for you from Excel data. 

Make Your Spreadsheets Work for You

Once you’ve created your data in your spreadsheet, you can get the most value from it by using Our platform allows you to turn your spreadsheets into REST APIs in a matter of seconds. 

Turn a spreadsheet into a REST API

You’ll be able to use your spreadsheets everywhere, from mobile apps to your own custom inventory management system. 

To try out for yourself and see what difference it can make in your business, get started for free today

Leave a Reply

Your email address will not be published. Required fields are marked *