How to Create QR Codes in Excel (QR Code Generator)

I was recently working on a project where I had to create QR codes (Quick Response codes) in bulk in Excel for some of the URLs I had in a column.

I figured out a couple of different ways to do this, and in this article, I’m going to share four methods you can use to quickly create QR codes in Excel.

You can also download the example file below and use it as a QR code generator template.

Replace the data in column A of the Using IMAGE Function or Using VBA sheet with your own, and the QR codes update (the sections below cover which Excel versions each one needs).

Create QR Codes Using the IMAGE Function (Excel 365 and 2024)

If you’re using Excel with Microsoft 365 (or Excel on the web) or Excel 2024, the easiest way to create QR codes would be by using the IMAGE function.

The IMAGE function works in Excel for Microsoft 365 (Windows, Mac, and the web), Excel 2024, and the Excel apps for iPhone and Android. If you are using older versions of Excel, you will have to use the other methods covered in this tutorial.

Let me show you how to use the IMAGE function to create QR codes.

Below is a data set where I have some URLs in column A for which I want to generate the QR codes in column B.

Here is the formula that will do this:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&qzone=2&data="&ENCODEURL(A2:A6))

Enter this formula in cell B2, and it spills down automatically to give you all five QR codes. If you have more data, just change A2:A6 to your own range.

Note: You may see a lag of a few seconds as the IMAGE function tries to access the QR Server URL, generates the QR code, and fetches it back in the cell.

It’s as easy as this!

In this example, I’ve shown you how to create QR codes for URLs, but you can do the same thing for any kind of data set, including numbers or text (such as names or addresses).

This formula uses the ENCODEURL function, which doesn’t work in Excel on a Mac or in Excel for the web. The next section covers what this function does and which formula to use in those versions.

How does this work?

QR server website has this free service where you can generate QR codes by using the URL https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=[Your Text]

You need to use the same URL and replace [Your Text] with the text or URL for which you want to generate the QR code.

With the IMAGE function, we use this URL to generate the QR code for the data that we have in column A and then fetch that image in the cell.

The formula also adds qzone=2 to the URL, which puts a small white border around each code. Without it, QR codes in neighboring cells touch each other, and that makes them harder to scan.

IMAGE function has the following syntax:

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

In the image function, the source argument must be provided, which should be the URL from which you want to fetch the image. The rest of the arguments are optional, so I’ve left them out.

A few things you need to know about this method:

• The QR code you get from the IMAGE function is embedded within the cell. If you delete the cell, then it will get deleted as well, and if you resize the cell, then it will also get resized with the cell. That’s why the rows in the example file are tall, as a code in a small cell can be too tiny to scan.
• The size of the QR code to be generated is already mentioned in the URL (150×150). You can change this if you want. This number sets the size of the image the website creates, while the cell decides how big it looks in Excel, so when your data is long (which means smaller squares in the code), increase both.
• Unfortunately, there is no download button for the image that the IMAGE function gives you, but you can still save it to your system. Copy the cell and paste it as a value, right-click the pasted cell, and go to Picture in Cell > Place over Cells. Then right-click the picture and choose Save as Picture.

The data you use in the formula is sent to the QR server website to create the code. Their documentation says they record where a request came from and not what’s in the QR code, but it’s still best to avoid this for anything sensitive.

You also need an internet connection for this to work, as the formula has to reach that website. If you want the codes to stop depending on it, copy the cells and paste them as values, which keeps the pictures and removes the formula.

The IMAGE function is also available in Google Sheets, so you can use the same idea in Google Sheets as well.

Google Sheets has the ENCODEURL function too. If the formula doesn’t fill the cells below when you use a range, point it at one cell (such as A2) and copy it down.

Also read: Create Barcodes in Excel (Free Barcode Generator)

Why the Formula Uses the ENCODEURL Function

You may have seen QR code formulas that leave out ENCODEURL and put the cell straight into the URL. That works for simple text, but some characters mean something special in a web address, so they don’t reach the QR code as you typed them.

The tricky part is that the wrong QR code looks perfectly fine in the cell, and you only find out when someone scans it. Let me show you what happens.

Below I have a URL, a phone number, and a business name in column A, and I want to create their QR codes without ENCODEURL in column B and with ENCODEURL in column D.

Here is the formula without ENCODEURL, which I’ve entered in cell B2:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&qzone=2&data="&A2:A4)

Column C in the example file shows what a scanner reads from each of these codes, and all three go wrong in a different way:

• The & sign cuts your data. In a web address, & starts a new setting, so everything after it gets dropped. The URL in A2 scans as https://trumpexcel.com/?utm_source=flyer, which loses the utm_medium=print part.
• The + sign turns into a space. A web address reads + as a space, so +14155550123 in A3 scans as a space followed by 14155550123, and the number loses its plus sign.
• Spaces show an error when the file reopens. You get a QR code for Smith Hardware Store when you enter the formula, but B4 shows #FIELD! when you save, close, and reopen the file. Pressing Ctrl + Alt + F9 brings the code back, but the error returns the next time you open the file.

