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".
Table of Contents
Watch the Video
[UPDATE]: the Google API shown in the video no longer works. See the blog post below for an alternate API.
Get the Excel Example File and Cheat Sheet
Enter your email address below to download the sample workbook.
Download the Cheat Sheet
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 a free QR Code Generator API, in this blog we will use
https://qrcode.tec-it.com/API/QRCode?data=
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://qrcode.tec-it.com/API/QRCode?data="&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.
You can change the QR code size using the custom sizing arguments of the IMAGE function.
Thus, my final IMAGE function will look like this:
=IMAGE("https://qrcode.tec-it.com/API/QRCode?data="&C4,C4,3,100,100)
Where the sizing argument is 3 and the following arguments for height and width are 100 to return a 100 x100 QR code:
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:
- Simplicity: The IMAGE function is simple to use
- Enables bulk QR code creation: You can create multiple QR codes in seconds
Limitations:
- Accessibility: The IMAGE function is only available in Microsoft 365
- No backward compatibility: There's no backward compatibility of the IMAGE function with the older Excel versions
- 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://qrcode.tec-it.com/API/QRCode?data="&url,url,3,size,size))
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:
- 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.
- 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
- Eliminates the need to use VBA: As a Microsoft 365 user, you don't need VBA anymore for generating QR Codes
Limitations:
- Difficult to create: Writing the LAMBDA function requires some level of technical understanding of Excel functions
- 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:
- Older version compatibility: The add-in works with older versions also, with which IMAGE and LAMBDA functions are not compatible
- Color choices: You can change QR Code colors from right within Excel
- 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
- Inserted as objects: The QR Code generated using the add-in are inserted as images, not in-cell.
- Unalterable: Each time you alter the URL, you will have to create a new QR code and delete the old one.
- 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:
- Simplicity: Excel can easily generate QR codes based on data in cells
- Bulk creation: You can quickly generate QR codes in bulk, you just need to drag the formula down
- Data integration: You can keep all your data together in one place, including the QR codes
- Easily alterable: You can alter the QR codes quickly by changing the referenced cell values
- 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
- Multi-Input compatibility: You can store URLs and Text in the QR Codes. They can store up to 4,296 alphanumeric characters
- Expiry: Static QR codes don't expire, assuming the URL stored in them is still valid
- 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:
- 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.
- 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.
Course |
Course URL |
QR Code |
Excel Functions |
||
Charting Blog Posts |
||
Excel Dashboard Blog Posts |
https://www.myonlinetraininghub.com/category/excel-dashboard |
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.
Sarah
Hi.
I’m using the add in function, and it’s great and very easy to use!
Should I be worried about any limitations for our customers accessing the QR code? Like it only works x amounts of times or can it handle being used daily for several years?
Mynda Treacy
Hi Sarah,
There’s no limits and it will never expire.
Mynda
Arnold
I have the same problem as Adric.
I don’t think it’s an internet connection problem. It’s more likely to do with the fact that this API is now deprecated: “Warning: This API is deprecated. Please use the actively maintained Google Charts API instead. See our deprecation policy for details.”
Mynda Treacy
Hi Arnold,
The API has been deprecated for 10 years and has been working fine. I don’t think it has anything to do with that.
Mynda
Adric
I love your guides – thank you for being so helpful with your Excel videos and website.
I have tried using the various options relating to creating QR codes from your page: https://www.myonlinetraininghub.com/qr-codes-in-excel
Whether I use the IMAGE or LAMBDA functions as you’ve described, I continually receive the #CONNECT! error.
Excel provides no further context as to what is missing or unable to connect.
Can you assist further at all?
Mynda Treacy
Hi Adric,
The #CONNECT! error indicates an issue with the internet connection. More on how to resolve it here.
Mynda