Excel Dropdown List Without Blanks: Keep Options Clean

Yes. In Excel for Microsoft 365, use a FILTER formula to create a helper list containing only nonblank source values, then point Data Validation to that list. The dependable setup uses a defined name for the formula’s spill range, so the dropdown stays clean as the source changes.

Why blank options appear in an Excel dropdown

A Data Validation dropdown displays the values in its source range. If that range includes empty cells, Excel can show empty entries in the menu. This commonly happens when a list is given extra rows for future additions, when someone selects an entire worksheet column, or when the source data has gaps between entries.

For example, suppose A2:A6 contains Apple, Banana, a blank cell, Pear, and another blank cell. If the validation source is A2:A6, the dropdown’s range includes the blank cells as well as the three names. Excel is not treating those cells as separate choices; it is simply reading the source range exactly as supplied.

There are two different meanings of “blank” to keep in mind. A blank option in the dropdown comes from the list source. The Ignore blank checkbox in Data Validation concerns whether the input cell itself may be left empty; it does not remove blank entries from the menu.

Make a dynamic list that excludes blanks

First, put the source values in A2:A100 on a sheet named Lists, with the heading, if any, in A1. The range can contain empty cells between entries. In Lists!D2, enter this formula:

With source values in Lists!A2:A100, enter this in Lists!D2:

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

The formula filters out empty cells and cells whose formula result is an empty string, removes duplicate values, and sorts the remaining choices. Its results spill down from D2 automatically. If you want to preserve the order in which values first appear rather than sort them alphabetically, use this version in D2 instead:

With the same source range in Lists!A2:A100 and the helper formula in Lists!D2:

=UNIQUE(FILTER(A2:A100,A2:A100<>""))

Next, give the spilled results a name. In Excel for Microsoft 365 on Windows, choose Formulas > Name Manager > New. Enter DropdownItems as the name, leave the scope as Workbook, and set Refers to to the spill range below. The sheet name and starting cell must match your helper formula:

With the filtered results spilling from Lists!D2, use this as the defined name’s Refers to value:

=Lists!$D$2#

The final # means “the entire spill range starting at D2,” so the name can expand or contract when the formula returns a different number of choices. Select the cells that need dropdowns, then choose Data > Data Validation. Set Allow to List, and enter =DropdownItems in Source. Keep In-cell dropdown selected, then choose OK.

Whenever you change the source values, Excel recalculates the helper list and updates the available choices. The validation rule does not need to be rebuilt each time the list changes.

Use an Excel table for a growing source list

A fixed range such as A2:A100 is straightforward, but it has a limit: values entered below row 100 are not included. If people will regularly add choices, format the source as an Excel table. Select the source data and choose Insert > Table, or press Ctrl+T on Windows. Give the table a clear name, such as Items, and name its column Choice.

With an Excel table named Items and a column named Choice, enter this in a helper cell outside the table:

=SORT(UNIQUE(FILTER(Items[Choice],Items[Choice]<>"")))

The formula uses the table’s column rather than a fixed cell range. When a new row is added to the table, the filtered helper results can include its value without changing the formula. Create a workbook-scoped name that refers to the helper cell’s spill range, then use that name as the Data Validation source just as in the earlier setup. Keep the helper formula outside the table because dynamic-array formulas need open cells in which to spill.

Understand what the filter counts as blank

The condition <>"" removes truly empty cells and cells containing formulas that return an empty string. It does not remove a cell containing spaces. A cell with one or more spaces looks empty on screen but contains text, so it can still appear as an apparently blank dropdown choice.

To deal with that case, clean the source values by removing unwanted spaces. For a small list, edit the affected cells directly. For a larger list, Excel’s TRIM function can help remove ordinary leading and trailing spaces; be aware that TRIM also reduces repeated spaces within text to single spaces. Check the cleaned results before using them as choices, especially if spaces inside names are meaningful.

Also check for errors in the source. FILTER can return an error if a value being evaluated contains an error, and a formula error in the helper cell prevents the dropdown list from working properly. If every source cell is blank, FILTER returns a no-results error unless an alternative result is supplied. In that situation, add at least one valid choice or handle the empty-list case deliberately; returning an empty string as a fallback can itself create a blank entry.

