QR codes are encrypted squares that can include content, links, event information, and other information that users wish to see. You can generate QR code with the help of Excel. The main objective of this article is to demonstrate how to create QR code in Excel.
In this article, I will explain 3 methods by which you can create QR code in Excel. To explain these methods I have taken a dataset that contains the Site Name and its URL which is the Value for our QR code.
1. Using Office Add-ins to Create QR Code in Excel
In this method, I will explain how to create QR Code in Excel by using Office Add-ins.
Let’s see step by step how it is done.
- To begin with, go to the Insert tab.
- After that, select the Get Add-ins option from the Add-ins group.
A library will appear on the screen.
- Now, search for QR4Office. And you will get the QR4Office.
- Next, click on Add to add the QR4office to your Add-ins.
Now, it will show you license terms and policy.
- Finally, select Continue, and QR4Office will be installed.
- Now, again go to the Insert tab.
- After that, select My Add-ins.
This will lead you to your My Add-ins library.
- Next, select QR4Office.
- Finally, click on Add.
Now, you will see that QR4Office has opened on the Excel worksheet. You can type text or URL that you want to encode. You can also change the color, size, and background of the QR code from here.
- Now, type in the text or URL that you want to encode. Here, I typed the URL for ExcelDemy.
- Finally, click Insert to get your QR code.
Now, I have got the QR code for my desired site.
By following this same process you can get all the other QR codes you want.
2. Creating User Defined Function to Generate a QR Code
In this 2nd method, I will explain how to create QR codes in Excel by using user defined function. For this, I will use VBA.
Let’s see step by step how it is done.
- Firstly, go to the Developer tab.
- Secondly, select Visual Basic.
Now, you will see the Visual Basic window has opened.
- After that, go to the Insert tab.
- Now, select the Module option.
You will see a Module has opened. In that Module type in the following code.
Function QR_Generator(qrcodes_values As String)
Dim Site_URL As String
Dim Cell_Values As Range
Set Cell_Values = Application.Caller
Site_URL = "https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl=" & qrcodes_values
On Error Resume Next
ActiveSheet.Pictures("Generated_QR_CODES_" & Cell_Values.Address(False, False)).Delete
On Error GoTo 0
.Name = "Generated_QR_CODES_" & Cell_Values.Address(False, False)
.Left = Cell_Values.Left + 2
.Top = Cell_Values.Top + 2
QR_Generator = ""
- Here, I have created a Function named QR_Generator. Next, used qrcodes_values as String within the function.
- Then, declared Site_URL as String and Cell_Values as Range.
- Next, used the Application.Caller in the Set property to trigger the macro where it will be called.
- After that, given the URL address for qr codes.
- I, also used On Error Resume Next to ignore the error.
- Afterward, used the ActiveSheet.Pictures to create the picture in the active sheet.
- Next, by using the With statement re-sized the qr codes.
Now, Save the code as Excel Macro-Enabled Workbook and go back to your sheet.
- Now, select all the cells where you want your QR codes. Here, I selected cells D5, D6, and D7.
- After that, write the following formula.
Here, I used the QR_Generator function which I defined by the VBA code. And for qrcodes_values I selected cell C5. This function will return us the QR code for the Value in cell C5.
- Finally, press CTRL+ENTER and you will get QR codes for all the cells.
3. Creating QR Codes in Excel Using the IMAGE Function
With the help of the IMAGE function, we can set a QR code quite easily, according to the size of the cell.
- Just apply the following formula and use Fill Handle to AutoFill QR codes in the rest of the cells in column D.
Here, I have provided a practice sheet for you to practice.
Things to Remember
- While working with the second method it should be noted that here I used an open source link. So, to work this function properly you have to keep your internet connection on.
- While using the IMAGE function to generate QR codes, the generated QR codes get fitted according to the cell height. You can resize the QR codes just by changing the row height according to your needs.
Frequently Asked Questions
1. Can I use a specific font to generate a dynamic QR code in Excel?
There is a dedicated font named AlphanumericQR to generate QR codes in Excel.
2. How do I generate a QR code with a formula in Excel?
With the help of the IMAGE function in a formula, we can generate a QR code in Excel.
3. Is it possible to create multiple QR codes at once in Excel?
With the help of a VBA code, we can create multiple QR codes quite easily.
You can download the practice workbook that is used in this article from the download link below.
To conclude, in this article, I have tried to explain how to create QR codes in Excel. I covered 3 methods. Hope this was helpful for you. If you have any questions please, let me know in the comment section below.
QR Code in Excel: Knowledge Hub
- Batch QR Code Generator from Excel
- QR Code Attendance Tracking with Excel
- How to Scan QR Code to Excel Spreadsheet
- Excel VBA: Open Source QR Code Generator
<< Go Back To Learn Excel