Quick Response (QR) codes can be used in Excel to share information or to access data stored in an Excel spreadsheet quickly. However, Excel does not have a built-in feature to create QR codes.
Still, you can use various techniques, such as an add-in, to generate QR codes and insert them into Excel.
What is a QR Code?
A QR code (short for “Quick Response code”) is a two-dimensional barcode that contains information that can be read via a smartphone or other QR code reader.
An example of a QR code image is shown below:
The QR code consists of black and white squares arranged in a specific pattern that can be scanned by the reader, which then decodes the information stored in the code.
The information stored in the QR code can include a wide range of data, such as website URLs, contact information, text, and more.
QR codes are commonly used in marketing and advertising, as well as in retail and manufacturing, for inventory tracking and management.
This tutorial shows four techniques for creating a QR code in Excel.
Method #1: Using IMAGE Function to Create QR Codes in Excel
The IMAGE function, only available in Excel 365, returns an image from a given source. We can utilize the function to create a QR code image in Excel.
Assume we have the following dataset showing various URLs:
We want to apply the IMAGE function in column B to return to QR codes of the URLs displayed in column A.
We use the steps below:
- Copy the formula below and paste it into cell B2:
=IMAGE("https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl="&A2)
- Click Enable on the Microsoft Excel Security Notice dialog box that appears (in case it appears):
- Drag or double-click the fill handle feature in cell B2 to copy the formula down the column:
We now have QR codes in our Excel worksheet that can be easily scanned using a QR code reader app on a mobile device.
Explanation of the Formula
=IMAGE("https://chart.googleapis.com/chart?chs=100x100&&cht=qr&chl="&A2)
This formula generates a QR code image using the Google Chart API with the URL in cell A2.
Here is the breakdown of the formula:
- =IMAGE( This is the start of the formula indicating that you want to display an image in the cell.
- “https://chart.googleapis.com/chart?chs=100×100&&cht=qr&chl=” is the link to the Google Chart API service that produces QR codes. The “chs” parameter specifies the QR code size (100×100 pixels), while the “cht” parameter indicates the chart type (QR code). The content of the QR code is specified through the “chl” parameter.
- “&A2)” The value in cell A2 is joined to the end of the URL, passing it as the content (or data) for the QR code.
Method #2: Using an Add-in to Create QR Codes in Excel
We can use various add-ins, for example, QR4Office, to create and insert a QR code into Excel.
For this article, I will use the QR4Office add-in and explain how to use it to insert QR codes in Excel.
Let’s consider the following dataset showing various URLs.
We want to use the QR4Office add-in to generate a QR code for each URL and display it in the corresponding cell in column B.
Below are the steps to generate QR codes using the add-in:
Step #1: Insert the QR4Office Add-in into Excel
Use the steps below to insert the QR4Office add-in into Excel:
- On the Insert tab, open the Add-ins drop-down and choose the Get Add-ins option.
- On the Office Add-ins dialog box, enter the search term “qr4office” on the search box. When the QR4Office add-in appears on the list, click the Add button.
- Click Continue on the dialog box that pops up.
- The QR4Office add-in is installed, and its task pane is displayed on the right of the Excel window.
Note: When you open the ‘Enter URL/text you’d like to encode’ drop-down on the task pane, you see the data types you can encode using the add-in.
Step #2: Use the QR4Office Task Pane to Generate QR Codes
Use the below steps to generate the QR codes for the dataset’s URLs:
- Select cell A2 containing the first URL and press Ctrl + C to copy it.
- Paste the URL onto the “Enter the URL/text you’d like to encode” box:
Notice the preview of the QR code on the Preview box of the task pane.
- Use the options on the task pane’s Options group to change the QR code’s size and color. In this example, we stick with the default settings.
- Select cell B2 and click the Insert button at the bottom of the task pane.
The QR code is inserted into cell B2. You can resize it and position it in the cell as needed.
- Repeat steps 1-4 to insert QR codes for the rest of the URLs.
- Save the worksheet.
Now you have QR codes in your Excel worksheet that can be scanned by a QR code reader app on a mobile device.
Also read: How to Create Barcodes in Excel
Method #3: Using QR Code Generator Website to Create QR Codes in Excel
You can use QR code generator websites such as Qr-code-generator.com, qrstuff.com, or kaywa.com to create QR codes and insert them into Excel.
In this tutorial, we shall use QR-code-generator.com in our illustration.
Suppose we have the following Excel dataset showing the URLs of particular organizations.
We want to use the QR code generator website, QR-code-generator.com, to create QR codes for the URLs and display them in column B.
Below are the steps to do this:
- Open a web browser and go to the QR code generator website, QR-code-generator.com.
- Select cell A2 and press Ctrl + C to copy the website URL.
- Enter the URL in the generator and customize the desired size, color, and other settings.
Notice the generated QR code preview on the right of the screen.
- Click the green Download button on the right of the website window to download the image file to your computer.
- Open your Excel worksheet and select cell B2 where you want to insert the QR code.
- On the Insert tab, open the Illustrations, then the Pictures drop-downs, and choose This Device.
- Browse to the location where you saved the QR code image file, select the file, and click Insert.
- Resize and position the QR code image within the cell.
- Repeat steps 2-8 to generate and insert QR codes for the rest URLs.
- Save your Excel worksheet.
Now you have created QR codes in your Excel spreadsheet that can be scanned by a QR code reader app on a mobile device.
Method #4: Using a User-Defined Function to Create QR Codes in Excel
We can use Excel VBA to create a User Defined Function that we can apply in creating QR codes in Excel.
We have the following dataset showing the URLs of particular organizations:
We want to create and use a User Defined Function to generate QR codes for the URLs.
We use the following steps:
- Press the shortcut Alt + F11 to open the Visual Basic Editor.
- Open the Insert menu and choose Insert to create a module in the Visual Basic Editor.
- Copy the following function procedure and paste it into the module:
'Code developed by Steve Scott from https://spreadsheetplanet.com
Function QRCODEGENERATOR(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
QRCODEGENERATOR = ""
End Function
- Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).
- Press the shortcut Alt + F11 to switch to the active worksheet containing the dataset.
- Select the cell range B2:B6:
- Type the following formula in the active cell of the selected cell range:
=QRCODEGENERATOR(A2)
- Press Ctrl + Enter to enter the formula.
The QR codes for the URLs are generated:
This code would generate QR codes that are all the same size (we specified the size in our code). So you need to ensure that your cells are big enough so that the QR code is placed in them.
Explanation of the User-Defined Function
- The UDF is named QRCODEGENERATOR and takes one input parameter, QrCodeValues, which is the string value to be encoded in the QR code.
- Two variables are declared: URL and CellValues. URL stores the URL for the Google Chart API, which will generate the QR code image. CellValues is used to store a reference to the cell range that called the UDF.
- Application.Caller is used to set the CellValues variable to the reference to the range of cells that called the UDF. This process lets the UDF know where to insert the generated QR code image.
- The URL variable is then constructed using the input parameter QrCodeValues and the Google Chart API URL.
- On Error Resume Next is used to suppress any error messages that may occur when trying to delete an existing QR code image from the worksheet.
- If a QR code exists in the cell range, it is deleted using the ActiveSheet.Pictures(“Generated_QR_CODES_” & CellValues.Address(False, False)).Delete statement.
- On Error GoTo 0 is used to turn off error handling.
- The ActiveSheet.Pictures.Insert(URL) statement inserts the QR code image into the worksheet.
- The With Selection.ShapeRange(1) statement selects the newly inserted QR code image and sets some properties. The Name property is set to a unique name based on the cell that called the UDF. The Left and Top properties are set to position the QR code image next to the cell that called the UDF.
- The QRCODEGENERATOR function returns an empty string, which is not used for anything.
In this tutorial, I’ve covered three methods you can use to generate QR codes in Excel.
The easiest way would be to use and add in such as QR4Office or an external website such as QR-code-generator.com, however, you will have to generate these QR codes one by one.
In case you want to generate a lot of QR codes in one go, you can use the VBA method covered in this tutorial.
Other Excel articles you may also like:
