3 Ways to Generate a QR Code in Microsoft Excel

Do you want to generate a QR code in Excel?

QR codes are two-dimensional barcodes that can be read by scanners and smartphones.

QR codes are a popular way to link physical objects to digital information. When a QR code is scanned, it will link the user to a website, text, audio, or video file.

This post will show you how you can make QR codes in Excel. Download your copy of the example workbook used in this post and follow along.

Generate a QR Code with a Font

Excel allows you to use your own custom fonts for your spreadsheets. You can read this post to find out how to install a new font in Excel.

An easy way to create a QR code in Excel is to use a custom QR code font!

The AlphanumericQR Font found on FontSpace is a great option for a QR code font.

When you install the font, you’ll be able to convert individual characters to a QR code.

  1. Select the range of cells that contains the text you want to turn into a QR code.
  2. Go to the Home tab.
  3. Select the AlphanumericQR font from the dropdown list in the Font section.
  4. Increase the font size so the QR codes are easily visible.

Your text will now display as a series of QR codes!

📝 Note: This doesn’t convert the whole text in a cell to a single QR code, but rather it will convert each character of the text to a QR code.

Making a QR code for a single character is admittedly quite a useless result. But don’t worry there are better ways to get a QR code to link longer text or URLs.

Generate a QR Code with Organization Data Types

If your organization uses Power Bi, then you might avail of the Organization Data Types to create your QR codes.

The data types will allow you to contain multiple pieces of data inside a single cell. This includes images from a URL.

This means you can use data types to show an image of a QR code inside a cell, and you can generate this image from a QR Code API.

https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=<yourdatagoeshere>

The above URL will generate a QR code image for whatever you append on the end place of the <yourdatagoeshere> part.

You can use this in your Org Data Type to get QR codes in Excel.

You will need an Excel table with the data you want to use to generate your QR codes. This example will create QR codes for a list of webpages.

Now you will need to create a Power BI model with the Excel data.

Open the Power BI desktop app and go to the Home tab and click on the Excel Workbook command to import the Excel data.

This will open a file picker menu where you can navigate to your saved Excel file. Select the file and press the Open button to start the import process.

📝 Note: Your Excel file needs to be closed while you are setting up the query in Power BI.

This will open the Navigator menu where you can select what data to import from your Excel file. Select the table and press the Transform Data button.

Now you will need to create a new column with the data from the Excel table appended to the end of the API URL.

Go to the Add Column tab and choose the Custom Column option in the General section.

This will open the Custom Column editor where you can add a formula for the new column.

= "https://api.qrserver.com/v1/create-qr-code/?size=150x150&data=" & [URL]

Insert the above formula into the Custom column formula editor and press the OK button.

This will append the URL column to the end of the API URL for each row in the table. This is what will be used to generate each QR code.

Now you can go to the Home tab and press the Close and Apply button to load the query.

Now you will need to set this table as a featured table.

Go to the Model view, select the table, and toggle on the Is featured table option in the bottom part of the Properties menu.

When you enable the featured table option, this will open the Set up this featured table menu.

Set the Row label and Key column.

  • The Row label is the value you will see in the Excel cell for the data type.
  • The Key column is a value that uniquely identifies a row in the table.

In this example, they have both been set to the URL column.

Now you will need to identify the added custom column as an image URL.

Go to the Data view and click on the custom column containing the QR Code URL and set the Data category as an Image URL.

Now you can publish the Power BI model to the Power BI service online. This will make the organization data types available in Excel.

Go to the Home tab and click on the Publish command, then choose the location to publish the model and press the Select button.

Now you can create organization data types from your Excel data. Make sure to close and reopen Excel if you don’t immediately see the featured table in your Data Types.

Now you will be able to convert text data from your table into a data type.

  1. Select the cells to convert into an organization data type.
  2. Go to the Data tab.
  3. Click on the button to expand the Data Type tray.
  4. Click on your table in the From your organization section.

The text will be converted to a data type and you will be able to tell because of the small icon to the left of the text.

= B2.[QR Code]

You can now extract the QR code image from the data type with a formula. The above formula will get the QR Code from your data type in cell B2.

Generate a QR Code with the IMAGE Function

Using the organization data types can be a bit complex to set up, and you might not even have access to the Power BI pro license that’s required.

Fortunately, there are other ways to get an image inside a cell.

This method will use the IMAGE function along with the QR Code API to generate a QR code image inside a cell.

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

The above function will append the value in cell B3 onto the end of the QR code API URL. This will create a QR code for the value in cell B3.

Conclusions

QR codes are a popular way to create scannable hyperlinks and now you can easily make them in Excel.

Using a custom QR code font will only allow you to generate a QR code for a single character, which might not be that useful.

Organization data types used with a QR code API will allow you to create a QR code for any text or URL data you need, but it does require a Power BI pro license and is complex to set up.

The easiest option is to generate a QR code using the IMAGE function along with the QR code API.

Did you know you could make your own QR codes in Excel?

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and qualified actuary with over 15 years of experience. He has worked in a variety of industries, including insurance, ad tech, and most recently Power Platform consulting. He is a keen problem solver and has a passion for using technology to make businesses more efficient.

Related Posts

Comments

5 Comments

  1. Frank Panipinto

    Hi John. Thanks for the article. Is there any way that I can make a QR code clickable? I have a list of URLS that will take me to an online map. Some are accompanied by QR codes that will do the same thing. The QR codes I generate do NOT link to the URL except by using a QR reader. I have searched quite a bit and have not found anything.

    • John MacDougall

      Unfortunately, last time I tried the HYPERLINK function doesn’t work with the IMAGE function.

  2. Patrick

    Hi John,

    First of all, I’d like to thank you so much for those solutions. For what I’m working for, the API solution from qrserver is quite the best option for me. However it works 1 out of 2 times.

    That’s because I’m running a macro that takes a serial number, past it into a cell and print it automatically out with the QR code. As I have about 8 QR code to generate each time, I think that the macros are running faster than the API so the QR code cannot be generated and some of them are giving back an error when printing them out.

    Do you know if my thoughts are right or do you think it might be another issue? If I’m right, would you recommend smth in VBA to slow down the macro before printing?

    Thank you so much for your help.

    • John MacDougall

      Yes, usually API’s will have some limit to number of calls per period of time.

      You can try adding a wait before each call Application.Wait (Now + TimeValue("00:00:10"))

      • Patrick

        Hi there,

        I’d like to thank you for your prompt answer. Using a wait time isn’t the solution as the problem is always on the first label printed (out of 8) which doesn’t have the QR code. The other 7 are ok.

        How was this solved?

        To do this, I download the QR code image from the API and make a refresh of the workbook. Afterwards, I make a false print request then I start printing the first label out followed by the other 7 prints. And it works.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