Monday, February 26, 2007

UPC Barcode Generator

Here's a recent $50 Project I completed for a client: a simple, straightforward UPC barcode image generator. Now, many of you might be groaning, thinking, "Come on, Excel_Geek, there are hundreds of barcode add-ins and software packages out there that work with Excel." And you're right. What's wrong with them? Nothing really. This client simply wanted his own ultra-simple Excel-based solution -- something he could input a number into and get an image he could use.

Here's a look at what I built:

(Click on the image above to open it larger in another window.)

How's it work? Basically I created a range containing cells with very narrow column widths and used conditional formatting to change the background color of the cells to either black or white, depending upon the value in the cells (either "TRUE" or "FALSE") which I obtained using VLOOKUP formulas to grab the appropriate value for each digit in the code from a table containing them.

Then once the "live" version of the barcode is set, I use Excel's little known "camera" feature (copy a range then hold down shift when selecting the Edit menu and you'll find it -- this feature is gone in Excel 12 :-( ) to copy the range containing the barcode and paste it as a copy-pastable image.

It was remarkably simple once I understood how UPC barcodes work.

To my Excel_Geek Insiders (who'll be receiving this file soon as part of your subscription), unhide the hidden rows if you really want to to see the "guts" of how this works.




Anonymous said...

Very cool, for so many different reasons. This is the type of thing most people would never consider to be within Excel's capabilities. Also thanks for linking to how bar codes work.

Excel_Geek said...

And this is way on the manual side, still. Using this as the basis, one could create a system that automatically creates barcode images to use in mail merge applications en mass. For example, each afternoon, an e-tailer might run a script that dumps all their SKUs that sold that morning, and print out all the invoices, including UPC codes, or mailing labels, or packing slips, etc.

My rule of thumb: If in doubt, it can probably be done in Excel.

Unknown said...

Camera tool is not gone in Excel 12. It can be found under Excel Options -> Customize -> 'Commands Not in the Ribbon'.

-Mike Alexander

Unknown said...

Camera tool is not gone in Excel 12. It can be found under Excel Options -> Customize -> 'Commands Not in the Ribbon'.

-Mike Alexander

Excel_Geek said...

Thanks, Mike! Must admit, still not really using 12. I'll get into it though...

Lisa Jones said...

Very easy to use and the tutorials were great. Thanks!create a barcode