Data Import & Export

How to Populate an Excel SKU Suffix from a Drop-Down List

Algirdas Jasaitis Oct 23, 2026 869 views

Question details

The user wants to automatically generate Product SKUs based on a selected size range or suffix from a drop-down list to minimize manual entry errors.

How to Populate an Excel SKU Suffix from a Drop-Down List

Product: Excel
Device & OS: not provided
Scenario: Inventory management or product catalog creation requiring automated SKU generation from base product codes and size matrices.
Observed behavior: Manual entry of SKU suffixes and size ranges leads to errors and inefficiencies; the goal is to fully automate this using lookup tables, drop-downs, and concatenation.

Before you start

Ensure you have a master list of your base product codes and a separate reference table defining all available sizes or suffix ranges before setting up the automation.

Solution 1Recommended

Use Data Validation and Concatenation Formulas

Create a drop-down list for suffixes and combine it with the product code using a simple text formula.

This method is ideal for simple inventory lists where each base product only needs a single, easily selectable suffix applied at a time.

Create a drop-down list

Select the cell where you want the drop-down list, go to the 'Data' tab, click 'Data Validation', choose 'List' under the Allow criteria, and input your suffix references in the Source box.

Concatenate the values

In your target SKU cell, use the CONCATENATE function or the ampersand (&) operator (e.g., =A2 & "-" & B2) to merge the base product code and the selected suffix.

Apply to all rows

Drag the fill handle down to apply the drop-down validation and the concatenation formula to the rest of your inventory list.

Use Data Validation and Concatenation Formulas

Automation tip: Using a hyphen or underscore inside quotation marks in your formula (like "-") helps keep the generated SKUs highly readable.

Solution 2

Automate Complex Size Ranges with Power Query

For bulk SKUs requiring a dynamic range of sizes, Power Query merges tables to generate required combinations.

Automate SKUs and Inventory Management with WPS Spreadsheet

WPS Office provides robust data validation and powerful formula capabilities, allowing you to easily generate automated SKUs and streamline inventory tasks efficiently.

1. Open your lists: Open your product and suffix lists in WPS Spreadsheet.
2. Set up Data Validation: Navigate to the 'Data' tab and select 'Validation' to create your suffix drop-down list.
3. Apply concatenation: Use the CONCATENATE function or the '&' symbol to combine your product codes with the drop-down values.
4. Fill the column: Drag the formula across your product column to instantly generate all SKUs.

Fully compatible with Microsoft Excel formulas and data validation rulesSeamless data formatting and automated SKU concatenation featuresLightweight application that runs smoothly even on older devicesFree to download and use for your daily spreadsheet needs

microsoft office alternative - wps office

Frequently Asked Questions

Why is my concatenated SKU formula not updating when I change the drop-down selection?

Ensure that Excel's calculation mode is set to Automatic. Go to the 'Formulas' tab, click 'Calculation Options', and select 'Automatic'.

Can I add a hyphen or other separator between the product code and suffix?

Yes, you can include text strings enclosed in quotation marks within your formula. For example, using =A2 & "-" & B2 will insert a hyphen between the contents of A2 and B2.

How do I update the drop-down options if I add new SKU suffixes?

If your data validation source is a static range, you need to manually adjust the range in the Data Validation menu. To make it update automatically, format your source list as an Excel Table before referencing it in the Data Validation menu.

What should I do if my Power Query result is missing newly added products?

Power Query results do not update automatically in real-time as you type. You must right-click anywhere in the output table and select 'Refresh', or go to the Data tab and click 'Refresh All' to pull in newly added data.

Excel & Data Problems