Now here is the same formula with ENCODEURL, which I’ve entered in cell D2:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&qzone=2&data="&ENCODEURL(A2:A4))

The ENCODEURL function replaces those special characters with a code that’s safe to use in a web address (for example, & becomes %26). The website turns them back into your original text, so all three QR codes scan exactly as your data in column A.

The ENCODEURL function doesn’t work in Excel on a Mac or in Excel for the web. If you’re using either of those, you can try the below formula instead, which swaps out the & sign and spaces on its own:

=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&qzone=2&data="&SUBSTITUTE(SUBSTITUTE(A2:A4,"&","%26")," ","%20"))

This version only handles those two characters, so a + sign still turns into a space, and the phone number in A3 would still lose its plus sign. Use it only when your data doesn’t have a + sign.

Important: A QR code can look fine in the cell and still scan as the wrong text. So scan a few of your codes with your phone before you print or share them.

Using VBA Code to Generate QR Codes With a Custom Function

If you do not have the IMAGE function in your Excel version and you want to generate QR codes in bulk, the next best thing would be to use a simple VBA code to create your own function to do this.

This code uses the EncodeURL function, which needs Excel 2013 or later on Windows. If you’re on a Mac version that doesn’t have the IMAGE function, use the add-in or the website method covered below instead.

Below is the VBA code that creates a user-defined function called GETQRCODES, which can be used within a cell, and it would instantly generate the QR code for the given data point.

'Code by Sumit Bansal from https://trumpexcel.com
Function GETQRCODES(QrCodeValues As String)
Dim URL As String
Dim CellValues As Range
Dim QrPicture As Shape
Set CellValues = Application.Caller
URL = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&qzone=2&data=" & WorksheetFunction.EncodeURL(QrCodeValues)
On Error Resume Next
CellValues.Parent.Shapes("Generated_QR_CODES_" & CellValues.Address(False, False)).Delete
On Error GoTo 0
If Len(QrCodeValues) = 0 Then
GETQRCODES = ""
Exit Function
End If
Set QrPicture = CellValues.Parent.Shapes.AddPicture(URL, msoFalse, msoTrue, CellValues.Left + 2, CellValues.Top + 2, -1, -1)
QrPicture.Name = "Generated_QR_CODES_" & CellValues.Address(False, False)
GETQRCODES = ""
End Function

'Code by Sumit Bansal from https://trumpexcel.com
Function GETQRCODES(QrCodeValues As String)
Dim URL As String
Dim CellValues As Range
Dim QrPicture As Shape
Set CellValues = Application.Caller
URL = "https://api.qrserver.com/v1/create-qr-code/?size=150x150&qzone=2&data=" & WorksheetFunction.EncodeURL(QrCodeValues)
On Error Resume Next
CellValues.Parent.Shapes("Generated_QR_CODES_" & CellValues.Address(False, False)).Delete
On Error GoTo 0
If Len(QrCodeValues) = 0 Then
GETQRCODES = ""
Exit Function
End If
Set QrPicture = CellValues.Parent.Shapes.AddPicture(URL, msoFalse, msoTrue, CellValues.Left + 2, CellValues.Top + 2, -1, -1)
QrPicture.Name = "Generated_QR_CODES_" & CellValues.Address(False, False)
GETQRCODES = ""
End Function

Let me show you how to use this code.

Below is the data set where I have some URLs in column A, and I want to generate the QR codes in column B using the above VBA code.

Here are the steps to do this:

1. Open the VB editor by clicking the Developer tab and then the Visual Basic icon. You can also use the keyboard shortcut ALT + F11 to open the VB editor.

2. Click on the Insert option in the menu, and then click on the Module option. This will insert a new module for the workbook where we are going to copy and paste our VBA code.

3. Copy and paste the above VBA code to the module code window.

4. Go back to the Excel worksheet and enter the following formula in cell B2:

=GETQRCODES(A2)

As soon as you enter this formula and hit the enter key, it is going to take a few seconds and then give you the QR code image right there above the cell where you entered the formula.

Resize the cell to fit the QR code in the cell.

5. Copy this formula for the other cells in column B to generate the QR codes for the remaining cells.

