How to create QR codes in Excel for FREE (3 easy ways)

I was recently asked to add a QR code to a document. While I had seen them before, I had never needed to create one from scratch. But, it turns out it’s pretty simple and free in Excel. So, in this post, we look at 3 easy ways to create QR codes in Excel.

Table of Contents

About QuickChart.io
Create QR codes with the IMAGE function (Excel 365)

Basic parameters
Using additional parameters

Create QR codes with VBA
Create QR codes with Office Scripts
Which method to use?
Conclusions

Download the example file: Join the free Insiders Program and gain access to the example file used for this post.

File name: 0139 QR Codes in Excel.zip

Get access

Watch the video

3 ways to create QR Codes in Excel for free | Excel Off The Grid

Watch on YouTube

About QuickChart.io

All the methods in this post use an online service to generate the QR code image. We are using QuickChart.io, but others are available, such as goqr.me, and the google API.

With QuickChart, we only need a URL with the correct syntax to generate a QR code. For example, the URL below generates the QR code.

https://quickchart.io/qr?text=https://exceloffthegrid.com

The section after text= is the text embedded in the QR code.

QuickChart.io has lots of additional parameters for generating the QR codes. Here is a selection of those parameters, which we use in the next section.

margin: the amount of white space around the QR code (default is 4)
size: the image size in pixels (default is 150)
dark: the hex color of the dark cells (default is #000000)
light: the hex color of the light cells & margin (default is #ffffff)
ecLevel: the amount of error correction, valid entries are L, M, H, Q (default is M, or H if QR code includes an image)
centerImageURL: the URL of an image to include in the center of the QR code
centerImageSizeRatio: the % of space taken up by the center image.

Note: If using a center image, test the result to ensure the QR code works. If the image covers too much of the code, it may fail to operate correctly.

Create QR codes with the IMAGE function (Excel 365)

Since late 2022, Excel 365 and Excel Online users have had the IMAGE function. If combined with a service like QuickChart, we can create dynamic QR codes in Excel.

The image function has the following syntax.

=IMAGE(source, [alt_text], [sizing], [height], [width])

The arguments are:

source: The URL path to the image
alt_text (optional): The text to describe the image for accessibility purposes
sizing (optional): A setting to represent how the image size should be set:
- 0: Fit the image in the cell and maintain the aspect ratio
- 1: Stretch or squeeze the image to fill the cell
- 2: Keep the original image size, even if it is outside the cell
- 3: Provide custom heights and widths
height (optional): Set the image height in pixels where the sizing argument is set to 3
width (optional): Set the image width in pixels where the sizing argument is set to 3

Want to know more about the IMAGE function? Click here

Basic parameters

OK, let’s try this out.

Cell C2 is the URL for Excel Off The Grid. Cell C3 uses C2 to create the full URL path for QuickChart.

="https://quickchart.io/qr?text="&C2

The function in cell C10 is:

=IMAGE(C3)

That’s it; that is all it takes to generate a QR code.

If we change the text in cell C2, the QR code changes. It’s completely dynamic.

Using additional parameters

Playing with the additional parameters creates lots of different styles (all available in the download file).

QR code with an image in the center

https://quickchart.io/qr?text=https://exceloffthegrid.com&centerImageUrl=https://exceloffthegrid.com/wp-content/uploads/2016/10/excel-off-the-grid-logo.jpg&centerImageSizeRatio=0.5

QR Code with non-standard colors

https://quickchart.io/qr?text=https://exceloffthegrid.com&dark=16522E&light=DDF7E8

QR Code with lower error correction

https://quickchart.io/qr?text=https://exceloffthegrid.com&ecLevel=L

Create QR codes with VBA

If you don’t have Excel 365, and not using Excel Online, then you don’t have the IMAGE function. But… there is another option using VBA.

Copy the following code into a standard code module. Then, run the code.

The macro places the QR code into cells C19:C25 of Sheet1 based on the URL in cell C3. You can change these to your requirements.

Sub GenerateQRCode()

Dim QRURL As String
Dim QRShape As Object
Dim QRLocation As Range

'Assign variables
Set QRLocation = Sheets("Sheet1").Range("C19:C25")

'Create the URL path
QRURL = Sheets("Sheet1").Range("C3").Value

'Delete the previous QR code
On Error Resume Next
Sheets("Sheet1").Shapes("QRCodeVBA").Delete
On Error GoTo 0

'Get and create the image
Set QRShape = Sheets("Sheet1").Pictures.Insert(QRURL)

'Change placement of image
With QRShape
    .Name = "QRCodeVBA"
    .Top = QRLocation.Top
    .Left = QRLocation.Left
    .Height = QRLocation.Height
    .Width = QRLocation.Height
End With

End Sub

Create QR codes with Office Scripts

So, having looked at a VBA example, the next question is… can we do the same with an Office Script. The answer is YES.

Copy the code below into the script editor. Then, run the script.

The script places the QR code into cells C27:C33 of Sheet1 based on the URL in cell C3. Change this in the code for your scenario.

async function main(workbook: ExcelScript.Workbook) {

//Assign variables
let ws = workbook.getWorksheet("Sheet1");
let QRURL = ws.getRange("C3").getValue().toString();
let QRLocation = ws.getRange("C27:C33");

//Fetch the URL Request
let QRResponse = await fetch(QRURL);

//Store response as ArrayBuffer (required for an image file).
let QRImageData = await QRResponse.arrayBuffer();

//Convert image to base64
let uInt8Array = new Uint8Array(QRImageData);
let count = uInt8Array.length;
let charCodeArr = new Array(count) as string[];
for (let i = count; i >= 0; i--) {
    charCodeArr[i] = String.fromCharCode(uInt8Array[i]);
};
let QRBase64 = btoa(charCodeArr.join(''));

//Delete the previous image
try {
    ws.getShape("QRCodeScripts").delete();
} catch {};

//Add image to worksheet
let QRShape = ws.addImage(QRBase64);
QRShape.setName("QRCodeScripts");
QRShape.setTop(QRLocation.getTop());
QRShape.setLeft(QRLocation.getLeft());
QRShape.setHeight(QRLocation.getHeight());
QRShape.setWidth(QRLocation.getHeight());

}

NOTE: I did not write all this code myself. It is based on this post by Microsoft:
https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/add-image-to-workbook

Which method to use?

Of the three methods, which should you use?

If you do not have the IMAGE function or Office Scripts, VBA is the only option.

However, if you have IMAGE and Office Scripts, then it depends on your requirements. The Office Script and VBA solutions create a static image in the sheet. Once the image is in there, it no longer depends on QuickChart.

IMAGE function is more dynamic, but requires QuickChart each time it executes.

So, if you intend to keep static versions of the QR codes, then use the Office Script or VBA method. However, if that’s not a key requirement, then IMAGE is easier to apply.