Office Settings & Configuration

How to Create an Excel Drop-Down from Comma-Separated Values

Elise Williams Sep 30, 2026 869 views

Question details

The user needs to generate a drop-down list in one cell based on a comma-separated text string located in another single cell.

Create an Excel Drop-Down List from Comma-Separated Values

Product: Spreadsheet / Excel
Device & OS: not provided
Scenario: Creating data validation choices dynamically from a single cell containing multiple items separated by commas (e.g., EUR, GBP, USD).
Observed behavior: The drop-down list needs to display EUR, GBP, and USD as three separate, selectable options rather than reading the entire string as a single menu item.

Before you start

Identify the cell containing your comma-separated string and determine whether you want to use a dynamic formula like TEXTSPLIT or manually input the values directly into the Data Validation menu.

Solution 1Recommended

Use the TEXTSPLIT Function with a Dynamic Array

Best for dynamic lists where the comma-separated text might change over time, allowing the drop-down to update automatically.

This method utilizes a helper cell to split the string into a dynamic array. The drop-down list then references this array, meaning any changes to the original text cell will immediately reflect in the drop-down options.

Set up a helper cell

Select an empty cell, such as C5, which will be used to split the values from the original string.

Enter the TEXTSPLIT formula

In the helper cell, type =TEXTSPLIT(A5, , ", ") and press Enter. This function splits the text in cell A5 by the comma and space, spilling the results into separate rows.

Open Data Validation

Select the cell where you want the drop-down menu to appear (e.g., A1). Navigate to the Data tab on the ribbon and click on Data Validation.

Reference the dynamic array

Under the Allow drop-down, select 'List'. In the Source box, enter =C5# (the hashtag ensures it captures the entire spilled array) and click OK.

Use the TEXTSPLIT Function with a Dynamic Array

Version Compatibility: The TEXTSPLIT function is available in Microsoft 365 and Excel for the web. If you are using an older version, use one of the alternative solutions below.

Solution 2

Enter Values Directly into the Validation Source

The quickest method if your comma-separated list is static and won't need frequent updates.

Solution 3

Standardize the Source Range in Separate Cells

Ideal for older versions of spreadsheet software that do not support dynamic array functions.

Effortless Spreadsheet Management

Create Dynamic Drop-Down Lists Easily in WPS Spreadsheet

WPS Office provides a highly compatible and intuitive Spreadsheet application that makes managing data validation and drop-down menus simple, ensuring your data entry remains accurate and highly efficient without requiring complex workarounds.

1. Open your workbook: Launch WPS Spreadsheet and open the document where you need to organize your data.
2. Select the target cell: Click on the specific cell or range of cells where the drop-down menu should appear.
3. Navigate to Data Validation: Go to the Data tab on the top ribbon and click the Data Validation icon.
4. Configure the list settings: In the Settings tab, change the 'Allow' criteria to 'List'.
5. Define the source: Select your predefined cell range or type the comma-separated items directly into the Source box, then click OK to finish.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and standard data validation rules.Create, edit, and manage drop-down lists intuitively without complex setups.Free, lightweight, and fast-loading office suite for Windows, Mac, and mobile platforms.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my drop-down list show the comma-separated values as a single line?

This happens if you reference a single cell containing the string (like =A5) instead of splitting the values. The spreadsheet treats the cell's entire content as one single item. You must use a function like TEXTSPLIT or enter the items directly into the source box separated by commas.

Can I make the drop-down list update automatically when the text changes?

Yes. By using the TEXTSPLIT function in a helper cell and referencing the spill range with a hashtag (e.g., =C5#), your drop-down list will dynamically update whenever the original comma-separated text is modified.

Does the TEXTSPLIT function work in all versions of Excel?

No, TEXTSPLIT is a newer dynamic array function available only in Microsoft 365 and Excel for the web. For older versions, you must manually split the data into separate cells or use the Text to Columns feature before creating the drop-down.

Office App Problems