How to Create SKU Numbers (With Examples)

A SKU (stock keeping unit) is your own internal code for a specific product you stock. Unlike a UPC barcode, which is assigned through GS1 and is the same at every store that sells the product, a SKU is something you create, and it can follow whatever logic suits your business. That freedom is also the problem: SKUs invented on the fly turn into a mess of inconsistent codes that nobody can read and spreadsheets quietly mangle.

This guide shows how to design a SKU format once, apply it to products and variants, and generate SKUs automatically in Excel.

What a good SKU looks like

The most practical SKUs are built from a few short segments, from general to specific, separated by hyphens:

CATEGORY - PRODUCT/STYLE - VARIANT - (optional) SEQUENCE

TSH-CRW-BLK-M        T-shirt, crew neck, black, medium
MUG-12OZ-WHT         Mug, 12 oz, white
CBL-USBC-2M          Cable, USB-C, 2 meters
CFE-ETH-1LB-WB       Coffee, Ethiopia, 1 lb, whole bean

Someone picking an order can read TSH-CRW-BLK-M and know what to grab without looking it up. Because the most general part comes first, sorting the list alphabetically groups all T-shirts together, then all crew necks, then all black ones.

Rules for SKUs that don't break

1. Keep them short. Roughly 8 to 12 characters is a comfortable range. Long SKUs get truncated on labels and mistyped.
2. Use only letters, numbers and hyphens. Spaces, slashes, periods, ampersands and accented characters cause trouble in URLs, CSV files, barcode fonts and marketplace imports.
3. Use uppercase. Some systems treat abc-1 and ABC-1 as different SKUs; others don't. Uppercase everywhere avoids the question.
4. Avoid look-alike characters. O and 0, I and 1, and sometimes S and 5 or B and 8 get confused when read off a label or typed by hand. Many businesses simply never use the letters O and I in SKUs.
5. Don't start with zero. Excel treats a value like 00125 as the number 125 and drops the leading zeros. Starting every SKU with a letter avoids this entirely.
6. Avoid patterns Excel reads as dates or numbers. A SKU like 3-12 can turn into a date, and something like 1E5 can turn into scientific notation. A letter prefix and hyphen-separated segments prevent both.
7. Never reuse a SKU. When a product is discontinued, retire its SKU. Reusing it corrupts your sales history.
8. Don't encode things that change. Price, supplier and storage location change over time. Keep them in their own columns, not in the SKU.

Handling variants

Each sellable variant needs its own SKU — if a customer can choose it and you need to count it separately, it gets a code. A common approach is a parent code for the product and a child SKU for each variant:

Keep a small lookup table of approved variant codes (BLK, HGR, NVY; S, M, L, XL, 2XL) so nobody invents BK for one product and BLCK for another. Consistent codes are what make SKUs filterable: every black item ends in -BLK- no matter who created it.

SKU examples by business type

Resellers are a special case: every item is unique, so a short category plus a running number is usually enough. The bin location goes in its own column — see our guide to tracking reseller inventory and profit.

Build SKUs automatically in Excel

If your product list has Category in column A, Style in B, Color in C and Size in D, this formula builds a SKU from the first three letters of each field:

=UPPER(LEFT(A2,3))&"-"&UPPER(LEFT(B2,3))&"-"&UPPER(LEFT(C2,3))&"-"&UPPER(D2)

Category: Hoodie | Style: Zip | Color: Black | Size: m   →   HOO-ZIP-BLA-M

That works, but first-three-letters breaks down quickly (Black and Blue happen to differ, but Brown and Bronze both become BRO, and Heather Gray becomes HEA). For real catalogs, use lookup tables for codes. Put a two-column table named ColorCodes with Color and Code, then:

=UPPER(LEFT(A2,3))&"-"&UPPER(LEFT(SUBSTITUTE(B2," ",""),3))&"-"&XLOOKUP(C2,ColorCodes[Color],ColorCodes[Code],"???")&"-"&UPPER(D2)

SUBSTITUTE removes spaces so "Zip Up" becomes "ZIP".
The "???" fallback makes a missing color code obvious instead of silently wrong.

For a running sequence number with a category prefix (useful for one-of-a-kind items), count how many times the category has appeared so far:

=UPPER(LEFT(A2,3))&"-"&TEXT(COUNTIF($A$2:A2,A2),"0000")

Row 2: Denim  → DEN-0001
Row 3: Shoes  → SHO-0001
Row 4: Denim  → DEN-0002

TEXT(…, "0000") pads the number to four digits so DEN-0002 sorts before DEN-0010. Because the result starts with letters, Excel keeps it as text and the zeros survive.

Check for duplicates

Before you import SKUs anywhere, make sure each one is unique:

Duplicate flag (F2):  =IF(COUNTIF($E:$E,E2)>1,"DUPLICATE","")

Or highlight duplicates with conditional formatting:  =COUNTIF($E:$E,$E2)>1

Rolling out SKUs to an existing catalog

If you already have products without SKUs, or with a jumble of old codes, you don't have to fix everything in one weekend. A practical order of operations:

1. Write the format down. One short document or a tab in your spreadsheet listing each segment, the approved codes (categories, colors, sizes) and the rules above. This is what keeps the system consistent when someone else adds products.
2. Export your product list from your POS, store platform or existing spreadsheet into Excel.
3. Generate new SKUs with formulas, check for duplicates, then paste them as values.
4. Keep the old code in a column called Legacy SKU, so past sales and supplier references can still be traced.
5. Update labels and bins during your next inventory count, when you are touching every item anyway. Our inventory count guide covers how to plan that count.
6. Import the new SKUs into your sales channels last, after the spreadsheet and the shelves agree.

Common SKU mistakes

• Using the manufacturer's part number as your SKU. It works until two suppliers use the same number or a supplier changes it.
• Encoding the supplier or price in the SKU, which forces a new SKU every time you switch vendors or reprice.
• Mixing formats, like TSH-BLK-M for one product and TSHIRT_M_BLACK for another. Pick one and convert the outliers.
• Making SKUs so detailed they need a decoder. If staff can't read them, they will stop using them.

Matching your SKUs to supplier invoices

Your suppliers have their own item numbers, and they rarely match yours. Add a Supplier Item # column next to your SKU so receiving can look one up from the other:

Your SKU from a supplier item number on an invoice:
=XLOOKUP(B2,Products[Supplier Item #],Products[SKU],"NEW ITEM")

The invoice to inventory spreadsheet converter reads supplier item codes, descriptions, quantities and unit costs off invoice photos and PDFs. Paste the result next to this lookup and every delivery is mapped to your SKUs in seconds, with anything new flagged for you to assign a code. For purchase orders you send out, the purchase order to Excel converter does the same for PO line items.

Good starting points: the boutique clothing template for size and color variants, the hardware store template for spec-heavy items, and the e-commerce template for multi-channel sellers.