Excel Data Validation List: How to Exclude Blanks in Excel

How-to basics · Updated November 19, 2026

To exclude blank entries from an Excel data validation dropdown in Microsoft 365, create a helper list with FILTER, then point the validation rule to that spilled list. For example, if the source values are in A2:A100, put the formula below in D2, define a name for the spill range D2#, and use that name as the dropdown’s source.

With the source values in A2:A100 and the filtered list starting in D2, enter: =FILTER($A$2:$A$100,$A$2:$A$100<>""). The result is a compact list containing only nonblank entries; the validation dropdown no longer has empty items between its choices. The helper column can be on another worksheet and hidden after setup.

Why blank entries appear in a dropdown

A list-based validation rule uses a range or a named range as its list of permitted choices. If that source includes empty cells, Excel can treat those positions as blank entries in the dropdown. This is common when a list has been given extra rows for future additions: a range such as A2:A100 may contain only ten names today, but the remaining cells are still part of the source range.

Blanks can also arise when a source list is maintained by formulas. A formula that displays an empty string may look like an unused cell, even though the cell contains a formula. A direct source range can therefore produce empty-looking choices or confusing gaps, depending on how the source is laid out and the Excel build. Filtering the list based on whether each value is empty removes both genuinely empty cells and cells whose formulas return an empty string.

There are two different issues that are easy to confuse: an empty item offered by the dropdown, and whether a user is allowed to leave the validated cell empty. Removing blank source entries addresses the first. The validation setting called Ignore blank concerns the second; it does not clean the dropdown’s source list.

Build a filtered helper list in Microsoft 365

FILTER is available in Microsoft 365 and Excel 2021 or later. It returns only the values whose corresponding test is TRUE, and its results spill into neighboring cells automatically. To set up a helper list:

  1. Keep the original choices in one column, such as A2:A100. Leave the header out of the range.
  2. Choose an empty area with enough room below it for the results. In this example, use D2.
  3. With source values in A2:A100 and the output beginning in D2, enter =FILTER($A$2:$A$100,$A$2:$A$100<>""). If the source contains Blue, Green, a blank cell, and Red, the formula returns Blue, Green, and Red, in that order.
  4. Confirm that the results have space to spill. Any existing value or merged cell in the spill area can cause a #SPILL! error.
  5. Give the spill range a workbook-level name, then use that name in the validation rule, as described below.

The test compares each source cell with an empty string. It excludes genuinely empty cells and formula results that display as empty strings. It does not remove cells containing spaces, such as a cell with one space character. Those are not empty to Excel and may still appear as apparently blank choices.

Connect the spill range to Data Validation

Using a defined name for the spill range is a reliable way to connect a dynamic array to Data Validation. In Excel for Microsoft 365 on Windows, select Formulas > Name Manager > New. Enter a name such as DropdownItems, set Scope to Workbook, and set Refers to to =Sheet1!$D$2#, replacing Sheet1 with the actual name of the worksheet containing the helper formula. The # refers to the entire current spill range, not just its first cell.

Next, select the cells where users should choose an item, then select Data > Data Validation. On the Settings tab, set Allow to List and enter =DropdownItems in Source. Make sure In-cell dropdown is selected, then select OK. If your worksheet name contains spaces, Excel may add apostrophes around it in the name definition; accept the reference Excel provides.

When a value is added to the original source range, the FILTER results update and the named spill range expands or contracts. The validation list then follows that changing range without needing to edit its source manually. Keep the helper formula outside the cells being validated, and ensure the worksheet has room for the spill results.

On Excel for Mac, the same approach works in current Microsoft 365 versions: use the Formulas tab to create the name and Data > Data Validation to configure the list. Ribbon arrangement can vary slightly by release. Excel for the web supports many data-validation tasks, but its interface and support for editing defined names can differ; if the name or spill reference is difficult to configure there, set it up in the desktop app.

What Ignore blank does—and does not do

The Ignore blank checkbox in the Data Validation dialog is often mistaken for a command to remove empty options. It is not a list-cleaning control. It determines how Excel treats blank input cells when applying the validation rule. Leaving it selected generally permits the validated cell to remain empty; clearing it makes blank input invalid under the rule. Neither choice removes blank cells from the list source.

For example, suppose a dropdown source contains East, West, and an empty cell. Clearing Ignore blank does not turn that source into a two-item list. It changes the treatment of an empty validated cell, while the empty source entry remains a separate concern. To prevent the dropdown from offering that empty item, use a filtered source or remove the blank from the source range.