Set up validation without a dynamic formula

If the list is short and changes rarely, the simplest solution is to place all choices together in one continuous range, with no empty cells inside it. Then select the destination cells, choose Data > Data Validation, set Allow to List, and select or enter just the cells that contain choices. Leave unused rows outside the source range rather than including them in the dropdown.

In older Excel versions without the dynamic-array functions used above, make a separate contiguous list by copying only the nonblank values into a helper area. Update that helper list when the source changes. This manual approach is less automatic but avoids relying on newer functions and is often easier to maintain for a small, stable set of choices. Do not delete blank cells from the middle of a source column if other columns on those rows hold related records; shifting cells can misalign the data.

FILTER, SORT, and UNIQUE are available in Microsoft 365 and Excel 2021 or later. In Excel for Mac, the same functions and basic Data Validation approach are available in supported versions; the ribbon layout may differ slightly. Excel for the web supports many dynamic-array formulas, but if you cannot manage a defined name or validation setting in your current interface, make those setup changes in the desktop app.

Common problems and how to fix them

The dropdown still has a blank choice: Check whether the source contains spaces, nonprinting characters, or a formula returning a space rather than an empty string. Also confirm that the validation rule uses =DropdownItems, not the original unfiltered range.
The source box rejects the spill reference: Excel’s Data Validation source box may not accept a spill reference typed directly. Use a defined name that refers to the spill range, then enter the name in Source.
The list does not grow: If the helper formula uses A2:A100, entries below row 100 are outside its source. Increase the range or use an Excel table reference. Confirm that the defined name refers to the spill range with the final #.
The formula shows a spill error: Clear cells below and beside the helper formula that block its results. The spill area must be empty, and the formula cannot spill inside an Excel table.
The arrow is missing: Select a cell with the validation rule and check Data > Data Validation. Confirm Allow is set to List and In-cell dropdown is selected. A cell can still have validation even if its dropdown arrow is not currently visible until the cell is selected.
Old invalid entries remain in destination cells: Changing the source list does not automatically clear values already entered in cells. Review those cells separately. The validation setting also controls how Excel responds to invalid new entries; an error alert can help prevent choices outside the list.

Choose the right method for your workbook

Use the FILTER-based helper list when the source changes often, contains gaps, or needs duplicates removed. It gives you an automatically maintained set of choices while leaving the original data in place. A table-backed formula is particularly useful when users regularly append new items.

Use a clean, contiguous range for a short list that rarely changes. It is easier to understand and requires no helper formula or defined name. For an older Excel version, a manually maintained helper range is often more reliable than trying to reproduce modern dynamic-array behavior with complicated formulas.

Whichever method you choose, keep the source choices on a clearly labeled sheet, avoid editing the helper spill area, and test the rule in a destination cell. A quick test should confirm that ordinary choices appear, blank source rows do not, and a newly added choice behaves as expected.

Frequently Asked Questions

Does Ignore blank remove blank items from the dropdown?
No. Ignore blank determines whether the cell receiving the dropdown may be left empty. To remove empty options, change the source range or use a filtered helper list.

Can I use a FILTER formula directly in the Data Validation Source box?
For a reliable dynamic setup, put FILTER in a worksheet helper cell, define a name referring to its spill range, and use that name as the source. The validation source box does not consistently accept a dynamic-array formula typed directly.

Why does my dropdown show a blank even though the cell looks empty?
The source cell may contain spaces or another invisible character. A formula may also return a space instead of an empty string. Clean the source and check the exact cell contents.

Will the dropdown update when I add a new item?
Yes, if the helper formula’s source covers the new item or uses an Excel table column, and the validation source refers to the helper’s spill range through a defined name. A fixed source range will not include entries added beyond its boundary.

Can I remove duplicates as well as blanks?
Yes. The example uses UNIQUE to return one copy of each nonblank value. If repeated choices are intentional, remove UNIQUE from the helper formula and keep FILTER.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.