Copy the Lido SKU Generator Template here. A new tab or window will open, asking you to copy the template. Click Make a copy.
The SKU Generator Template by Lido has three sheets. The first sheet contains the instructions on using it. The second sheet is for the categories to be used in creating SKUs. The third sheet is for listing the items and generating the SKUs themselves.
Click the Categories tab. You will see the following sheet:
Row 2 contains checkboxes. To include a category, tick the checkbox above it. For example, to include CATEGORY 1 in the SKU, we tick the checkbox in its column.
After selecting it, we add the category name in the cell below the checkbox.
After adding the category name, we then list the category options under it and their corresponding codes on the side.
We repeat this for the other categories we set.
The sheet can accommodate up to six categories and twenty options each. For our example, we have a clothes shop and we want to generate SKU numbers for the items. The selected categories and their corresponding options are
Brand: Peace Mankind, Quizmaster
Type: T-Shirt, V-Neck
Color: White, Black, Brown, Green, Orange, Blue
Design: Orchid, Oak, Sea, Mountains
Size: Small, Medium, Large, Extra Large
And their corresponding codes are assigned in the columns to their right.
After setting the categories, we can now add the items and set their categories. Click the Items sheet tab. You will get the following interface.
Input the items in the first column starting from Row 2.
Afterwards, select the categories for each of the items listed by selecting them from the drop-down lists.
As you select the categories, you will see the SKU being generated real-time. Once you finish selecting the categories, you can now copy the generated SKUs on the last column.
The formula for generating the SKU in the template is
=CONCATENATE(C2,E2,G2,I2,K2,M2,TEXT(N2,"000"))
This generates a SKU where the codes form a continuous string:
PMTSBLMTS001
You can add dashes between each part of the code to make it more readable. Use this formula:
=CONCATENATE(C2,"-",E2,"-",G2,"-",I2,"-",K2,"-",M2,TEXT(N2,"000"))
The resulting SKU is
PM-TS-BL-MT-S-001
Finally, if you don’t want numbers to be automatically generated as part of the SKU, use either of the two formulas:
With dashes
=CONCATENATE(C2,"-",E2,"-",G2,"-",I2,"-",K2,"-",M2)
Without dashes
=CONCATENATE(C2,E2,G2,I2,K2,M2)
To customize the formula, check our tutorials on CONCATENATE and TEXT below:
CONCATENATE Google Sheets: The Ultimate Guide [2023]
How to Convert Numbers to Strings in Google Sheets - Lido.app
