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

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.

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.

Select Continue, and QR4Office will be installed.

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.

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.

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.

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.

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

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.

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.

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)

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.

Generating QR Code.xlsm

QR Code in Excel: Knowledge Hub

<< Go Back To Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

Mark Dec 24, 2022 at 10:45 AM

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?
- Reply
  Mashhura Dec 27, 2022 at 4:42 PM
  
  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)
  
  Step-02: Press Ctrl + Enter and you will get your desired output.
  
  I hope this will help you to solve your problem. Please let me know if you have other queries.
  Regards
  Mashhura,
  ExcelDemy.
Rob Jan 23, 2024 at 10:14 AM

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
- Reply
  Afrina Nafisa Jan 23, 2024 at 4:04 PM
  
  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