The above VBA code uses a QR Server website API URL (https://api.qrserver.com/v1/create-qr-code/?size=150x150&qzone=2&data=) to create the QR code for each cell. It also encodes your data first, for the same reason the IMAGE formula uses ENCODEURL.

The custom formula GETQRCODES that we have created returns no value except the QR code image. This function takes one cell at a time, which is why you copy it down instead of letting it spill.

Note: Unlike the IMAGE function, the QR code image that you get using the VBA code is like a shape/object that floats over your worksheet (and is not embedded into the cell in which the formula is used).

So once you have the QR code image, you need to resize the cell and make space so that the QR code can be placed in the cell.

Because the picture floats, it stays on the sheet when you delete the formula, so you will have to select the picture and delete it yourself. Clearing the cell in column A is different, as the function removes that picture for you.

You need an internet connection to create the pictures, and your data goes to the same QR server website, so the same caution about sensitive data applies here.

Once created, the pictures are saved in the workbook, so you don’t need an internet connection to see them later.

Important: Save your workbook as a macro-enabled file (.xlsm), or the code will be gone when you close it. If macros are blocked in a downloaded file, right-click the file, choose Properties, select the Unblock checkbox, and click OK.

Using a Third-Party Add-in (QR4Office)

You can also take advantage of some third-party add-ins that can be used to create QR codes in Excel.

This method is suitable in cases where you only have a few data points for which you want to create the QR code (as you need to create the QR code one by one for each cell).

Below, I have the same data set where I have some URLs in column A, and I want to create the QR codes for them in column B.

Here are the steps to first install an add-in and then use that add-in to create the QR codes:

1. Click the Developer tab. If you do not see the Developer tab, click here to read how to get the Developer tab in your Excel ribbon.

2. Click on the Add-ins option. This will open the Office Add-ins dialog box.

3. Click on the ‘STORE’ option.

4. Enter the term ‘QR Code’ in the search box and then hit the enter key. This will show you all the add-ins that can be used to make QR codes in Excel.

5. In the list, spot the QR4Office add-in and click on the Add button.

6. Click on the Continue button in the dialog box that opens. This will install this add-in in your Excel and also open the QR4Office pane on the right side.

7. In the ‘Enter the URL/text you’d like to encode:’ drop-down, select the https:// option. You can make the selection based on the data type that you have. In this case, because I have URLs, I have selected the https:// option.

8. Copy the first URL in column A and paste it in the box next to the drop-down. Then delete the https:// part from the box, because the drop-down already adds it.

9. [Optional] Specify the color, background color, or size of the QR code.
10. Click on Insert.

The above steps would insert the QR code for the URL in the worksheet.

Once you have the QR code, you can resize the cells and place the QR code in column B.

Now you need to repeat the same process for all the other URLs.

One obvious drawback of this method is that you need to generate these QR codes one by one, which may not be the most efficient method in case you have a lot of URLs.

You may also come across QR code fonts when you look for add-ins.

A font alone can’t create a QR code, because the pattern has to be calculated from your data first, so before you pay for one, check that it comes with a tool that does this calculation (it’s called an encoder).

Also read: How to Create and Use an Excel Add-in

Using an External QR Code Website

If you do not want to add a third-party add-in into your Excel file, or you do not have permission to do that, another method you can use is by creating QR codes in an external website and then importing them into Excel.

There are many different websites you can use to create QR codes, and the one that I will show you in this tutorial is QR Code Generator.

Just like the add-in method, here also you will have to create these QR codes one by one.

Below I have the same data set where I want to create the QR code for the given URLs in column B.

Here are the steps to do this:

1. Open a browser and navigate to the URL https://www.qr-code-generator.com/
2. Return to the Excel sheet and copy the first URL for which you want to generate the QR code.
3. Return to the QR code generator website, select the Website option, and paste the copied URL in the box. You’ll see that the QR code has instantly been generated and is being shown on the right.

4. [Optional] Make the customization, such as selecting the frame or changing the shape or color of the QR code.

5. Click the Download JPG button. This will download the image of the generated QR code on your system. The free download is a JPG, and the site asks you to sign up for PNG or SVG.

6. Return to Excel, click the Insert tab, and then click on the Pictures option. If you see Place over Cells, click on it. Then click on the ‘This Device’ option (if the Insert Picture dialog box opens right away, go to the next step).

7. In the ‘Insert Picture’ dialog box that opens, locate the file, select it, and then click on the Insert button.
8. Resize the cells and place the QR code image in the cell in column B.

Pro Tip: If your Excel version has it, select the picture, go to the Picture Format tab, and click on Place in Cell. The QR code then sits inside the cell, so it moves and resizes with the cell like the IMAGE function result.

You need to repeat these steps for all the data points for which you want to generate the QR code and insert the image in Excel.

Since you need to create these QR codes one by one, this method is suited when you have a few data points only. In case you have a large data set, it is better to use the VBA method.

In this article, I’ve shown you four ways you can use to generate QR codes in Excel.

If you are using Excel with Microsoft 365 or Excel 2024, the best way would be to use the IMAGE function.

In case you do not have access to the IMAGE function and you want to generate these QR codes in bulk, you can use the VBA method.

And in case you want to generate these only for a few cells then you can use the add-in or the external website method.

Other Excel articles you may also like:

• Quickly Generate Military Alphabet Code for a Word in Excel
• How to Insert a Picture in Excel Comment
• How to do a Picture Lookup in Excel
• How to Insert/Draw a Line in Excel (Straight Line, Arrows, Connectors)
• How to Create a Heat Map in Excel
• FREE QR Code Generator