For 500 labels that each have a different item number, item name and barcode, build a sheet where one Excel row becomes one label, load it with Connect data in the tool panel on the left of GoodLabel Designer, and print. If only a number goes up by 1 on each label, Generate serial numbers is enough without Excel. The two features can't be used together in one document, so first decide which of the two you need.
5 rules for preparing Excel and CSV files
| Rule | Reason |
|---|---|
| Put only column names (item number, item name, barcode, quantity) in row 1 | The designer reads row 1 as column names and rows from 2 on as label data |
| Format number columns that start with 0 as text | Values are read exactly as they appear in Excel, so with a number format 00123 comes in as 123 |
| Put only digits in the quantity column, without thousands separators | The quantity column accepts only cells made of 0–9, so "10 pcs" or "1,000" is dropped as having no quantity |
| Keep date columns in an Excel date format | In the detailed mapping settings they are converted to one of 10 formats, such as YYMMDD or YYYY-MM-DD |
| Separate CSV fields with commas and don't break lines inside a cell | CSV splits rows by line, so a line break inside a cell splits one row into two |
For CSV text encoding, the designer detects UTF-8 (with or without BOM) and UTF-16 with a BOM on its own, and reads files that aren't UTF-8 as Korean ANSI (CP949). It accepts .xlsx and .xls Excel files, and if a file has 2 or more sheets, you choose the sheet to use when loading it.
5 steps to link label elements to columns
- Place the text and barcodes on the label first, with sample text to hold their positions.
- Drag the file onto Connect data in the tool panel. For practice there is Use sample data.
- For each element, choose a column name in Linked field, and if needed enter a Prefix and Suffix (for example SKU- and -KR) and a Default value to use when the cell is empty.
- For a date column, expand Details, treat it as a date and choose a format.
- Check the first 3 rows in the preview, then save.
To join 2 or more columns in one element, use Combine data. Drag blocks to set the order, such as item number column + "-" + color column. Clean data removes spaces before and after cell contents and is on from the start. Pictures of each screen are in the Data connection chapter.
2 settings for choosing rows and copies in the Print window
| Setting | What you can choose | Example |
|---|---|---|
| Print quantity settings | Same for all records (Copies per record) / Set per record (Quantity column) | 200 rows × 2 copies = 400 labels |
| Select data | Row check boxes, search column and search term, Select range (start–end row number) | Add only the range of rows 101–200 |
While data is linked, the Print window has no Print quantity field for serial numbers, and as many labels print as the rows you selected. Range row numbers skip the column name row and count the first data row as 1. They are off by 1 from Excel's row numbers, so to print Excel rows 102–201, enter 101–200.
With Set per record, rows whose quantity cell is empty or 0 are left out of automatic selection, and when you add a range, a message shows how many rows were excluded for failing GS1 validation or having no quantity. If you've linked a column to a GS1 barcode, rows with the wrong number of GTIN digits or a wrong date format are left out the same way; the detailed rules are in How to make GS1-128 and GS1 DataMatrix labels.
Making numbered labels with Generate serial numbers
Choose Generate serial numbers on the Data tab of the text or barcode Properties panel to open the settings window, which has 2 tabs: Simple settings and Compose serial number.
- There are 2 serial number types, numbers (0001, 0002…) and letters (A, B, C…), with 1 to 10 digits.
- You enter a start value and an increment; a negative increment such as -1 makes the numbers count down.
- You enter a prefix that goes in front, such as SN-, and a suffix that goes after, such as -KR, separately.
- On the Compose serial number tab, you chain several blocks to build a compound number such as LOT-A-0001.
In the Print window, Print quantity is how many numbers to generate (1–99999), and Copies is how many labels to print for each number. With a print quantity of 50 and 2 copies, you get 50 numbers, 2 labels each, 100 labels in all. Date and time elements are fixed at the moment printing starts.
For a document saved as a file, the next number is saved in that .glb file when printing finishes, so if you printed 50 numbers with a start value of 1 and an increment of 1, opening the file the next day continues from 51. For test prints that start again from the same number, such as 0001, every time, check Reset to start after printing.
A number column for using serial numbers with Excel data
In one document, a serial number element locks data linking, and with a spreadsheet loaded, serial number generation is blocked. If you need numbering to restart at 0001 for each item number, add another number column in Excel, fill it in, and link that column to a text or barcode element. With item numbers in column A, enter =TEXT(COUNTIF($A$2:A2,A2),"0000") in B2 and fill down; each item number gets 0001, 0002… as text. If you paste the formula results as values, the numbers you've assigned won't change even if you later sort or delete rows.