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.

Download Workbook

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.

How to hide used items in dropdown lists in Excel

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.

=FILTER(<Original List>,COUNTIFS(<Used items>,<Original List>)=0)

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.

=FILTER(Staff_All,COUNTIFS(Staff_Assigned,Staff_All)=0)whereStaff_All = E7:E11Staff_Assigned = C7:C9

 

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.