How to create barcodes in Excel that WORK!


Today we’re going to
do something different. We’re going to create barcodes in Excel, specifically the UPC-A barcode version which is a very common barcode. It has 12 numeric digits
which are uniquely assigned to each trade item. We’ve all seen barcodes on
the products that we buy. Did you ever imagine
creating them in Excel? Well, Pete did and he actually did it. Now, some time ago, Pete sent me a message that went something like this. (upbeat music) Now, I know some are going
to say, there are programs that are meant for this,
Excel isn’t one of them. We agree, here’s what
Pete said about this. (upbeat music) So let’s have some fun. Let’s create barcodes
and practice some VBA. (upbeat music) Here it is, here is Pete’s automatic
barcode generator in Excel. All you have to do is type in the barcode which is split to one digit for type, five digit for manufacturer
and five digits for product and this entire barcode here will be automatically generated. We can test this quickly. Let’s take a look at this area right here and this number right here,
I’m going to change the type to a one (talks), this changes. Now let’s test this for
manufacturer as well, just keep an eye on this area
where the four is underneath, I’m going to change the
first digit to a three and notice that this
changed automatically. Now I had no clue about
how barcodes are generated so I took a look at the reference image that Pete had included in the file and I also did some research online. This may look a bit complex
but it’s actually quite simple. So that type digit that
we input is basically the type of barcode. Then we have the five
digit manufacturer code and the five digit product code. There is something at
the end of the barcode which is called the modulo check and that was generated
automatically, we didn’t input that. The formula for it is actually right here. It takes a combination of these numbers, it does a calculation and
then it subtracts the result of this formula from the
next highest multiple of 10. So in this example, the result
of the calculation is 23 so 30 minus 23 is seven and
that’s the modulo check. So basically, this is driven by a formula. Now how are these lines,
these black lines generated? They’re generated from these numbers. So each number here has
a set of binary codes, so zero and ones that
are associated with it. So for example, if you
take a look at the five, the five consists of
this set of binary codes, you can picture it this
way, each digit here reflects a column in Excel. A zero is an empty column
and a one is a black column. So now if you take a look at the five here and we see this set of binary
code written right here, underneath it, the zero is an
empty column, a white column and a one is a black column. Then we have three zeros, so
empty columns and then a one. Each number has its own set, so a one here when we look up here, it has
this set of binary codes. So the black columns
are in different places. We have a similar pattern
for the right side codes. They have their own sets of binary codes. In addition to this, we
have a standard layout for the right guard, center
guard and the left guard. Now how did Pete manage to
create this barcode in Excel from these numbers? Well before I show this to
you, we need to make sure that it works. So let’s take an actual product,
let’s put in the barcode of it in here, let’s scan
it to see if it works. So here I have a bottle of
honey Chipotle barbecue sauce. On the back here, I have the barcode. Now the first digit, that’s the type so I’m just going to put
it in Excel, it’s a zero. Then I have the five digit
manufacturer code which is 13409 and then I have the five digit
product code which is 51519. Then notice that the modulo is an eight, let’s take a look at our file here, the modulo is also an eight. Now I’m going to bring up my
phone to scan the barcode. It works, it says honey
Chipotle barbecue sauce, the ingredients are listed here. High fructose corn syrup,
that’s the first ingredient, doesn’t sound so healthy
but I already finished it. Well, the good news is that
our barcode reader works. Now let’s take a look
at how this is set up. First off, let’s take
a look at the numbers on the bottom of the barcode. Well, this one is simple,
it’s a direct reference to a name cell which is called type number which is this cell right here. This changes, this is
going to change as well. Next, we have a five
digit manufacturer code. Now notice that each digit here is sitting in its own set of merge cells. To get this done properly,
Pete has used a combination of the left function together
with the mid function to get the other digits out separately. And the same applies to the
five digit product code. Now for the modulo check, it’s referencing a named range called modulo
check which is actually sitting in a hidden cell that’s right here. So I’m just going to unhide
this area and we can see that modulo check cell
is sitting right here and this is the formula behind it. So we have the mod function to get the next highest
multiple of 10 and then Pete has plugged in the formula
that we saw right here, that’s going to give us
the modulo check number. Now what about the bars here? Well, notice that the
columns are really narrow and each column is either
black or it’s white. So this is following the binary digits. So if it’s zero, it’s white,
if it’s one, it’s black. Now for the left guard, these are fixed so is the center guard
and the right guard. The other ones have digits behind them. Now there is no formula
here because this part is generated with VBA but
before I show you the VBA code, let’s just double check this. If I take this image, I’m just
going to copy and paste it here and then crop it for the left
code just so we can follow how this works. So type is the first part of
the barcode, our type is zero so it has this set of binary codes. So notice the first three
columns have to be empty and then we have two
black columns, then empty and then black, it’s exactly
what we see right here, three empty, two filled,
one empty, one black. And then if you take a
look at the first digit of the manufacturer, it’s a
one and the one has this set of binary codes where the
first two columns are empty and then we have two fields columns. The next one is a three
and if you look down here, that’s how a three looks, that’s what it translates to, right? So that’s how you can read this. Now, let’s quickly take a look at the VBA. So I’m going to press
Alt + F11, we notice that there is one module in
here, it looks like this, I’m just going to scroll to
the bottom so you get an idea of how long this is. Now this is written in a
very nice and simple way. What I like about it is that
it’s super easy to follow. So what Pete has done here is
he’s created different sets of array variables to keep those
left codes and right codes, basically those binary
codes that we saw before. So he’s keeping them in
variables and then what he does is he loops through each
of the digits here to check which of these binary codes
apply to it and then he keeps it in memory in an array. So if you’re curious to find
out more about VBA arrays, I have a separate video on
this and I’m going to add it to the description of the
video and to the cards. After he keeps this in array,
he does the loop a few times to run through all the
digits of the code and then in the end, he just copies
and pastes the array that he creates to the Excel file. So one’s for the left array
which is the manufacturer codes and the second time for the right array which is the product code. Now these are names in Excel. So if I actually go here
and select rightArray, notice that we see everything
after the center guard. So it’s basically the sets of
zeros and ones that is kept in memory in VBA is just
pasted right back in. But after we paste in zeros and ones, how come we get these colors? Well these colors are actually created with conditional formatting. So if I just go to conditional
formatting to manage rules, notice that we have a simple
formula, if it’s a one, the cell should be black, if it’s a zero, it shouldn’t have a color. And the cells, the height of
the cell is made long enough to look like a bar. Now one last tweak that Pete did to this is to make this dynamic, right? So every time we change
this, we don’t have to click a button to execute or
to refresh the code, it does it automatically
and that’s because there is a selection change
procedure behind this code. So if I go back to VBA, with Alt + F11, the sheet here is called
barcode, that’s the sheet, its code name is source. There is a worksheet
selection change procedure so that every time we change a number, only in these three cells, the
CreateBarcode sub-procedure which was in module one
here is going to run. If we don’t change any of these cells, the macro is not triggered. If you would like to take
a closer look at the code, feel free to download the
workbook and go through it at your own pace. That’s it, that’s how you
can create barcodes in Excel. If you’d like to take a look at the file, you can download it from
the link in the description, Pete was very kind to share this with you. Let’s give a thumbs up to thank Pete. Thank you for dropping by
today and if you’re new, consider subscribing before you leave. (upbeat music)