Do you want to make a barcode in Excel?

A barcode is a representation of data using parallel lines that vary in width and spacing.

Barcodes are most commonly used for inventory management and point-of-sale systems to scan and track products.

If you work with inventory or product data you might need to create scannable barcodes for the items you track.

This post will show you how you can generate your own barcodes in Excel.

Generate a Barcode with a Font

Did you know that you can use install and use new fonts for your Excel spreadsheets? Yes, you can and this is one of the best ways to customize your workbooks.

One way to create a barcode in Excel is to use a custom barcode font.

Then all you need to do to make a barcode is format your data using the custom font.

Another Barcode Font is a free font you can download from DaFont and use to create working barcodes that look nice.

This custom font comes in a zipped package, so you will need to unzip the file. Then you can double click on the ttf font file which should open the file in the Windows Font Viewer app.

Click on the Install button in the Windows Font Viewer app and the next time you open Excel, the font will be available in the Home tab.

Now you will be able to format your text with the barcode font to generate your barcodes.

1. Select the range of cells to convert to barcodes.
2. Go to the Home tab.
3. Select the Another barcode font option from the font dropdown in the Font section.

That’s it! Your data will be displayed as a barcode.

You will need to adjust the font size so the barcode is visible. Select a larger font size such as 48 from the dropdown menu in the Font section. The cells will automatically resize to accomodate the larger font size.

📝 Note: This doesn’t change the underlying value in the cells. It will only display the vertical lines for the barcode above the characters.

Generate a Barcode with Organization Data Types

An interesting method to create barcodes from your data is to use a barcode API to generate an image of the barcode.

You can use Power BI data types to get this image inside your Excel cells.

https://barcodeapi.org/api/auto/<data-goes-here>

This barcode API will generate a barcode image based on your data input and you can use it with the above URL. Append your data on the end in place of the <data-goes-here> part and the URL will generate an image for that data.

Add the data you want to transform to a barcode in an Excel table.

= "https://barcodeapi.org/api/auto/" & [@[Product Code]]

Add the above formula to table where [@[Product Code]] refers to the column containing the data to turn to a barcode. This is the URL that will generate the image in your Power BI data types.

Now you will be able to import this data into the Power BI desktop app. Go to the Home tab and click on the Excel Workbook command.

This will open a file picker where you can select the Excel file with your data.

When you select your Excel file, this will open up the Navigator menu which lists all the sheets and tables in the the file. Select the table that contains your data and press the Load button.

Now go to the Model View in the Power BI desktop app. Select the table and toggle on the Is featured table option in the Properties window pan.

When you toggle on the Is featured table option it will open the Set up this featured table menu and you’ll need to select the Row label and Key column for your table.

• The Row label is the value that will display in cell for your data type.
• The Key column should be a unique value across all the rows in the table.

The Row label and Key column can be the same column.

Now that your table is set up as a featured table, you will need to set the URL column to an image type.

Go to the Data View and select the column in your table with the API URL and choose Image URL in the Data category options.

Your Power BI dataset is now ready to publish to the Power BI service. Go to the Home tab and click on the Publish command.

This will open the Publish to Power BI menu where you will be able to select the workspace to which the dataset will be published.

📝 Note: Publishing to My workspace won’t allow you to access the dataset as a data type in Excel. Choose any other workspace you have access to.

Make sure you are signed into Excel with an account in the same tenant as the workspace where you published the Power BI data set. You can close and reopen Excel if you don’t immediately see the new data type in the Data tab.

Now you will be able to convert any text from the Row label column to a data type.

1. Select the range of cells to convert to a data type.
2. Go to the Data tab.
3. Click on the button in the lower right of the Data Type tray in the Data Types section to expand and see all the available data types.
4. Click on the newly created data type in the From your organization section.

Thi converts the text to your custom data type. You will see a small briefcase icon to the left of the text to indicate it’s now a data type.

When you select the cells with your data types, you will see a small button on the top right of your selection. This is the extraction button andn it allows you to get any column of data from your data type.

Click on the Extract button and select the URL column. This will extract the barcode image into the adjacent column.

= B2.URL

This places the above dot formula referencing the data type cell and the URL displays as an image.

This is really cool because the value in column B contains both the product code text and the image of the barcode for that product code.

Generate a Barcode with the IMAGE Function

Using the Power BI data types to create a barcode image can be a complex process.

Thanksfully, there is a way to get the same image into Excel without the need to use Power BI and set up a data type.

You can use the IMAGE function to create same barcode.

The IMAGE fucntion takes a URL for an image and returns image in the cell.

= IMAGE ( "https://barcodeapi.org/api/auto/" & B3 )

The above formula will append on the data in cell B3 to the end of the barcode API. Then in the IMAGE function will return an image of a barcode for that data in cell B3.

Conclusions

Barcodes have been a long standing way to create scannable codes associated with physical items. You can easily make them in Excel.

Using a custom barcode code font is the easiest method to generate barcodes, however they won’t show as barcodes when you share the file to anyone without the font installed.

Power BI data types can be used with an API to create bar code images inside a cell. But you will require a Power BI pro license to get it done.

The best option in most cases is likely to generate your barcodes using the IMAGE function.

Did you know you could make barcodes in Excel? Let me know in the comments section below!