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.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

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

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.

Conclusions

We’ve seen that we can easily create QR codes in Excel for free using QuickChart.io with 3 methods (IMAGE function, VBA, and Office Scripts).

No matter which method we use, QuickChart.io gives us the ability to customize the QR code with logos and color schemes.

Enjoy… happy QRing.

Related posts:

• How to email or save Excel images with Power Automate
• How to change images based on cell values (3 ways)
• How to Insert, Move & Delete Pictures with VBA