Monthly Archives: February 2015

How to make sequential, coded labels with Excel & Illustrator

Claire in the artist market tent.

Claire in the artist market tent.

Each piece of artwork and merchandise for Claire’s display in the Artist Market at the Canada Winter Games in Prince George was required to have unique, coded labels. This was to help any volunteers at the checkout keep track of sales. I knew I could generate unique sequences of identifier codes using Excel and print them from a Word label template, but I wanted to include a colour logo and apply styles that might be awkward to achieve in Word.

InDesign and QuarkXPress can import Excel spreadsheet data, but I couldn’t find label templates for them. Although Illustrator doesn’t import easily from Excel, it does have label templates and its tools offer lots of precision, so I decided to work with Illustrator and Excel.

I started by creating a sequence of unique identifier codes in Excel, by making two numbered cells in a row, selecting them, then grabbing the +sign handle and dragging the selection down to create a numbered sequence as long as I wanted.

Starting a code sequence in Excel.

Starting a code sequence in Excel.

Second code entry to generate a sequence.

Second code entry to generate a sequence.

After selecting both cells, the cursor will change to a + sign when hovering over the bottom right corner.

After selecting both cells, the cursor will change to a + sign when hovering over the bottom right corner.

Pulling the + handle down starts generating a sequence of code numbers.

Pulling the + handle down starts generating a sequence of code numbers.

Then I opened a label template (Avery 5167 – 80/sheet) in Adobe Illustrator.

Avery label 5167 template imported into Illustrator: 80 labels/sheet.

Avery label 5167 template imported into Illustrator: 80 labels/sheet.

On a separate layer, I placed the Canada Winter Games logo that we were authorized to use, and the price for Claire’s art magnets we wanted to label and track. I locked these layers so I wouldn’t risk accidentally moving any of those elements, then created a new layer for the codes.

Prices and logos placed on each label.

Prices and logos placed on each label.

Illustrator layers for setting up labels.

Illustrator layers for setting up labels.

Next step was to create a text container box in Illustrator, then make 3 copies of it.

Creating a text box in Illustrator that is roughly the same height as an existing column of labels.

Creating a text box in Illustrator that is roughly the same height as an existing column of labels.

Four identical text boxes placed over the label columns.

Four identical text boxes placed over the label columns.

Back in Excel, I selected my column of sequenced code from CMK-mag-1 to CMK-mag-80, clicked Copy, moved to Illustrator, selected the first text box with my Area Type Tool, and Pasted the string of code into the box.

Importing sequential code data into a text box in Illustrator.

Importing sequential code data into a text box in Illustrator.

Of course, I needed to increase the leading so each code would land in the same position on each label.

Formatting a column of text in Illustrator to adjust font, leading, colour, etc.

Formatting a column of text in Illustrator to adjust font, leading, colour, etc.

Once that was fixed, I selected all four text boxes, went up to the Type menu, selected Threaded Text -> Create. (This is the equivalent of linking text boxes in InDesign or QuarkXPress.)

Creating threaded text boxes in Illustrator.

Creating threaded text boxes in Illustrator.

After adjusting the bottoms of a couple of text boxes, all 80 code numbers fit into the same place on their own unique labels.

All four text boxes filled and linked with sequential code.

All four text boxes filled and linked with sequential code; the third column needed to be shortened to bump its bottom entry to top of the fourth column.

I was ready to print to a Postscript laser printer.

There’s probably a way to accomplish this in Word, but this was a lot easier. Having built the file in layers, I am able to quickly build variations with additional Illustrator Pasteboards. And it was easy to select codes and prices to create  master inventory sheets in Illustrator for the checkout.

A second pasteboard added to the Illustrator file, then filled with a continuing sequence of codes.

A second pasteboard added to the Illustrator file, then filled with a continuing sequence of codes.

I now realize that I could also create custom, graphic-rich labels in InDesign and QuarkXPress by saving an Illustrator label template as a PDF, importing it into ID or Quark, then designing my labels on a separate layer above. A key challenge is to have the printer deposit its ink or toner exactly within the label perimeters, not overlapping the edges, and this process should offer the same precision as my Illustrator-Excel workflow.

Advertisements