3 Ways to Generate QR Codes in Microsoft Excel

Generate a QR Code Using a Font

Excel allows you to use custom fonts for your spreadsheets. You can read this post to learn how to install a new font in Excel. A simple 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.

Once you install the font, you will be able to convert individual characters into a QR code.

1. Select the cell range containing the text you want to transform into a QR code.
2. Go to the Home tab.
3. Select the Alphanumeric QR font from the dropdown list in the Font section.
4. Increase the font size so that the QR codes are easily visible.

Your text will now appear as a series of QR codes.

Note: This does not convert the entire text in a cell into a single QR code, but rather converts each character of the text into a QR code.

Creating a QR code for a single character is arguably a rather useless accomplishment. But do not worry, there are better ways to get a QR code for linking longer text or URLs.

Generate a QR Code Using Organization Data Types

If your organization uses Power BI, you might leverage Organization data types to create your QR codes. Data types will allow you to hold more data within a single cell. This includes images from a URL.

This means that you can use data types to display a QR code image within 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 URL above will generate a QR code image for whatever you add to the end of the <yourdatagoeshere> part.

You can use this within your Organization 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.

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

Open the Power BI desktop app and go to the Home tab and click on the Excel Workbook to import Excel data command. A file selection menu will appear, where you can access your saved Excel file. Select the file and press the Open button to begin the import process.

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

The Navigator menu will appear, where you can select which data to import from your Excel file. Select your table and press the Transform data button. You will now need to create a new column with the data from your 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.

The Custom Column editor will appear, where you can add a formula for the new column.

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

Enter the formula above into the custom column formula editor and press the OK button. This will add 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. You can now go to the Home tab and press the Close & Apply button to load the query.

You now need to set this table as a featured table.

Go to the Model view, select your table, and toggle the Featured table option at the bottom of the Properties menu.

When you enable the featured table option, the Configure this featured table. menu will appear.

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.

Now you will need to identify the custom column you added 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 Image URL. You can now publish the Power BI template to the online Power BI service. This will make the organization data type available in Excel.

Go to the Home tab and click on the Publish command, then choose the location where you want to publish your template and press the Select button.

You can now create organization data types from your Excel data. Be sure to close and reopen Excel if you do not immediately see the featured table in your Data Types.

You will now 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 the button to expand the Data type section.
4. Click on your table under the From your organization section.

The text will be converted into a data type, and you will be able to recognize it by 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 formula above will get the QR Code from the data type in cell B2.

Generate a QR Code Using the IMAGE Function

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

Fortunately, there are other ways to get an image within a cell. This method will use the IMAGE function along with the QR code API to generate a QR code image within 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 to the end of the QR code API URL. This will create a QR code for the value in cell B3.

Conclusion

QR codes are a popular way to create scannable hyperlinks, and you can now easily create them in Excel. Using a custom QR code font will allow you to generate a QR code for only a single character, which may not be that useful.

Organization data types used with a QR code API will allow you to create a QR code for any text data or URL you need, but it requires 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.

Pubblicato in Excel