Excel

How to create barcode in Excel without VBA

Browsing through one of the online forums I noticed someone asking “How to create barcodes in Excel?”. Among many answers he got, the most popular were:

  • install additional font(s)
  • use conditional formatting
  • use VBA
  • use Word mailing functions to get data from Excel and generate barcodes in Word

I digged a bit into all of them and learned a few things here and there, but still was asking myself: is it possible to create barcodes in Excel without VBA, without installing fonts, without passing through other apps (eg. Word)?

And the answer is: YES, we can create barcodes directly in Excel, using only built-in features, but that requires some preparation.

Here is how.

0. What you need to know to proceed

  • Functions: index+match or vlookup, mid, value
  • Charts: how to create and edit charts
  • Shapes: how to insert and edit Shapes (option)
  • barcode construction – I’m using simplified UPC-A, but this method can be applied to any barcode that can be represented in binary format
  • attention: in some languages comma (,) is used to separate the functions’ parameters, in others it’s semicolon (;). All my functions are with semicolon as the parameters separator.
  • this is intermediate level, no basic Excel explanations are provided in the text below. Despite the long reading – it’s really easy to prepare and use. I’m using Excel 2013.

1. Preparation

For my test I decided to use simplified UPC-A code, that normally consists of 12 digits – I took only 10 (left and right groups of 5). Adding the other 2 shouldn’t be a problem.

I created a new Excel sheet that I called “Barcode data”, in which I put the binary representation of 10 digits (0-9):

I also named the two ranges of data, accordingly: “data_leftcode” and “data_rightcode” for ease of use with VLOOKUP function (that was my first solution). Not needed if going with INDEX+MATCH.

2. Converting numbers into binary representation

Having this sheet I can easily convert the numbers provided by the user into their UPC-A binary representation. For that, I have another Excel sheet, in which I’m going to perform all the calculations and display the barcode.

So in one cell I let the user enter the left part of the code, let’s say in A6, and in another cell, for example A8, I let the user enter the right part of the code; both of them consist of 5 digits.

Now comes the hard part: we need to take both numbers, digit by digit, and convert them into their binary representation using the “Barcode data” sheet.

To do that, in my 3rd row I have 5 groups of numbers, each of them corresponding to 1 digit from the user provided #left code. Please notice, that both #left code and #right code cells are formatted as text, so user can put 00001 or 00000.

In each cell with 0 or 1 in the 3rd row, there is a formula built on various functions:

  • we need to extract the number at position 1 (then 2 for the 2nd group, etc): the function to use is MID (STXT in French). In my case, for the first digit it will be: MID($A$6;1;1) which translates to: in the value in cell A6 go to position 1 and extract 1 character (in this case this function returns “0”). This value we will use to seek values in the “Barcode data”. This function is the same for the first 7 digits of the binary representation for the first digit of the code (marked with square and the blue zero in the picture above).
  • if we extract 1 character from the text – it will be also a text (even if it’s a digit). That may raise some issues with VLOOKUP or MATCH function later, because in the tables that we prepared in step 1 – we have number values in cells. So it’s a good idea to convert our character to number, using function VALUE (CNUM in French); we combine both together to get this: VALUE(MID($A$6;1;1))
  • with that value we can perform the search in the “Barcode data” sheet. Here is how we can do it with MATCH (EQUIV in French): MATCH(VALUE(MID($A$6;1;1));’Barcode data’!$C$2:$C$11;0) which translates to: extract character from the 5 digit code at position 1, convert it to a number, then seek its position in the values c2 to c11 on the “Barcode data” sheet. In our case the first digit of the code is 0, so its position in the selected range is 1
  • at this point we can add the INDEX function into that formula: INDEX(‘Barcode data’!E2:E11;MATCH(VALUE(MID($A$6;1;1));’Barcode data’!$C$2:$C$11;0)) that will give us the first digit of the binary representation for the character. This formula we need to copy to the 6 following cells, knowing that E2:E11 will become F2:F11, etc for the following digits of the binary representation.

At this stage we have the final formula for converting the 1st digit of the #left code provided by the user into 7 digit binary representation:

=INDEX(‘Barcode data’!E$2:E$11;MATCH(VALUE(MID($A$6;1;1));’Barcode data’!$C$2:$C$11;0))

in which E$2:E$11 are changing letters when copied to the 6 other cells.

Now, for the remaining 4 digits of the code we can use the same formula(s), except we need to change which character we are extracting from the #left code value. So the MID function for the next 7 cells is: MID($A$6;2;1), and MID($A$6;3;1) for the following ones, until MID($A$6;5;1) for the last 7 binary digits. This of course can be simplified to simply copy it all over the place, but that’s not a goal of this article.

The last step is to repeat it for the #right code cells, using the other range in “Barcode data”.

Your final result should be similar to this:

and row 3 changing accordingly when editing the yellow cells.

3. Here goes the magic: using chart as barcode

Having the binary representations of the left and the right part of the UPC-A code – we can insert the 2 charts that will be our barcodes. Which chart type can represent the line for 1 and nothing for 0? The easiest one will be Stacked histogram (French: Histogramme empilé 100%), so the bars are always the same height.

Select the first part of the data (the 35 0s and 1s) and insert the chart:

(please don’t pay attention to the first 2 lines – they’re remaining from my tests with VLOOKUP)

Obviously, we don’t need anything but the bars on the chart, so let’s delete everything else:

This looks better, but nothing like a real barcode. What we need to change is the color of the bars (black) and spacing between them, so we select the series of data, and change its look:

 

At this moment we have something, that looks like a barcode (at least its 5 digits)!

We can do the same with the right code, and arrive at the result as shown below, with 2 charts representing the 2 parts of the code:

The advantage of this solution – we can resize the charts and place them where we want, independently from the cells and their sizes! This wouldn’t be possible with the barcodes created using the conditional formatting or fonts:

4. Adding numbers

We can often see the number accompanying the barcode, either above or below. In the solutions described in this article we can place the number in the cells or we can add a Shape with a reference to the required value:

On the picture above I inserted 2 rectangular Shapes and changed their look (color, font, letter spacing). In the Formula bar you can notice, that the Shape references the cell with the value (A6 for the first Shape).

5. To do

To make it a proper UPC-A code there are still 2 digits missing and some markup. But that should be easy to do, having all the knowledge above, for example as an additional chart(s). Or it can all be combined into one big chart – possibilities are maybe not endless, but many.

6. Conclusion

This is a proof of concept, that barcodes can be created in Excel without installing additional features and without using VBA.  Even if not designed for the particular tasks – Excel is very flexible tool and can help in many activities.

Hope this article helps you.

Don’t hesitate contacting me if you have any questions.

 

Latest posts by Jakub (see all)

 

Standard

Leave a Reply