How to Create QR Code in Excel (3 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


Watch Video – 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.

2 Simple Ways to Create QR Code in Excel


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.

Steps:

  • 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.

Using Office Add-ins to Create QR Code in Excel

Now, it will show you license terms and policy.

  • Finally, select Continue, and QR4Office will be installed.

Using Office Add-ins to Create QR Code in Excel

  • 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.  

Using Office Add-ins to Create QR Code in Excel

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.

Using Office Add-ins to Create QR Code in Excel

  • 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.

Using Office Add-ins to Create QR Code in Excel

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.

Steps:

  • 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.

Creating User Defined Function to Create QR Code in Excel

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
    ActiveSheet.Pictures.Insert(Site_URL).Select
    With Selection.ShapeRange(1)
     .Name = "Generated_QR_CODES_" &  Cell_Values.Address(False, False)
     .Left = Cell_Values.Left + 2
     .Top = Cell_Values.Top + 2
    End With
    QR_Generator = ""   
End Function

Creating User Defined Function to Create QR Code in Excel

Code Breakdown

  • 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.

Creating User Defined Function to Create QR Code in Excel

  • 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.
=QR_Generator(C5)

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.

Creating User Defined Function to Create QR Code in Excel


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.

Steps:

  • Just apply the following formula and use Fill Handle to AutoFill QR codes in the rest of the cells in column D.
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=100×100&data="&C5)

Using IMAGE Function to Generate QR Code


Practice Section

Here, I have provided a practice sheet for you to practice.

Practice Section


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.


Download Practice Workbook

You can download the practice workbook that is used in this article from the download link below.


Conclusion

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


<< Go Back To Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

4 Comments
  1. I’ve been trying to implement this code, and though it does work well on a workbook with only one sheet, it has problems on anything else. The QR code always ends up on the active sheet (right location but wrong sheet most of the time). I thought I could fix that by specifying the sheet where it belonged, for example replacing ActiveSheet with Worksheets(“Sheet 1”) in both instances in the code. It doesn’t seem to help. Is there a way to make the QR code show on the correct page?

    • Hey MARK,
      Thanks for your comment. I am replying to you on behalf of ExcelDemy. For the provided practice workbook, I used the following VBA code and it solved this problem for me.

      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
      Worksheets("Using User Defined Function").Pictures("Generated_QR_CODES_" & Cell_Values.Address(False, False)).Delete
      On Error GoTo 0
      Worksheets("Using User Defined Function").Pictures.Insert(Site_URL).Select
      With Selection.ShapeRange(1)
      .Name = "Generated_QR_CODES_" & Cell_Values.Address(False, False)
      .Left = Cell_Values.Left + 2
      .Top = Cell_Values.Top + 2
      End With
      QR_Generator = ""

      End Function

      Now, Save the code and go back to your worksheet. Let’s see the steps of using the function.
      Step-01: Select the cells where you want the QR Codes. Here, I selected cell range D5:D7 >> write the following formula.
      =QR_Generator(C5)
      Writting Formula
      Step-02: Press Ctrl + Enter and you will get your desired output.
      Getting Result
      I hope this will help you to solve your problem. Please let me know if you have other queries.
      Regards
      Mashhura,
      ExcelDemy.

  2. Hi

    The solutions work great but is there a way to enable the QR code not to truncate the URL e.g. Google.com/sign_in gets truncated to Google.com.

    thanks

    • Hello ROB,
      Hope you are doing well. The behavior you describe is how a scanner or browser shows the URL while scanning. The QR codes contain the full URL within them, and the scanner opens the full URL.
      However, some scanners display the base domain of the URL for user-friendliness and simplification. Unfortunately, there is no direct solution but you can use online URL decoder to see the encoded URL before opening or try different scanners to get the full URL while scanning the QR code.

      I hope this will help you to solve your problem. Please let me know in the comment section if there are any other queries.

      Regards
      Afrina Nafisa
      Exceldemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo