Hi ,

Using Excel as a QR Code Generator is quick, easy, and free.

20 years after the first scanning of the UPC barcode, its successor, the 2D matrix barcode was invented – what we now know as the QR Code.

QR codes are used for everything – marketing campaigns, contactless payments, business cards, and restaurant menus because they can store more information and offer faster recognition, hence the name “Quick Response” or “QR”.

Prefer to read this on our website? 

Then click this link >>> QR Codes in Excel

Video: QR Codes in Excel

Watch on YouTube Subscribe 573K

Excel QR Codes Using IMAGE Function

If you use Microsoft 365, you don’t need any add-in to generate a QR Code in Excel. Excel’s IMAGE function offers everything you need, right in the Excel grid!

The IMAGE function syntax is:

=IMAGE(source, [alt_text], [sizing], [height], [width])

And for the source argument, all you need is Google’s QR Code Generator API

https://chart.googleapis.com/chart?chs=100x100&cht=qr&chl=

And the alphanumeric string you want encoded.

Let me show how:

Here’s a list of some of my Excel courses with their URLs. I want to generate the QR Code for these courses containing the course URLs.

As I have the first course’ URL in cell C4, my image function will look like this:

=IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&C4)

Using this function, you can quickly generate your QR Codes.

However, I also want to add an alt_text to my QR Codes, which I can do with the second argument in the IMAGE function.

And while you can change their size using the custom sizing arguments of the IMAGE function, I don’t recommend it as it may result in less crisp images.

Thus, my final IMAGE function will look like this:

=IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&C4,C4,2)

Where the last argument is 2 to return a QR code in its original size:

This will generate my first in-cell QR code.

And I can simply copy the formula down and have QR codes generated for all my courses in no time.

Tip: You will need to adjust the cell’s height and width to make the QR code properly visible in the cell.

Benefits:

1. Simplicity: The IMAGE function is simple to use
2. Enables bulk QR code creation: You can create multiple QR codes in seconds

Limitations:

1. Accessibility: The IMAGE function is only available in Microsoft 365
2. No backward compatibility: There’s no backward compatibility of the IMAGE function with the older Excel versions
3. Prone to accidental edits: Editing the IMAGE function incorrectly can break it and not generate QR codes.

Excel QR Codes Using LAMBDA Function

A more user-friendly approach to creating QR codes is to wrap the IMAGE function inside Excel’s LAMBDA function.

While writing a custom LAMBDA function seems daunting at first, the output custom function simplifies generating QR codes in Excel.

Let’s recreate the QR Codes from the last example, but this time using the LAMBDA function.

Go to Formula Tab > Define Name

This opens the new name dialog box where you can create your LAMBDA function. You can resize this window to make it wider for better visibility.

Tip: You can also use the Advanced Formula Environment from the Excel Labs add-in (see video for steps) to easily write LAMBDA functions and define their names, instead of using the Name Manager. Alternatively, you might find it easier to create the LAMBDA function in a cell in the grid and then copy it in the “Refers to” section to define its name.

Define a name for it e.g. fnGetQRCode. Keep it descriptive for the user to understand what the function does.

Tip: Consider prefixing the function with fn or the lambda symbol and use a combination of upper and lower case letters in the function name to differentiate it from the built in functions. E.g. fnGetQRCode or λGetQRCode (credit to Peter Bartholomew).

You can give a short description of the custom function in the comment section, for example = fnGetQRCode (URL For QR, Size of QR)

Now in the “Refers to” section paste the below LAMBDA function and press ok.

=LAMBDA(

            url,

            size,

            IMAGE("https://chart.googleapis.com/chart?chs="&size&"x"&size&"&cht=qr&chl="&url,url,2))

In this function, I have defined 2 parameters – ‘url’ and ‘size’ and then used them in the image function.

Tip: keep your LAMBDA parameters as descriptive as possible as they appear in the tooltip of the custom function and guide the user to fill in the right values.

Once done, your new function, fnGetQRCode is ready to use right in the grid to generate your QR Codes! See how much easier it is to use:

You just need to reference the cell containing the URL and provide the size of the QR Code.

Benefits:

