Create an Excel Data Validation List That Excludes Used Teams
Question details
Create a dropdown list that automatically excludes teams already selected.

- Product
- Microsoft Excel for Microsoft 365
- Device & OS
- not provided
- Scenario
- A user selects teams in a worksheet and wants subsequent dropdowns to show only teams that have not been used.
- Observed behavior
- The dropdown should update as team selections change, while retaining existing selections.
Use a version of Excel that supports dynamic arrays, including FILTER and SORT. Prepare a team list without blank entries and identify the cells where teams will be selected.
Use a Spilled Formula as the Data Validation Source
Generate a sorted list of available teams and use its spill range as the dropdown source.
Select the team list and use Formulas > Define Name to name it TeamList. Define the selection cells as UsedTeams, including the cells where you will add the dropdowns.
In an empty cell such as H2, enter =SORT(FILTER(TeamList,NOT(ISNUMBER(MATCH(TeamList,UsedTeams,0))))). Leave the cells below H2 clear so the results can spill.
Select the cells in UsedTeams, then choose Data > Data Validation. Under Allow, select List and enter =$H$2# in Source. Click OK.
Choose a team from one dropdown, then open another dropdown. The selected team should no longer appear; clearing the selection should make it available again.

Work with Team Selection Workbooks in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open Excel workbooks to continue working with team lists; check dynamic formulas and dropdowns after migration because support can vary by version.

Frequently Asked Questions
What does the # after H2 mean in the validation source?
The reference =$H$2# points to the entire range spilled by the formula in H2. As the formula returns more or fewer teams, the referenced range changes with it.
Will a selected team disappear from its cell?
No. Updating the validation source does not automatically clear an existing selection, even though that team is removed from the available list.
Why does the formula show a #SPILL! error?
The cells needed for the results may not be empty. Clear the cells below H2, or move the formula to an area with enough empty space.
What happens when every team has been selected?
FILTER has no matching teams to return and displays #CALC! unless you supply an empty-result value. Clearing a team selection makes that team available again.




