Do you want to create a barcode in Excel? A barcode is a representation of data using parallel lines that vary in width and spacing. Let’s see how to do it.
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 might need to create scannable barcodes for the items you track. This article will show you how to generate your own barcodes in Excel.
Generate a Barcode with a Font
Did you know that 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. Then all you need 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 and good-looking barcodes. This custom font comes in a compressed package, so you’ll need to unzip the file. Then you can double-click the font file ttf which should open the file in the Windows Font Viewer.
Click the Install button in the Windows Font Viewer and the next time you open Excel, the font will be available in the Home
Now you’ll be able to format your text with the barcode font to generate your barcodes.
- Select the range of cells you want to convert into barcodes.
- Go to the Home.
- Select the Another Barcode Font option from the font dropdown menu in the Font.
That’s it! Your data will be displayed as a barcode.
You’ll need to adjust the font size so the barcode is visible. Select a larger font size, like 48, from the font dropdown menu 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 for creating 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 within your Excel cells.
https://barcodeapi.org/api/auto/<data-goes-here>This Barcode API will generate a barcode image based on the data you enter, and you can use it with the URL above. Add your data to 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 turn into a barcode in an Excel table.
= "https://barcodeapi.org/api/auto/" & [@[Product Code]]Add the formula above to the table where [@[Product Code]]refers to the column containing the data to be turned into a barcode. This is the URL that will generate the image in Power BI data types.
You will now be able to import this data into the Power BI desktop app. Go to the Home tab and click on the Excel workbook command.
A file picker will open where you can select the Excel file with your data.
When you select the Excel file, the Navigator menu will open, which lists all the sheets and tables in the file. Select the table containing your data and press the Load button.
Go to the model view in the Power BI desktop app. Select the table and turn on the Table in foreground option in the Properties pane.
When you turn on the Is table in foreground option, the Configure this table in foreground menu will open, and you will need to select the row label and the key column for your table.
- The row label is the value that will be displayed in the cell for the data type.
- The Key columnmust have a unique value in all table rows.
The row label and the key column can be the same column.
Now that your table is set up as a featured table, you will want 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.
Your Power BI dataset is now ready to be published to the Power BI service. Go to the Home tab and click the Publish command.
Publish to Power BI menu will appear where you can select the workspace in which the dataset will be published.
Make sure you are signed into Excel with an account in the same tenant as the workspace to which you are publishing the Power BI dataset. You can close and reopen Excel if you don’t see the new data type in the Data.
tab immediately.
Now you will be able to convert any text from the Row label column into a data type.
Select the range of cells that you want to convert into a data type.
- Go to the
- Data tab. Click the button at the bottom right in the .
- Data type section in the Data Types section to expand and see all available data types..
- Click on the data type you just created in the 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 to indicate 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 extract button and it allows you to pull any data columns from your data type.
Click on the Extract button and select the column URL. This will extract the barcode image into the adjacent column.
= B2.URLThis makes the formula from the point above reference the data type cell and the URL will be displayed as an image.
This is really cool 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.
Luckily, 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 IMAGE function to create the same barcode.
The IMAGE function takes a URL for an image and returns the image in the cell.
= IMAGE ( "https://barcodeapi.org/api/auto/" & B3 )The above formula will append to the data in cell B3 at the end of the barcode API. So in the IMAGE function, an image of a barcode for that data in cell B3.
will be returned.Conclusion
Barcodes have long been a method of creating scannable codes associated with physical items. You can easily create them in Excel. Using a custom barcode font is the easiest method to generate barcodes, however these will not display as barcodes when you share the file with anyone without the font installed.
Power BI data types can be used with an API to create barcode images within a cell. However, 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 IMAGE.
Pubblicato in Excel
Be the first to comment