QR codes have become part of everyday life: we see them on business cards, tickets, product packaging and in business processes. They let you pass on a link or data quickly, without typing. More and more people want to automate creating QR codes right in Excel: to label data, generate tags for mailings, events or inventory.
How a QR code works
A QR code (Quick Response code) is a two-dimensional matrix barcode that holds encoded information: a link, text, a phone number and much more. It consists of many black and white squares arranged by a special algorithm, which lets scanners read the data quickly and reliably.
QR codes use error correction, so the information stays readable even if part of the image is damaged. For how it all works in detail, see this popular video from the Veritasium YouTube channel:
Adding QR codes in Excel with a formula
Since QR codes are popular, there is demand for generating them in bulk in Excel. Many articles suggest the IMAGE function, available only in recent versions of Microsoft 365. It inserts a QR code picture into the cell from an online service:
=IMAGE("https://api.qrserver.com/v1/create-qr-code/?size=150x150&data="&ENCODEURL(A1))
The ENCODEURL function matters: without it, a value with spaces, “&” or “#” breaks the request, and the QR code encodes only part of the text. Fill the formula down the column, and each row gets its own code. Increase the row height and column width to see the codes properly.
Generate QR codes in Excel in 2 clicks
This method has a few drawbacks, though:
- IMAGE is not available in Excel 2010–2021.
- Your data is sent to a third-party online service, which is not always acceptable for internal codes or personal data.
- The codes exist only while the formula works and the service is online: they disappear without internet access.
- It’s not very convenient: you have to keep the formula somewhere (you’re unlikely to remember it), remember where it is, copy it from there, and so on. Of course, you can always come back to my site, but that takes time too :)
That’s why I created a QR code procedure in my !SEMTools add-in for Excel. It works in all versions from Excel 2010 and creates QR codes in bulk for any data: numbers, text, links. Just select the column with data and run the procedure.
With the add-in, two clicks usually separate you from the solution of a task like this one, because it has hundreds of such procedures. The tool is in the SEO + PPC group, among the tools for webmasters and SEO specialists. If your data has links in text form, you may also need the procedures to open all links at once or extract URLs from hyperlinks.
Generate QR codes for a whole column of links, text or numbers in 2 clicks, in any Excel version from 2010.
!SEMTools solves this and hundreds of other tasks right in Excel!
Summary
I hope this article helped you generate QR codes in Excel and understand how they work. In Microsoft 365, IMAGE with ENCODEURL does the job with a formula. In any version from 2010, the !SEMTools procedure creates codes for a whole column in 2 clicks. Generate QR codes without limits and give your users this wonderful modern way to get useful information about your products and services!