Function Problems

Create an Excel Data Validation List That Excludes Used Teams

Anum MushtaqAnum Mushtaq Nov 18, 2026 868 views

Question details

Create a dropdown list that automatically excludes teams already selected.

Create an Excel Data Validation List That Excludes Used Teams
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.
Before you start

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.

Solution 1Recommended

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.

1
Name the ranges

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.

2
Create the available-team list

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.

3
Add the dropdowns

Select the cells in UsedTeams, then choose Data > Data Validation. Under Allow, select List and enter =$H$2# in Source. Click OK.

4
Test the list

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.

Use a Spilled Formula as the Data Validation Source
When all teams are used: If no teams remain, FILTER returns a #CALC! error unless an empty-result value is provided. Existing selections are not automatically cleared when the source list changes.
Free Microsoft Office alternative

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.

Open and edit Microsoft Excel .xlsx workbooksWork with team lists and spreadsheet data in a familiar interfaceUse a free, lightweight office suiteMove existing spreadsheets into WPS Office without starting from scratch
microsoft office alternative - wps office

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.