Function Problems

Create an Excel Drop-Down List Without Blank Entries from a Horizontal Range

Huma Ashraf ChHuma Ashraf Ch Nov 18, 2026 869 views

Question details

Create a data-validation drop-down list from a horizontal range while excluding blank entries.

Create an Excel Drop-Down List Without Blank Entries from a Horizontal Range
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the destination cell

Click the cell where you want the drop-down list.

2
Create the validation list

Go to Data > Data Validation, choose List, and enter your horizontal source range, such as =$A$1:$H$1, in the Source box.

3
Test the drop-down

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 the horizontal range directly in a recent Excel version
Create a cleaner drop-down

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. 1. Open the workbook: Open your Excel workbook in WPS Spreadsheets and locate the horizontal source row.
  2. 2. Prepare the helper row: Copy the nonblank source values into adjacent cells in an unused row, without leaving gaps.
  3. 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. 4. Test the list: Confirm the settings and open the drop-down. Update the helper row and source range when the original values change.
Create a drop-down from a horizontal list without blank choices.Open and edit Microsoft Excel XLSX and XLS files.Use a familiar spreadsheet interface with data-validation tools.Work with a free, lightweight office suite.
microsoft office alternative - wps office

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.