Function Problems

How to Create an Excel Drop-Down List Without Blank Cells

Maira Mehtab Sep 22, 2026 875 views

Question details

Create a data validation drop-down list that excludes any empty cells located between valid entries in the source range.

Product: Microsoft Excel 2016
Device & OS: not provided
Scenario: Setting up a drop-down menu from a list where data is added or removed, leaving empty gaps in the source column.
Observed behavior: Standard data validation includes the blank cells from the source range, resulting in a drop-down menu with empty, selectable gaps.

Before you start

Identify a clear column or a separate worksheet where you can safely create a 'helper range' without disrupting your existing data.

Solution 1Recommended

Use a Helper Column with INDEX and AGGREGATE Formulas

Extract non-blank cells into a new continuous list using the AGGREGATE function, which can handle arrays natively without requiring special keystrokes.

This method involves creating a secondary 'helper' column that pulls only the valid entries from your original data. You then point your drop-down list to this clean helper column.

Create a helper column

In a separate column, enter an INDEX and AGGREGATE formula designed to pull only cells containing text or numbers from your original range, ignoring the blanks.

Define a dynamic named range

Go to Formulas > Name Manager and click New. Use a dynamic OFFSET formula, such as =OFFSET($Z$1,0,0,COUNTIF($Z:$Z,"> "),1) (assuming your clean data starts in Z1), to refer to this new list.

Apply Data Validation

Select the cell where you want the drop-down menu. Go to Data > Data Validation, choose 'List' from the Allow drop-down, and type the name of your dynamic range (e.g., =CleanList) in the Source box.

Regional Formula Separators: Depending on your computer's regional settings in Excel 2016, you may need to use semicolons (;) instead of commas (,) to separate arguments in your OFFSET and AGGREGATE formulas.

Solution 2

Use a Traditional Array Formula

If you prefer not to use the AGGREGATE function, you can extract the non-blank values using a traditional INDEX array formula.

Seamless Data Management

Create Dynamic Drop-Down Lists Easily in WPS Spreadsheet

WPS Spreadsheet provides robust data validation features and full compatibility with advanced Excel formulas like INDEX, AGGREGATE, and OFFSET. You can seamlessly create dynamic drop-down lists without blank cells using the exact same methods.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your source data with blank gaps.
2. Create a clean helper range: Use the INDEX or OFFSET formulas in an empty column to filter out the blank cells from your main list.
3. Access Data Validation: Select the target cell, navigate to the Data tab on the ribbon, and click on 'Validation'.
4. Configure the Drop-Down: Choose 'List' under the Allow criteria, reference your clean helper range in the Source box, and click OK.

Fully compatible with Microsoft Excel formulas and data validation rules.Lightweight, fast, and runs smoothly on older devices and operating systems.Supports advanced dynamic named ranges for highly responsive data entry.Free to use with a clean, familiar tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel drop-down list show blanks even with 'Ignore blank' checked?

The 'Ignore blank' checkbox in the Data Validation menu only dictates whether a user can leave the validated cell completely empty without triggering an error alert. It does not actually remove or hide blank cells from the source range in the drop-down menu itself.

Why am I getting an error when typing the array formula in Excel 2016?

In Excel 2016 and older versions, dynamic array formulas are not processed automatically. You must commit the formula by pressing Ctrl+Shift+Enter simultaneously. If you only press Enter, the formula will return an error or produce an incorrect result.

Can I use commas in my OFFSET formula for the named range?

This depends entirely on your operating system's regional settings. While US/UK regions use commas (,) to separate function arguments, many European regions require semicolons (;). If Excel throws a formula error, try swapping the commas for semicolons.

Excel & Data Problems