How to hide used items in dropdown lists in Excel
Dropdowns are a great way to controls user entries to specific values. Although the default data validation dropdown in Excel doesn’t support hiding items as they are used, we can mimic the behavior with formulas. In this guide, we’re going to show you How to hide used items in dropdown lists in Excel.
Model
In our example we have a list of names which we want to match with different assignments. Because we do not want to assign the same person into two places, hiding used items in the dropdowns will simplify the usage.
Hiding used items in dropdown lists
In this guide we will use the power of dynamic arrays which is supported in Microsoft 365 only. The formula will use the FILTER function to eliminate the values which are already selected.
Either the COUNTIFS or the COUNTIF function is perfect for determining the used items. Either function can return the count of items in the original list which are also selected in other dropdowns. If the count is equal to 0, the item is not used.
Let's apply the same function into our model.
You can see that the new list doesn't include the name "Stu" because its already in Staff_Assigned (C7:C9).
Do not forget to update the data validation in the dropdown cells. The dropdowns should take their lists from the new list.