Validation is also not a complete barrier against every way data can enter a workbook. Pasting or filling cells can bypass or replace validation rules in some workflows. If a clean list is important, check the source and the validated cells after bulk edits, and consider protecting the sheet or using additional review controls where appropriate.

Other ways to avoid blank choices

A dynamic helper list is not always necessary. The best approach depends on whether the list changes and which Excel versions need to use the workbook.

  • Use a tight source range: If the choices occupy A2:A12 with no gaps, use only that range rather than including dozens of unused rows. This is simple, but someone must update the range when the list grows.
  • Remove gaps from the source: Move the choices together and exclude unused rows from the validation source. This works well for a short, rarely changed list, but does not automatically adapt to new entries.
  • Use a filtered helper range: FILTER is a good fit when the source contains blanks or formulas returning empty strings and the workbook uses Microsoft 365 or Excel 2021 and later.
  • Use a table for the source data: Tables expand as rows are added, which helps maintain the source data. A table by itself does not remove blank rows or formula results that look empty; pair it with a suitable helper list if blanks must be excluded.

For older Excel versions without dynamic arrays, a compact, contiguous source range is usually the easiest option to maintain. Legacy formula-based helper lists are possible, but they can require array formulas and version-specific entry steps. If a workbook must work across older releases, verify the chosen method in those releases rather than assuming a modern spill reference will be understood.

Common problems and how to fix them

  • The dropdown still has a blank choice: Check whether the source contains spaces or another character rather than a true blank. Also verify that the validation rule points to the named spill range, not the original unfiltered column.
  • The helper formula shows #SPILL!: Clear cells beneath and beside the formula where its output needs to appear. Look for merged cells or other content occupying the spill area.
  • The validation source is rejected: Check the name’s spelling and scope, and confirm that Refers to points to the helper formula’s spill range. A workbook-level name is generally easiest to use from validation rules on multiple sheets.
  • A new source item does not appear: Confirm it was entered inside the range the formula checks. If the formula checks A2:A100, an entry in A101 is outside its scope. Expand the range or use a source layout that grows with the data.
  • There is an empty item when all source cells are blank: FILTER without a matching result returns an error unless an optional empty-result value is supplied. In a normal dropdown list, it is usually better to ensure at least one real choice exists or handle the all-empty case deliberately.

Also check for duplicate values. FILTER removes blanks but preserves duplicates and the original order. That may be exactly what the list needs; if repeated choices are accidental, clean the source or use a separate formula designed to return unique values. Do not assume that excluding blanks also sorts or deduplicates the list.

Choosing the right method for a changing list

For a small list that rarely changes, a clean, tightly defined range is easier to understand than a formula-driven setup. For a list maintained by multiple people or updated regularly, the helper formula reduces routine range editing and makes the exclusion rule visible. Put a brief label near the helper list, such as “Dropdown choices—filtered,” so another workbook editor can identify its purpose.

Consider where the source values come from, too. If another process imports data, it may add trailing spaces, formulas, or blank rows. A simple FILTER condition removes empty-looking values but does not standardize spelling, trim ordinary entries, or resolve duplicates. Those are separate data-cleaning decisions. For example, “North” and “North ” may look the same to a person but can behave as different text values.

Finally, test the rule using a few representative cells: choose a valid item, try typing a value that is not in the list, and inspect the dropdown after adding or removing a source entry. This confirms both that the displayed choices are clean and that the validation rule refers to the intended list.

Frequently Asked Questions

Does Ignore blank remove blank items from an Excel dropdown?
No. Ignore blank affects whether the validated cell may be left empty. To remove empty choices, use a filtered helper list or exclude the empty cells from the source range.

Can I use FILTER directly in the Data Validation Source box?
It is more dependable to put FILTER in a worksheet cell, create a defined name that refers to the spill range with #, and use that name in Source. This also makes the helper results visible for troubleshooting.

Does FILTER exclude cells containing formulas that return an empty string?
Yes. The condition in =FILTER($A$2:$A$100,$A$2:$A$100<>"") excludes values equal to an empty string, including formula results that display nothing. It does not exclude cells containing spaces.

Will the dropdown update when I add another choice?
Yes, if the new value is entered within the range checked by the helper formula. The spill list and its defined name update with the results. An entry outside that checked range will not be included until the formula’s source range is expanded.

Can I exclude blanks without a helper column?
For a fixed, contiguous list, set the validation source to only the cells that contain choices. For a changing list with blanks in the middle, a helper range is generally clearer and easier to maintain than trying to make the validation rule clean the source itself.

Related

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.