Others

How to Create a Combined Excel Drop-Down List from Two Columns

Anum Mushtaq Oct 22, 2026 868 views

Question details

The user wants to combine data stored in separate columns (such as Product Type and Scale) into a single, unified drop-down list for easier selection during data entry.

How to Create a Combined Excel Drop-Down List from Two Columns

Product: Excel
Device & OS: not provided
Scenario: Setting up an efficient data entry worksheet where related lookup attributes are stored in separate columns but must be selected as a single combined string.
Observed behavior: The source data is currently split across multiple columns, preventing the standard Data Validation feature from directly turning them into a single-line combined drop-down menu.

Before you start

Ensure your source data columns are clean and free of unnecessary leading or trailing spaces. It is highly recommended to keep your lookup lists on a separate worksheet to maintain a clean main data entry interface.

Solution 1Recommended

Use a Helper Column and the Concatenation Operator

By creating a helper column, you can merge text from multiple columns using the ampersand (&) symbol. This new column can then serve as the source range for your data validation drop-down list.

This approach is straightforward and updates dynamically. Whenever you change the values in the original columns, the helper column and the drop-down list will reflect the changes immediately.

Add a helper column

Navigate to the lookup worksheet containing your separate data columns (e.g., Type in Column B and Scale in Column C). Choose the next empty column to act as your helper column.

Enter the concatenation formula

In the first data cell of the helper column (for example, D2), type the formula =B2&" "&C2. The quotation marks with a space in the middle ensure that the combined values are separated by a space for better readability.

Fill the formula down

Select the cell containing your formula, then click and drag the fill handle (the small square at the bottom-right corner of the cell) down to the bottom of your data list to apply the formula to all rows.

Apply Data Validation

Switch to your main data entry worksheet and select the cell(s) where you want the drop-down list to appear. Go to the 'Data' tab and click 'Data Validation'. Under the 'Allow' drop-down, select 'List'. Click inside the 'Source' box, then go to your lookup worksheet and highlight the range in your new helper column. Click 'OK'.

Use a Helper Column and the Concatenation Operator

Customizing the Delimiter: You can change the character between the quotation marks to format your drop-down options differently. For example, using =B2&" - "&C2 will insert a hyphen between the two column values.

Smart Data Management

Create Dynamic Drop-Down Lists Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful data validation tools and full formula support, allowing you to combine columns and create advanced drop-down lists with ease, completely free of charge.

1. Set up a helper column: Open your file in WPS Spreadsheet. Find your separate lookup columns and designate an empty adjacent column to store the combined values.
2. Combine the text: Type =B2&" "&C2 into the first cell of your helper column, then drag the fill handle downwards to combine the remaining rows.
3. Create the drop-down menu: Select your target input cells, navigate to the 'Data' tab, select 'Validation', choose 'List', and highlight your newly created helper column as the source.

Easily build combined drop-down lists using familiar concatenation formulas.Fully compatible with Microsoft Excel (.xlsx) files and features.Intuitive tabbed interface that makes navigating between lookup tables and main sheets simple.Lightweight application that loads quickly and consumes minimal system resources.

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine more than two columns into a single drop-down list?

Yes, you can chain multiple columns together using the ampersand operator multiple times. For example, the formula =A2&" "&B2&" "&C2 will merge the contents of columns A, B, and C into one cell with spaces separating each value.

How do I remove duplicates from my combined drop-down list?

Once you have combined the columns using a formula, you cannot remove duplicates directly from those formula cells. You need to copy the helper column, paste it as 'Values' in a new column, and then use the 'Remove Duplicates' tool found in the Data tab. Use this deduplicated column as your new Data Validation source.

Will the drop-down list update automatically if I add new data to the columns?

If you simply reference a specific range (like D2:D10), adding new rows to D11 will not update the list. To make it dynamic, format your lookup data and helper column as an official Excel Table (press Ctrl+T) before setting up data validation, or use the OFFSET formula to create a dynamic named range.

Why is my combined drop-down list showing errors like #VALUE!?

This error typically occurs if one of the referenced cells in the source columns already contains an error. Check your original columns (e.g., Column B and Column C) to ensure they contain valid text or numbers without hidden formula errors.

Office Quick Solutions