Barcodes are most commonly used for inventory management and in point of sale systems to scan and track products. If you work with inventory or product data, you may need to create scannable barcodes for the items you’re tracking. This article will show you how to generate your own barcodes in Excel.
Generate a barcode with a font
Add us to your preferred sources.
Barcodes are most commonly used for inventory management and in point of sale systems to scan and track products. If you work with inventory or product data, you may need to create scannable barcodes for the items you’re tracking. This article will show you how to generate your own barcodes in Excel.
Generate a barcode with a font
Did you know you can 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. So all you have to do to create a barcode is to format your data using the custom font.
Another Barcode Font is a free font you can download from DaFont and use to create working, good-looking barcodes. This custom font comes in a compressed package, so you will need to unzip the file. Then you can double-click the font file ttf which should open the file in the Windows Font Viewer app.
Click the Install button in the Windows Font Viewer app and next time you open Excel, the font will be available on the Home tab.
Now you will be able to format your text with the barcode font to generate your barcodes.
- Select the range of cells to convert to barcodes.
- Go to the Home.
- tab Select the Another Barcode Font option from the font dropdown menu in the Font.
section.
That’s it! Your data will now display 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 in the Font section. The cells will automatically resize to fit the larger font size.
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 Excel cells.
https://barcodeapi.org/api/auto/<data-goes-here>This barcode API will generate a barcode image based on the input data and you can use it with the URL above. Add your data at 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 convert into a barcode in an Excel table.
= "https://barcodeapi.org/api/auto/" & [@[Product Code]]Add the above formula to the table where [@[Product Code]]it refers to the column containing the data to be converted into a barcode. This is the URL that will generate the image in Power BI data types.
Now you can import this data into the Power BI desktop app. Go to the Home tab and click the Excel Workbook.
A file selector will open where you can choose the Excel file with your data.
When you select the Excel file, the Navigator menu will open listing all sheets and tables in the file. Select the table that contains your data and press the Load.
Go to the model view in the Power BI desktop app. Select the table and enable the option Bring to front table in the overview of the Properties.
When you enable the option Is bring to front table, the menu Configure this bring to front table will open and you will need to select the row label and key column for your table.
- The row label is the value that will display in the cell for the data type.
- The Key column must have a unique value in all the rows of the table.
The row label and the key column can be the same column.
Now that your table is set as a featured table, you need to set the URL column to an image type.
Go to the Data view and select the column in the table with the API URL and choose Image URL in the Data category options.
The Power BI dataset is now ready to be published to the Power BI service. Go to the Home tab and click on the Publish command.
The Publish to Power BI menu will appear, where you can select the workspace where the dataset will be published.
Make sure you are logged into Excel with an account in the same tenant as the workspace where you published the Power BI dataset. You can close and reopen Excel if you do not immediately see the new data type in the Data.
tab.
You will now be able to convert any text from the Row Label column into a data type.
- Select the range of cells to convert into a data type.
- Go to the Data.
- tab.
Click the lower right button in the Data Type section of the Data Types section to expand and view all available data types. - Click on the data type you just created in the section From your organization.
This converts the text into the custom data type. You will see a small briefcase icon to the left of the text indicating that it is now a data type. When you select the cells with your data types, you will see a small button at the top right of your selection. This is the extraction button and it allows you to get any column of data from your data type.
Click the buttonExtract and select the column URL . This will extract the barcode image in the adjacent column.
= B2.URLThis way the formula above references the data type cell and the URL is displayed as an image.
This is really interesting because the value in column B contains both the product code text and the barcode image for that product code.
Generate a barcode with the IMAGE function
Using Power BI data types to create a barcode image can be a complex process.
Fortunately, there is a way to get the same image in Excel without needing to use Power BI and set up a data type.
You can use the function IMAGE to create the same barcode.
The function IMAGE takes a URL for an image and returns the image in the cell.
= IMAGE ( "https://barcodeapi.org/api/auto/" & B3 )The above formula will be added to the data in cell B3 at the end of the barcode API. Then in the IMAGE function it will return a barcode image for that data in cell B3.
Conclusion
Barcodes have long been a method for creating scannable codes associated with physical items. You can easily create them in Excel. Using a custom barcode font is the easiest way to generate barcodes; however, these will not display as barcodes when sharing the file with anyone without the font installed.
Power BI data types can be used with an API to create barcode images inside a cell. But you will need a Power BI Pro license to do this. In most cases the best option is probably to generate the barcodes using the function IMAGE.
Pubblicato in Excel
Be the first to comment