Create an Excel Drop-Down List Without Blank Entries from a Horizontal Range
Question details
Create a data-validation drop-down list from a horizontal range while excluding blank entries.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A row contains values separated by blank cells, and the user wants to use those values in a drop-down list.
- Observed behavior
- The desired drop-down displays the nonblank values without empty choices.
Identify the row containing your list values and the cell where you want the drop-down. Check your Excel version, because handling of blank source cells varies between versions.
Use the horizontal range directly in a recent Excel version
Try this first in a recent desktop version of Excel or Excel for the web, where blank source cells are generally omitted automatically.
Click the cell where you want the drop-down list.
Go to Data > Data Validation, choose List, and enter your horizontal source range, such as =$A$1:$H$1, in the Source box.
Click OK or Save, then open the drop-down and check whether blank choices are omitted. If they appear, use a helper range instead.

Use a nonblank helper range in an older Excel version
Use this method if your Excel version shows blank choices when the original horizontal range is used directly.
Build a Drop-Down Without Blank Choices in WPS Spreadsheets
Use a contiguous helper row as the data-validation source in WPS Spreadsheets to create a drop-down without gaps.
- 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the horizontal source row.
- 2. Prepare the helper row: Copy the nonblank source values into adjacent cells in an unused row, without leaving gaps.
- 3. Add data validation: Select the destination cell, go to Data > Data Validation, choose List, and select the occupied helper cells as the source.
- 4. Test the list: Confirm the settings and open the drop-down. Update the helper row and source range when the original values change.

Frequently Asked Questions
Can an Excel drop-down use a horizontal row as its source?
Yes. Select the destination cell, open Data > Data Validation, choose List, and enter a row range such as =$A$1:$H$1 in Source.
Why does my drop-down still show blank choices?
Blank-cell handling depends on the Excel version. If the direct source range produces blank choices, copy the nonblank values into adjacent helper cells and use those cells as the Data Validation source.
Does Ignore blank remove blank items from the drop-down?
No. The Ignore blank setting controls whether an empty validated cell is allowed; it does not reliably remove blank entries from the list source.
Will a manually created helper row update automatically?
No. If you copied the values manually, update the helper row when the original values change and adjust the Data Validation Source if the number of entries changes.




