STEM

Generate Bar Codes, QR Codes in Excel: Quick & Easy Way

In this post, I’ll show you a quick, easy, and a free method that you can use today to generate QR codes, bar codes in UPC-A, UPC-E formats, and custom bar codes based on the product information you enter in Excel as text.

How it works

Two things are at play here that make it possible: Excel, and a free, open-source Web API. We have to use them both together to generate the codes. Once the codes are generated, you can even verify them by scanning them using a code scanning phone app.

Excel gives us IMAGE function that has the ability to bring in an image from virtually any source. Fortunately, the source can even be a web URI. So, if we find an end-point of a web service that can take text and return the code image, then we can accomplish this. And that’s what the barcodepai.org provides—a web service with a specific end point depending on the type of bar code we want. The complete documentation of the API is available here. For the simplest and common uses, we can use https://barcodeapi.org/api/auto/ and pass the textual part of the code such as product code, UPC code of different formats, or a website URL by appending the string to the end part “…./auto/”. Let’s look at some real examples.

Here, I have some items and their codes in various formats. The ‘Product Code’ contain custom codes, where UPC-A, UPC-E columns contain valid UPC symbols in -A, and -E formats. The ‘Website URL’ contains the web site address you want to display as a QR Code.

Next, all we have to do simply refer to the codes we want from the desired column and pass it on to the API as a parameter. Once we do that, the generated codes in different formats would be returned as shown below.

Generated QR Codes

Generated UPC-A and UPC-E codes

Generated custom product codes

The solution is extremely simple. All you have to do is put in the formula in a destination in this format: IMAGE("https://barcodeapi.org/api/auto/" & cell_ref) where cell_ref contains the code from your table or range for which you want the generated code.

The “auto” directive prompts the end point to decide on its own the most relevant format of code that it should return based on the parameter you passed. For example, as soon as it sees web URL passsed as the parameter, it’ll generate a QR Code when “auto” is set. However, you can also specify or force a specific type of code generation by specifying which format you like. That’s how the UPC-A and UPC-E were generated above. For those, I used: IMAGE("https://barcodeapi.org/api/upca/" & cell_ref) and IMAGE("https://barcodeapi.org/api/upca/" & cell_ref) for -A and -E formats respectively.

When these codes are scanned by a proper smartphone app or device, it’ll show the description as text after decoding the images (e.g. website mentioned in the Website URL column, or the product number, or other numbers specificed as per the format requirements for the code).
Hope you enjoyed reading this tip!

Leave a Reply

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

Back To Top