1. Simple to use: The custom function resulting from the LAMBDA function is very simple to use and you don’t have to remember the Google API URL as it’s tucked away in the LAMBDA for use when you’re ready.
2. Less prone to accidental edits: You don’t alter the actual LAMBDA function each time you use it. You only enter the parameter values; the LAMBDA function works in the background
3. Eliminates the need to use VBA: As a Microsoft 365 user, you don’t need VBA anymore for generating QR Codes

Limitations:

1. Difficult to create: Writing the LAMBDA function requires some level of technical understanding of Excel functions
2. Limited backward compatibility: LAMBDA is only available in Microsoft 365 and Excel for the Web, and has limited backward compatibility. Its results are visible in the older versions, however, you can’t edit or generate new QR codes using it in the older versions.

Excel QR Codes Using an Add-In

If you don’t have the IMAGE or LAMBDA functions, you can install a free Excel QR Code add-in.

Go to the Home Tab > Add-Ins

Get Add-ins > Search for QR Code > Scroll down to QR4Office > Press Add

Access the add-in from the Home Tab > Add-ins > My Add-ins > QR4Office.

The add-in will open in the task pane on the right.

Select the cell you want a QR generated for, then click the ‘copy’ icon in the add-in (see image below).

It will auto-detect if it’s a URL, but you can override this in the drop down:

You can also choose the color (4), size (5) and correction percentage (6) for your QR code.

Note: the maximum size allowed is 409x409.

Benefits:

1. Older version compatibility: The add-in works with older versions also, with which IMAGE and LAMBDA functions are not compatible
2. Color choices: You can change QR Code colors from right within Excel
3. Error-correction: You can specify error correction percentage in the QR Code add-in, which would otherwise require you to edit the API URL while generating QR codes using IMAGE or LAMBDA functions.

Limitations

1. Inserted as objects: The QR Code generated using the add-in are inserted as images, not in-cell.
2. Unalterable: Each time you alter the URL, you will have to create a new QR code and delete the old one.
3. Accidental mix-ups: As they are not in-cell QR codes, they can easily get mixed up because you can’t add an alt_text.

Benefits of Using Excel as a QR Code Generator

While generating QR codes is pretty simple using any online QR code generator, using Excel Functions has its benefits:

1. Simplicity: Excel can easily generate QR codes based on data in cells
2. Bulk creation: You can quickly generate QR codes in bulk, you just need to drag the formula down
3. Data integration: You can keep all your data together in one place, including the QR codes
4. Easily alterable: You can alter the QR codes quickly by changing the referenced cell values
5. Transferable: You can copy the in-cell QR codes and paste them as images in your chat, or website, or even print them to display
6. Multi-Input compatibility: You can store URLs and Text in the QR Codes. They can store up to 4,296 alphanumeric characters
7. expiry: Static QR codes don’t expire, assuming the URL stored in them is still valid
8. Google Sheets compatibility: the IMAGE function is also available in Google Sheets slightly different syntax: IMAGE(url, [mode], [height], [width]) where Mode is the sizing mode for the image and is equivalent to Excel’s Sizing argument.

Limitations of Using Excel as a QR Code Generator

There are some limitations to generating QR Codes in Excel:

1. Maximum size: While there is no size limit to creating the QR Code using Excel functions, the maximum row height in Excel is 409.5. After that, you can zoom in on the worksheet to make it bigger. Alternatively, you can resize the QR code in image editors such as Paint or a Word document, however, some loss in resolution is expected during resizing.
2. Single color: You can’t change the color of the QR codes generated using IMAGE and LAMBDA functions. However, if you paste it as an image in Word or PowerPoint, you can change QR code colors, in a jiffy. Simply go to Picture Format > Colors and pick the color you like! But the add-in offers that functionality right within Excel.

Next Steps to Excel Mastery

Of the 3 options, I think the LAMBDA custom function is the best. If you’d like to master the LAMBDA function to streamline more of your work check out my Excel LAMBDA Function tutorial.

If you liked the way we can simplify tasks in Excel with formulas like this, and you’d like to master more, check out my Advanced Excel Formulas course.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub

Want to sponsor our newsletters?  Just reply to this email to get in touch with us.