Function Problems

How to Exclude Blank Values from an Excel Data Validation List

Rana Garcia Oct 9, 2026 869 views

Question details

The user wants to remove empty entries from a data validation drop-down list where the source range has merged cells or blanks, without relying on a traditional helper column.

How to Exclude Blank Values from an Excel Data Validation List

Product: Excel
Device & OS: not provided
Scenario: Creating a clean, dynamic drop-down list from a range containing blank cells or merged cells.
Observed behavior: The data validation drop-down includes unwanted blank spaces when referencing ranges with empty cells, and directly typing a FILTER formula into the validation source box results in an error.

Before you start

Ensure your version of Excel or your spreadsheet software supports dynamic array functions (like FILTER), as these are required to create a dynamic, blank-free list without manual helper columns.

Solution 1Recommended

Use the FILTER Function with a Spilled Range Reference

Because spreadsheet software does not accept the FILTER formula directly inside the Data Validation Source box, you must place the formula in a separate cell and reference its spilled results.

By placing the FILTER function on a hidden worksheet, you can keep your main spreadsheet clean. The dynamic array will automatically extract only the non-blank values, and using the '#' symbol allows the data validation list to dynamically resize.

Enter the FILTER formula in a separate location

Open a new worksheet or select an unused cell (e.g., cell Z1). Enter the formula =FILTER(A6:A1000, A6:A1000<>"") to extract all non-blank items from your original list.

Select the target cell for your drop-down

Go to the cell where you want the drop-down list to appear and click to select it.

Open Data Validation settings

Navigate to the Data tab on the ribbon and click on 'Data Validation'.

Configure the Source with a spill operator

Under the 'Allow' drop-down, select 'List'. In the 'Source' box, type the reference to the cell with your formula followed by the '#' sign (e.g., =Z1#), then click OK.

Hide the formula worksheet (Optional)

Right-click the sheet tab containing your FILTER formula and select 'Hide' to keep your workbook clean.

Use the FILTER Function with a Spilled Range Reference

Dynamic Updates: Whenever you add or remove non-blank items in your original A6:A1000 range, the drop-down list will automatically update without showing any blank spaces.

Advanced Spreadsheets

Create Dynamic Drop-Down Lists in WPS Spreadsheet

WPS Spreadsheet fully supports dynamic arrays and advanced data validation. You can easily build professional, error-free drop-down menus while enjoying a fast and intuitive interface.

1. Apply the FILTER function: In a blank area, type =FILTER(SourceRange, SourceRange<>"") to create a clean list of items.
2. Open Data Validation: Select your target cell, go to the Data tab, and click Data Validation.
3. Reference the dynamic list: Choose 'List' and input the cell reference of your formula followed by the '#' symbol (e.g., =Sheet2!Z1#).

Easily filter out blank values using advanced functionsFully compatible with Microsoft Excel formulas (.xlsx files)Intuitive Data Validation dialog for seamless setupLightweight, fast, and free to use for daily tasks

microsoft office alternative - wps office

Frequently Asked Questions

Can I type the FILTER formula directly into the Data Validation Source box?

No, Excel and most spreadsheet programs do not allow dynamic array formulas like FILTER directly inside the Data Validation Source box. You must place the formula in a cell and reference it.

What does the '#' symbol mean in the validation source?

The '#' symbol is a spilled range operator. It tells the software to include the original cell and all adjacent cells that the dynamic array formula (like FILTER) has populated.

Why does my drop-down list still show blank spaces?

If you are not using a dynamic array reference (the '#' symbol) and are instead selecting a static range, any empty cells within that range will appear as blanks in the drop-down. Ensure your source refers to the exact cell containing the FILTER formula followed by '#'.

Excel & Data Problems