I was recently working on a project where I had to create QR codes (Quick Response codes) in bulk in Excel for some of the URLs I had in a column.
I figured out a couple of different ways to do this, and in this article, I’m going to share four methods you can use to quickly create QR codes in Excel.
You can also click here to download the QR code generator template, where you can specify the data in column A and then instantly generate the QR codes.
Using the IMAGE Function (for Excel with Microsoft 365)
If you’re using Excel with Microsoft 365 (or Excel on the web), the easiest way to create QR codes would be by using the IMAGE function.
IMAGE is a new function that is only available in Excel with Microsoft 365, so if you are using older versions of Excel, you will have to use the other methods covered in this tutorial
Let me show you how to use the IMAGE function to create QR codes.
Below is a data set where I have some URLs in column A for which I want to generate the QR codes in column B.
Here is the formula that will do this:
Enter this formula in cell B2, and then copy it for all the other cells where you want the QR codes.
Note: You may see a lag of a few seconds as the IMAGE function tries to access the GoogleAPIS URL and then generate the QR code and then fetch it back in the cell.
It’s as easy as this!
Note: In this example, I’ve shown you how to create QR codes for URLs, but you can do the same thing for any kind of data set, including numbers or text (such as names or addresses)
How does this work?
Google has this free service where you can generate QR codes by using the URL https://chart.googleapis.com/chart?chs=100×100&&cht=qr&chl=[Your Text]
You need to use the same URL and replace [Your Text] with the text or URL for which you want to generate the QR code.
With the IMAGE function, we use this URL to generate the QR code for the data that we have in column A and then fetch that image in the cell.
IMAGE function has the following syntax:
=IMAGE(source, [alt_text], [sizing], [height], [width])
In the image function, the source argument must be provided, which should be the URL from which you want to fetch the image. The rest of the arguments are optional, so I’ve left them out.
A few things you need to know about this method:
- The QR code you get from the IMAGE function is embedded within the cell. If you delete the cell, then it will get deleted as well, and if you resize the cell, then it will also get resized with the cell.
- The size of the QR code to be generated is already mentioned in the URL (100X100). You can change this if you want.
- Unfortunately, there is no way for you to download this image that the IMAGE function has given to your system.
Note: The IMAGE function is also available in Google Sheets, so you can use the same formula in Google Sheets as well
Using VBA Code to Generate Custom Function
If you do not have the IMAGE function in your Excel version and you want to generate QR codes in bulk, the next best thing would be to use a simple VBA code to create your own function to do this.
Below is the VBA code that creates a user-defined function called GETQRCODES, which can be used within a cell, and it would instantly generate the QR code for the given data point.
'Code by Sumit Bansal from https://trumpexcel.com Function GETQRCODES(QrCodeValues As String) Dim URL As String Dim CellValues As Range Set CellValues = Application.Caller URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & QrCodeValues On Error Resume Next ActiveSheet.Pictures("Generated_QR_CODES_" & CellValues.Address(False, False)).Delete On Error GoTo 0 ActiveSheet.Pictures.Insert(URL).Select With Selection.ShapeRange(1) .Name = "Generated_QR_CODES_" & CellValues.Address(False, False) .Left = CellValues.Left + 2 .Top = CellValues.Top + 2 End With GETQRCODES = "" End Function
Let me show you how to use this code.
Below is the data set where I have some URLs in column A, and I want to generate the QR codes in column B using the above VBA code.
Here are the steps to do this:
- Open the VB editor by clicking the Developer tab and then the Visual Basic icon. You can also use the keyboard shortcut ALT + F11 to open the VB editor.
- Click on the Insert option in the menu.
- Click on the Module option. This will insert a new module for the workbook where we are going to copy and paste our VBA code.
- Copy and paste the above VBA code to the module code window.
- Go back to the Excel worksheet.
- Enter the following formula in cell B2
As soon as you enter this formula and hit the enter key, it is going to take a few seconds and then give you the QR code image right there above the cell where you entered the formula. Resize the cell to fit the QR code in the cell.
- Copy this formula for the other cells in column B to generate the QR codes for the remaining cells.
The above VBA code uses a Google API URL (https://chart.googleapis.com/chart?chs=100×100&&cht=qr&chl=) to create the QR code for each cell.
The custom formula GETQRCODES that we have created Returns no value except the QR code image.
Note: Unlike the IMAGE function, the QR code image that you get using the VBA code is like a shape/object that floats over your worksheet (and is not embedded into the cell in which the formula is used). So once you have the QR code image, you need to resize the cell and make space so that the QR code can be placed in the cell.
Using Third Party Add-ins
You can also take advantage of some third-party adding that can be used to create QR codes in Excel.
This method is suitable in cases you only have a few data points for which you want to create the QR code (as you need to create the QR code one by one for each cell).
Below I have the same data set where I have some URLs in column A, and I want to create the QR codes for them in column B.
Here are the steps to first install and add-in and then use that add-in to create the QR codes:
- Click the Developer tab. If you do not see the Developer tab, click here to read how to get the Developer tab in your Excel ribbon
- Click on the Add-ins option. This will open the office add-ins dialog box
- Click on the ‘STORE’ options
- Enter the term ‘Qr Code’ in the search box and then hit the enter key. This will show you all the add-ins that can be used to make QR codes in Excel.
- In the list, spot the QR4Office add-in and click on the Add button.
- Click on the Continue button in the dialog box that opens. This will install this add-in in your Excel and also open the QR4Office pane on the right side.
- In the ‘Enter the URL/text you’d like to encode:’ drop-down, select the https:// option. You can make the selection based on the data type that you have. In this case, because I have URLs, I have selected the https:// option.
- Copy and paste the URL in the box next to the drop-down.
- [Optional] Specify the color, background color, or size of the QR code
- Click on Insert
The above steps would insert the QR code for the URL in the worksheet.
Once you have the QR code, you can resize the cells and place the QR code in column B.
Now you need to repeat the same process for all the other URLs.
Note: One obvious drawback of this method is that you need to generate these QR codes one by one, Which may not be the most efficient method in case you have a lot of URLs.
Also read: How to Create and Use an Excel Add-in
Using External Website
If you do not want to add a third party adds into your Excel file, or you do not have permission to do that, another method you can use is by creating QR codes in an external website and then importing them into Excel.
There are many different websites you can use to create QR codes, and the one that I will show you in this tutorial is QR Code Generator.
Just like the add-in method, here also you will have to create these QR codes one by one.
Below I have the same data set where I want to create the QR code for the given URLs in column B.
Here are the steps to do this:
- Open a browser and navigate to the URL https://www.qr-code-generator.com/
- Return to the Excel sheet and copy the first URL for which you want to generate the QR code.
- Return to the QR code generator website, select the URL option, and paste the copied URL in the box. You’ll see that the QR code has instantly been generated and is being shown on the right.
- [Optional] Make the customization, such as selecting the frame or changing the shape or color of the QR code
- Click the Download button. This will download the image of the generated QR code on your system.
- Return to Excel, click the Insert tab, then click on the Pictures option, and then click on the ‘This Device’ option.
- In the ‘Insert Picture’ dialog box that opens, locate the file, select it, and then click on the Insert button.
- Resize the cells and place the QR code image in the cell in column B.
You need to repeat these steps for all the data points for which you want to generate the QR code and insert the image in Excel.
Note: Since you need to create these QR codes one by one, this method is suited when you have a few data points only. In case you have a large data set, it is better to use the VBA method.
Click here to download the QR code generator template, where you can specify the data in column A and then instantly generate the QR codes
In this article, I’ve shown you four ways you can use to generate QR codes in Excel.
If you are using Excel with Microsoft 365, the best way would be to use the IMAGE function.
In case you do not have access to the IMAGE function and you want to generate these QR codes in bulk, you can use the VBA method.
And in case you want to generate these only for a few cells then you can use the add-in or the external website method.
Other Excel articles you may also like: