Remove Blank Entries from an Excel Dependent Drop-Down List
Question details
The user needs an Excel dependent drop-down list that excludes empty source cells and unwanted zero values, including when the source data is in another workbook.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A data validation list uses a linked source table to display options based on a parent selection.
- Observed behavior
- The dependent drop-down includes blank entries because its source contains empty cells or formulas that return zero.
Check whether unwanted entries are truly empty, formulas returning an empty string, or zero values. Identify the parent-selection cell and source columns, and check whether your Excel version supports FILTER.
Filter the dependent options into a local helper range
Use this method in Excel versions that support FILTER when blanks or zero values appear anywhere in the source list.
The example uses a table named TblData with Parent and Category columns, and a parent-selection cell at F2. Replace these names and references with those in your workbook.
In an unused cell such as H2 on a sheet named Lists, enter =FILTER(TblData[Category],(TblData[Parent]=$F$2)*(TblData[Category]<>"")*(TblData[Category]<>0),""). Keep the cells below H2 clear so the results can spill. If the parent selector is on another sheet, qualify its reference, such as Entry!$F$2.
Go to Formulas > Name Manager > New. Enter DependentOptions as the name and =Lists!$H$2# in Refers to, then click OK.
Select the dependent drop-down cell, open Data > Data Validation, and choose List under Allow. Set Source to =DependentOptions and click OK.
Change the parent-selection cell and open the dependent drop-down. Confirm that each category shows only its matching nonblank, nonzero options. Clear any previously selected child value that no longer matches the parent.
Trim trailing empty or zero entries with a named range
Use this method when the dependent source has valid options grouped at the top and unwanted entries only at the bottom.
Build a Blank-Free Dependent Drop-Down in WPS Spreadsheet
In WPS Spreadsheet, create compact helper lists for each parent category, name those lists, and use INDIRECT in data validation to show the matching options. This approach avoids relying on linked Excel dynamic-array formulas.
- 1. Prepare clean category lists: Open your workbook in WPS Spreadsheet. On a helper sheet, place each category's valid options in a separate column without gaps, empty strings, or unwanted zeros.
- 2. Name each option range: Select only the populated options for a category and use Formulas > Define Name. For example, name one range Fruits and another Vegetables. Use valid range names without spaces.
- 3. Create the parent drop-down: Select the parent cell, such as F2, and open Data > Validation. Choose List and set the source to the cells containing Fruits and Vegetables, matching the range names exactly.
- 4. Create the dependent drop-down: Select the child cell, open Data > Validation, choose List, and enter =INDIRECT($F$2) as the source. Confirm the rule.
- 5. Check and maintain the lists: Select each parent value and open the child drop-down to verify its options. When adding items, update the named range to include the new populated cells without including empty rows.
Frequently Asked Questions
Why does selecting Ignore blank not remove empty items from the drop-down?
In Data > Data Validation, Ignore blank controls how validation handles empty values; it does not filter the source list. Remove unwanted entries from the source or point the rule to a cleaned helper range.
Can the INDEX and COUNTIF formula remove blanks between valid options?
No. It creates a continuous range ending at a calculated row, so internal blank cells remain inside that range. Use FILTER to create a compact helper list, or manually consolidate the options before defining the named range.
How do I exclude formulas that return an empty string or zero?
In the FILTER include argument, combine (TblData[Category]<>"") with (TblData[Category]<>0). Add the parent-category condition to keep the list dependent. If zero is a legitimate option, omit the zero exclusion.
Can a dependent drop-down use a source workbook that is closed?
Do not assume linked dynamic-array formulas will work with a closed source workbook. Excel supports linked dynamic arrays only while both workbooks are open, and refreshing after closing the source can produce #REF! errors. Use a local helper table or import the source data into the destination workbook for a more reliable validation source.
Why does the old child selection remain after I change the parent?
Data validation updates the available choices but does not automatically clear an existing cell value. After changing the parent cell, clear the child cell and choose an option from the updated list.
