In this article we’ll focus on the most popular barcode format: the UPC-A barcode wich is a series of 12 numbers you see on products in stores.

It’s mainly used for selling things in stores and for keeping track of items in bulk. This barcode tells you who made the product and what the product is, making it easier for stores to manage their items and for you to check out at the register.

## What is a Barcode

A barcode is a series of black stripes and white spaces you see on almost everything you buy. These stripes are a special code that computers can read with a scanner. Barcodes made it much easier and faster to check out items at grocery stores, and now, we use them everywhere to keep track of products. When you scan an item with a barcode scanner, the computer instantly knows what it is and how much it costs.

You can easily create your own using Excel. Before we dive into how to do that, let’s demystify what makes up a barcode. Understanding its parts will not only satisfy your curiosity but also empower you to use the barcode generator Excel template.

### The Guards (the edges)

Think of these as the bookends of a barcode. They don’t change, no matter what the barcode is for. They help a scanner figure out where the barcode starts and ends.

### Barcode Type

At the beginning of the barcode, there’s a number that tells you what kind of barcode it is.

For example:

• 0 is for regular items you buy
• 2 is for tings sold by weight, like fruit
• 3 is for medicine
• 5 is for coupons

### Manufacturer Code

After that, the first 5 numbers tell you who made the product.

### Product Code

The next 5 numbers are specific to what the product is.

### Check Character

The very last number is a special check to make sure the barcode is read correctly. It’s made using a formula that adds up and multiplies certain numbers from the barcode.

It ensures the scanner reads everything right. It’s called the modulo check character.

Here’s the formula to figure out the check digit:

• Start with the Odd Ones: First, look at all the numbers in odd spots (like 1st, 3rd, 5th, etc.). Add them all up and then triple that total.
• Then the Even Ones: Now, take the numbers in even positions (2nd, 4th, 6th, and so on, but not the very last number). Add these to the total you got from the odd numbers.
• Divide and Conquer: Take the big number you’ve got and divide it by 10. The remainder (the bit left over) is what we’re interested in.
• Final Touch: If you got a remainder, subtract it from 10. The number you’re left with? That’s your check digit. If there was no remainder, the check digit is 0.

Let’s calculate the check digit for a barcode example: 59006765011. It’s easier than it sounds, I promise!

• First, the Odd Positions: Look at the numbers in odd spots (1st, 3rd, 5th, etc.), which are 5, 0, 6, 6, 0, 1. When we add these up, we get 18. Multiply that by 3, and we have 54.
• Next, the Even Positions: Now, for the numbers in even spots (but not the last one), which are 9, 0, 7, 5, 1. These add up to 22. Add this to our first total, and we’re at 76.
• Divide Time: If we divide 76 by 10, the remainder is 6.
• Finding the Check Digit: Since the remainder isn’t zero, we take 10 minus 6, giving us 4. That’s our check digit!

So, the complete barcode, with the check digit, is 590067650114.

## How Barcodes Work

In a barcode, the black and white lines tell a scanner about the product and who made it.

Each number (from 0 to 9) in the barcode is shown as a unique set of 7 thin lines, or “tracks,” as we’ll call them.

Here’s the easy way to understand it: for each track, if it’s marked with a 1, it’s black, and if it’s a 0, it’s white. This means each number is actually just a specific pattern of black (1) and white (0) tracks next to each other.

The table below lists the combinations of 1’s and 0’s for the values of 0 through 9.  There is a separate set of code combinations for the left side (manufacturer) and the right side (product).

If you look closely, you’ll notice the pattern for numbers on the right side is just the opposite of the pattern on the left side.

Taking the number 9 as an example again for the manufacturer, it’s represented by the pattern 0001011. This means it starts with three white tracks (000), followed by a black one (1), another white (0), and then two black ones (11).

## Making a Barcode Generator in Excel

To create a barcode in Excel, we’re going to use some of Excel’s features to handle different parts of the barcode.

If you’re not into the nitty-gritty of how it’s done, no worries! Scroll down to find our ready-to-use barcode generator Excel template. Just enter your info, and you’ll get a perfect UPC barcode.

### Named Ranges

We’re using named ranges for referencing. For the barcode type (cell C3) the named range is called “typeNumber”.

For the manufacturer code (left set of 5 digits) the named range is called “leftNumber” (cell C5).

For the product code (right set of 5 digits) the named range is called “rightNumber” (cell C7).

### Text Functions

To create a barcode in Excel, we need to pick apart the manufacturer’s code and the product’s code digit by digit.

Each of the values in the manufacturer’s code is using a text function to extract a single character from the 5-digit value stored in “leftNumber”.

