Fix an Excel Drop-Down List That Displays on One Line
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.
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.
Correct the separator in the data validation source
Use this method when the list entries were typed directly into the Source box.
Select the affected cell, then choose Data > Data Validation.
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.
Click OK, then open the cell's drop-down and check that each entry appears separately.

Use a worksheet range as the drop-down source
Use a range of cells to avoid typing a separator between list entries.
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. Open your spreadsheet: Open WPS Spreadsheets and use Open to select your workbook.
- 2. Enter the list values: Type each option in a separate cell, such as A1 through A3.
- 3. Set up data validation: Select the cell for the drop-down, choose Data > Data Validation, and select List as the validation type.
- 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.

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.




