Data Import & Export

Fix an Excel Drop-Down List That Displays on One Line

Huma Ashraf ChHuma Ashraf Ch Nov 14, 2026 868 views

Question details

The user needs an Excel data validation drop-down to display each entry as a separate option.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A drop-down list was created by typing its entries directly into the data validation source.
Observed behavior
All entries appear together on one line, potentially because the source uses a separator that does not match the regional settings.
Before you start

Select a cell with the affected drop-down and identify the entries that should appear as separate options. Check whether the list was typed directly into the data validation source or comes from worksheet cells.

Solution 1Recommended

Correct the separator in the data validation source

Use this method when the list entries were typed directly into the Source box.

1
Open the validation settings

Select the affected cell, then choose Data > Data Validation.

2
Change the separator

On the Settings tab, check the Source box. Replace the separator between entries with the one used for your region; for example, change commas to semicolons if your system uses semicolons.

3
Test the list

Click OK, then open the cell's drop-down and check that each entry appears separately.

Correct the separator in the data validation source
Regional settings vary: A semicolon fixed the reported case, but the correct list separator depends on your regional settings.
Create a drop-down list in WPS

Create a range-based drop-down list in WPS Spreadsheets

Use WPS Spreadsheets data validation to create a drop-down from worksheet cells, with each cell providing a separate option.

  1. 1. Open your spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
  2. 2. Enter the list values: Type each option in a separate cell, such as A1 through A3.
  3. 3. Set up data validation: Select the cell for the drop-down, choose Data > Data Validation, and select List as the validation type.
  4. 4. Choose the source range: Enter or select the cells containing your options in the Source field, confirm the settings, and open the drop-down to test it.
Create drop-down options from a worksheet rangeOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceGet started with WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Why do all my Excel drop-down options appear as one item?

If you typed the options directly into Data > Data Validation > Source, Excel may be treating the separators as part of one entry. Use the list separator configured for your region or select a worksheet range instead.

Should I separate my list entries with commas or semicolons?

Use the separator configured for your regional settings. If comma-separated entries appear as one item, try semicolons in the Source box and test the drop-down again.

How do I use cells as the source for an Excel drop-down?

Put each option in a separate cell, select the drop-down cell, and open Data > Data Validation. Choose List and enter the cell range in Source, such as =$A$1:$A$3.

Can I create a similar drop-down in WPS Spreadsheets?

Yes. Enter the options in separate cells, select the destination cell, and use Data > Data Validation to create a List using those cells as the source.