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.

>> Copy the introduction part from the News item <<


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 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 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 barcode widget. The best way to change the size of the placeholder is to edit the settings. You can also drag the corners 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.

>>Add detail TBD<<

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 4,296 alphanumeric characters, according to the specification (version 40 with correction level L). 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, 4296).
  • 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 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.
  • 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.