These functions are as follows (reading from left to right):

• Position 1 – LEFT(leftNumber)
• Position 2 – MID(leftNumber, 2, 1)
• Position 3 – MID(leftNumber, 3, 1)
• Position 4 – MID(leftNumber, 4, 1)
• Position 5 – RIGHT(leftNumber)

Each of the values in the product’s code is using a text function to extract a single character from the 5-digit value stored in “rightNumber”.

These functions are as follows (reading from left to right):

• Position 1 – LEFT(rightNumber)
• Position 2 – MID(rightNumber, 2, 1)
• Position 3 – MID(rightNumber, 3, 1)
• Position 4 – MID(rightNumber, 4, 1)
• Position 5 – RIGHT(rightNumber)

Even though these steps sound a bit like a spy decoding a secret message, in Excel, it’s just a matter of using three easy commands: one that grabs the first digit, another that picks digits from the middle, and a third that catches the last digit.

### Modulo Check Character Calculation

The Modulo Check Character is a bit like the final puzzle piece for our barcode. In our workbook it’s referencing a named range called “moduloCheck” (cell C9).

The formula behind the modulo check is the same as the one stated earlier:

``3 × (1 + 3 + 5 + 7 + 9 + 11) + (2 + 4 + 6 + 8 + 10)``

(Remember – the numbers in the parentheses represent number positions as read from left to right)

When translated into Excel, the formula looks like so…

``=MOD(10 - MOD((3 * (B3 + MID(B5, 2, 1) + MID(B5, 4, 1) + LEFT(B7) + MID(B7, 3, 1) + RIGHT(B7)) + (LEFT(B5) + MID(B5, 3, 1) + RIGHT(B5) + MID(B7, 2, 1) + MID(B7, 4, 1))), 10), 10)``

It might sound complicated, but it’s all about breaking it down into these steps and letting Excel do the heavy lifting:

1. Individual number extractions using the LEFT, MID, and RIGHT functions
2. Add the left 5 and right 5 values
3. Multiply the left result by 3
4. Add the result to the right side
5. Use a modulo function to divide the result by 10 and retain only the remainder
6. Subtract the result from 10
7. Use a modulo function to divide the result by 10 and retain only the remainder

The reason for the second modulo function is to account for results where the remainder would be zero.  If that occurs, Step 6 would be 10 – 0 which results in 10, and we can’t return a double-digit result to the Modulo Check Character position.

### Generating the Black Vertical Bars (Conditional Formatting)

To get our barcode to look like the ones you see on products, we make the columns in Excel super narrow.

Then, we use colors to show the barcode: black for the number 1 and white for the number 0.

We use “Conditional Formatting” in Excel. This lets us set rules for how cells should look based on what’s in them.

• For our barcode, we set a rule: if a cell has a 1, it turns black; if it has a 0, it stays white.
• To see these rules, if you click on a cell in the barcode area, like cell H12, and then go to the “Home” tab, look under “Styles” for “Conditional Formatting,” and then click on “Manage Rules,” you’ll see exactly how we set it up for H12 and the other cells that make up the barcode.

Since the Left, Right, and Center guards are fixed, those are manually colored and are not dependent on Conditional Formatting.

### A Closer Look

Examining the first few digits of our barcode (5 90067 65011 4), we can consult the binary conversion chart from earlier and note the following binary patterns:

• 5 = 0110001
• 9 = 0001011
• 0 = 0001101
• 6 = 0101111

The 1/0 patterns can be seen in the sets of 7 below.

### Adding Data Validation to the Barcode Generator Excel template

To make sure everything in your barcode works perfectly, we need to be a bit picky about what you can enter into Excel:

• For the Type of Barcode: You can only pick from four options (0, 2, 3, or 5). We set up a special list in Excel that only lets you choose one of these for the barcode type.
• For Manufacturer and Product Codes: You must enter exactly 5 numbers—no more, no less. We’ve told Excel to expect exactly 5 characters for these.

But there’s a catch—just telling Excel to look for 5 characters means someone could accidentally type letters or symbols, which would mess things up. To avoid that, we go a step further:

• Making Sure It’s Numbers Only: We add an extra check for the Manufacturer and Product codes. Not only must there be exactly 5 characters, but those characters must all be numbers.

Here’s how we do that check:

• For the Manufacturer code, we set up a rule that says, “This must be 5 numbers long and only numbers.”
``=AND(LEN(leftNumber) = 5, ISNUMBER(VALUE(leftNumber)))``

For the Product code, we set up a similar rule.

``=AND(LEN(rightNumber) = 5, ISNUMBER(VALUE(rightNumber)))``