In Excel, we can create QR codes in bulk
for free for use in any application using a formula. Unlike Google Sheets, you can
simply copy the image and paste it where you need it. QR codes can be used
to open URLs, store mobile tickets, contactless payments, even obituaries
on headstones, to name a few. Because they're simply a graphical representation
of alphanumeric strings, they don't expire. Now, there are a couple of ways you can create
QR codes in Excel. We'll start with the IMAGE function technique, which is available with Microsoft
365, and then I'll show you an alternate way, which allows you to create colored QR codes
and works in all versions of Excel. At the end, I'm going to answer some frequently
asked questions, so stay tuned. The trick with the IMAGE function is
to use Google's QR code generator API, which you can find on their developer site.
Now, to save you having to remember where to find this API and how to write the formula,
I've created an Excel file you can download from the link in the video description,
with the formulas ready to go. But let me step through how it works, so you
know how to modify it for your needs. To demonstrate, I'll create QR codes
for this list of courses. But first, let's take a closer look at Google's QR
code API. It's made up of the root URL, and then three required parameters,
which specify that it's a QR code, the width and height you want the QR code, and
the alphanumeric string, binary bytes of data, or Kanji. There's also a list of optional parameters,
which I'll let you explore in your own time. So, back in Excel, I'll use the IMAGE
function to fetch the QR code. Then, in double quotes, I'll paste in the Google
API URL plus the three required parameters, which I've already copied to my clipboard, and
then close the double quotes. Notice, I've set the size to 150 by 150, and you can change it here,
but it's easy to introduce a typo. So, I'll show you another way we can have the size as an input
parameter that's unbreakable later in the video. Next, I need to concatenate the URL on the end
using the ampersand and the cell reference C3. Now, I could leave it there, but with the IMAGE
function, we can also return some alt text, which I'll make the URL. Next, I can
choose the sizing. I recommend two so that it retains its original size. Lastly, the
IMAGE function also allows you to resize images, but I don't recommend it because you could end
up with a blurry QR code. So, I'll press Enter, and because this list is in a table, it
automatically copies the formula down, and voila! Now, you may need to resize the cells to display
the QR code properly. If you create QR codes regularly or you want to hand this task off to
someone less experienced with Excel, then you can create a custom Lambda function. That way,
you don't need to remember the Google API URL, and less experienced users won't accidentally
break the formulas. It might sound scary, but it's pretty straightforward, and
you'll find the finished function in the file you can download from
the video description. But first, let me show you how it's constructed, in
case you want to make some modifications. I'll use the advanced formula environment
to write the Lambda, because it has more space. It's a free add-in. Now, if you don't
already have it, go to the Home tab, Add-ins, plus more Add-ins, and search for Excel Labs,
and then add it. Then you'll find it on the Home tab. Open Excel Labs and go to the advanced
formula environment. Here, on the names tab, click the plus icon to create a new function.
So, my function name will be fn_GetQRCode. Now, it's a good idea to differentiate custom
functions from built-in functions by prefixing them with FN or the Lambda symbol.
I've also used some lowercase letters, which you never see in built-in functions.
Shout out to Peter Bartholomew for this tip. Next, the description pops up as a tooltip when you
use the function, so I'll put the syntax in here: fn_GetQRCode. Now, my formula will be used to
create QR codes for URLs, so the first argument will be URL for QR, and I'm going to allow my
users to specify the size that they want. So, the next argument will be the size of
the QR. Then, I just list the arguments, URL and size. I'll make the pane wider, so we
have more room, and now I can define the function, which is based on the IMAGE function that
I used earlier. So, I'm just going to paste it in, and then replace the hardcoded
parameters with my variable names. So, we need to terminate the start of the
URL with a double quote and then concatenate the variable name, then another
ampersand, and a double quote around the X, and repeat that for the next size parameter. Then, I'll replace both references to the course
URL with the URL variable, and click done. Okay, let's test it out. I can use it just
like a regular function. The first argument is the URL I want the QR code for, and then
the size. And I'm done. I think you'll agree, this will be much easier for Excel beginners. If you want to copy the Lambda to other files, simply copy the sheet that the Lambda is in to
a new file, and then you can delete the sheet, and it will still be in the name manager for
use in that new file. If you like the way we can simplify tasks in Excel with formulas
like this, and you want to master more, check out my Advanced Excel Formulas course.
You'll find the link in the video description. Now, the nice thing about the formula-based QR
codes is they will update if you change the text in the cell they reference. Another way we can
create QR codes in Excel is with a free add-in, which is available in all currently supported
Excel versions. Plus, we can create colored QR codes, which you can't do with the IMAGE
function technique. To get the add-in, go to the Home tab, Add-ins, get Add-ins,
search for QR for Office, and press Add. Then, access the add-in from the Home tab, Add-ins,
My Add-ins, and select it. The add-in opens the task pane on the right. I'm just going to drag
it over closer to the list of URLs, and all we need to do now is select the cell containing the
text or URL that you want converted to a QR code, and then click the copy icon. It detects the type
of text you're encoding, but you can change it from the dropdown. Then, select the color you
want. You can also select a background color, drag the slider to alter the size. I won't
make it too big. And you can change the error correction settings before inserting it.
Check the preview, and when you're happy, insert it. Now, this results in an image object
that you can move around or cut and paste into other apps. The downside of this method
is you have to generate them one by one, and if you have changes to the URL or alphanumeric
string, then you have to recreate them manually. Now, if it's color QR codes you want, you can
copy and paste ones created using the image function as a picture. First, remove any cell
formats, because these will also be copied. I'll make the table formatting plain, and then copy
the cell containing the QR code, and paste it as a linked picture. This way, if the original QR
code changes, it will reflect those changes. Then, on the Picture Format tab, under Color, choose
a new color from the recolor gallery or choose one from the palette. And now, I have a QR code
image that I can copy and paste to other apps. The maximum size you can make the QRs
with the QR code add-in appears to be 375, although I have had different results depending on the URL that I'm encoding. And with
the IMAGE function, it's 547. However, you can zoom in on the page to make it bigger
without much loss of resolution, which is good. You may notice that Google have deprecated
their static charts, which the QR code is one of, way back in 2012. Given that it's over a decade
later, it doesn't appear that they're planning on removing the API. But even if they do, any QR
codes you've created with it will still work. Of the three options, I think the Lambda
function is the best. If you'd like to master the Lambda function to
streamline more of your work, check out this video next. And if
you like this video, don't forget to like and subscribe for more. Thanks for
watching. I'll see you in the next video.
