The QR code widget

QR Codes make it easy to store a lot of information in a small space. The code can be scanned to identify a person, a location or an object. QR codes are often used on smartphone screens.

Now you can encode any data in your spreadsheet in the form of a QR code. The QR code widget allows you to scan large amounts of information directly off a phone’s screen, from a laptop or in printed form.

Screentshot of a Eurostar train ticket from London to Paris with a QR code

QR codes are images with a two-dimensional matrix that is often scanned by or displayed in smartphones. If users see a QR code during a self-guided tour, they can scan it with their phone’s camera to get more information about a significant object, either by listening to a voice recording or by reading a web page.

Since QR codes are square, they fit the phone’s screen well both in portrait and landscape mode, which have made them popular to carry digital boarding cards or tickets like the Eurostar train ticket above. QR codes can also be used for marketing purposes and in discount coupons.

Of course, QR codes can be also used for identification purposes like any standard barcode, and were actually initially developed to track parts in vehicle manufacturing.

For QR codes, we initally support the following code formats:

  • Text – the encoded text will be input into the active input field.
  • URL – the encoded link will open in the default web browser.
  • Call – the encoded phone number will be dialed automatically by the phone.
  • E-mail – the encoded e-mail address will be used as the recipient of a new e-mail inside the default e-mail client.

Adding a QR code to your web page

The QR code widget uses two cells in the spreadsheet, one for the source data and one for the generated QR code. To create the widget, select the cell where you want the upper left-hand corner of the QR code. Open the task pane and select the Widgets tab. Scroll down to the QR code widget. Click on the icon to insert the widget into the selected cell.

The QR code format

The widget can generate the QR code in different formats. Select the format you want in the Format section of the widget properties.

Screenshot of the Format setting for the QR code widget

The source data cell

The content of the QR code will be taken from one of the cells in the spreadsheet. The source data cell can contain a static value or calculate the QR code contents with a formula. Static and calculated values can be concatenated.

To select the source data cell, click on the Select a cell button, click on the source data cell and then click OK. You can also type a cell reference into the text box and press OK.

Screenshot of the Data source setting for the QR code widget

The QR code size setting

Screenshot of the size setting for the QR code widget

A QR code is always square. The size of the QR code on the page is defined by the Size setting. The QR code in the screenshot would be generated as a 150 by 150 pixel matrix.

The QR code placeholder

When you insert a QR code widget in a cell, a placeholder appears. Each actual QR code is generated starting from the upper left-hand corner of the placeholder.

When you edit the spreadsheet in Excel, the placeholder will not reflect the current value of the source cell. In the example below, the source data is in A1, but if you scan the code below you will find that a QR code placeholder always points to our website http://www.spreadsheetconverter.com. Like most widgets, the QR code widgets are live in the converted web page, but not in Excel.

Screenshot of a spreadsheet in Excel with a QR code widget showing its placeholder

When you work with widgets that use placeholders, you will often get a better result if you merge all the cells behind the placeholder. This enables the converter to better determine the size of the widget as it will appear on the web page. In the example above, we have merged cells A3:C11 to make room for the widget.

The size of the placeholder in Excel is based on your settings for the QR code widget. The best way to change the size of the placeholder is to edit the settings. You can also drag the handles of the placeholder to adjust it. The resulting width of the placeholder will be used for both the height and width of the square QR code.

Data validation

You need to validate the data used with a QR code

The QR code widget does not validate any part of the code you provide. If the source data for a QR code is typed manually into a form, you must validate it before you use it to generate a QR code.

  • Text QR codes have a maximum length of 2,331 characters, according to the specification (version 40 Binary data with correction level M). You must ensure that the source data isn’t too long, either by testing the length of it using =LEN(qrcode) or by truncating it with =LEFT(qrcode, 2331).
  • URL QR codes contain links, usually to web pages. You must verify that the link is a valid URL.
  • Call QR codes contain a tel: link as described in https://www.ietf.org/rfc/rfc3966.txt. When dialing with digits, it is always a good habit to ensure that the source data contains the country code, or add one by default. Note that some countries allow Phonewords, i.e. letters in phone numbers; with the alphabet mapped to different digits. In the USA, dialing 1-800-FLOWERS is the same as dialing 1-800-3569377. A QR code can also be used for making Voice Over IP calls where the subscriber is identified by a character string.
  • E-mail QR codes must contain a valid e-mail address. The easiest way to ensure this is to use the special E-mail widget in the cell where the e-mail address is typed.

Apple Wallet

Screenshot of an automatically generated loyality pass for the Apple Wallet

The Apple Wallet offers a convenient way to store and use tickets, membership cards, discount coupons etc in iPhones and Android devices. Many of these passes use QR codes to identify the holder. You can generate your own Apple Wallet passes in-house, or you can use an external API provider.