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

 

To explain how you can generate QR codes in Excel, let’s use 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


Method 1 – Using Office Add-ins to Create QR Code in Excel

Steps:

  • Go to the Insert tab.
  • Select the Get Add-ins option from the Add-ins group.

A library will appear on the screen.

  • Search for QR4Office. You should get a single exact match.
  • Click on Add to add the QR4office to your Add-ins.

Using Office Add-ins to Create QR Code in Excel

  • Select Continue, and QR4Office will be installed.

Using Office Add-ins to Create QR Code in Excel

  • Select the cell where you want to add the QR code (in this case D5).
  • Go to the Insert tab again.
  • Select My Add-ins.

  • This will lead you to your My Add-ins library.
  • Select QR4Office.
  • Click on Add.  

Using Office Add-ins to Create QR Code in Excel

  • QR4Office opens 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

  • Type in the text or URL that you want to encode. Here, we typed the URL for ExcelDemy.
  • Click Insert to get your QR code.

  • This fills the QR code for the desired site.

Using Office Add-ins to Create QR Code in Excel

  • Repeat the process to get all the other QR codes you want.


Method 2 – Creating User Defined Function to Generate a QR Code

Steps:

  • Go to the Developer tab.
  • Select Visual Basic. This opens the Visual Basic window.

  • Go to the Insert tab
  • Select the Module option to open a new module.

Creating User Defined Function to Create QR Code in Excel

  • In the 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

  • We have created a Function named QR_Generator. Next, used qrcodes_values as String within the function.
  • Then we declared Site_URL as String and Cell_Values as Range.
  • The Application.Caller in the Set property triggers the macro where it will be called.
  • The URL address is converted into qr codes.
  • We used On Error Resume Next to ignore errors.
  • The ActiveSheet.Pictures part creates the picture in the active sheet.
  • The With statement resizes the qr codes.
  • Save the code as Excel Macro-Enabled Workbook and go back to your sheet.

Creating User Defined Function to Create QR Code in Excel

  • Select all the cells where you want your QR codes. We selected cells D5, D6, and D7.

  • Write the following formula.
=QR_Generator(C5)

We used the QR_Generator function defined by the VBA code. This function will return us the QR code for the Value in cell C5.

  • Press Ctrl + Enter to get QR codes for all the cells.

Creating User Defined Function to Create QR Code in Excel


Method 3 – Creating QR Codes in Excel Using the IMAGE Function

Steps:

  • 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


Things to Remember

  • Using the conversion functions and installing add-ins requires an internet connection.
  • 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 used in this article from the download link below.


QR Code in Excel: Knowledge Hub


<< Go Back To Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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