Hide Used Names in Excel Drop Down List

In some Excel worksheets, you might want each item entered only once. For example, assign employees for one weekly on-call assignment, without duplicates. Instead of a normal drop-down, use this trick, to make a drop down list that hides all the used names.

Video: Hide Used Items in Drop Down List

In this video, there’s a weekly on-call schedule, where each employee should only be assigned to a single day.

I show how to make a list of the unused names, using dynamic functions in Excel 365. After that, I set up a data validation drop down to show those unused names, instead of the full list.

To get the Excel workbook for this video, go to the Hide Used Items in Excel Drop Down page of my Contextures site
That page also has detailed steps for Excel 365, and for older versions of Excel

Video Timeline

Here’s the video timeline, so you can go back and watch any section again:

00:00 Drop Down All Names
01:06 Formula for Unused Names
03:04 Change the Drop Down

Employee Schedule

IN the sample file, on the Schedule sheet, there is a named table – tblSched.

Scheduled employee names are entered in the Employee column
The data validation drop down list only shows names that haven’t been selected already – the unused names.

Formula for List of Unused Names

On another sheet, named Lists, there’s a table named tblEmp. It has the full list of employees, and names can be added or removed, as needed.

To get the unused names, I created a formula in cell D2:

=SORT(FILTER(tblEmp[EmpList],
COUNTIF(tblSched[Employee], tblEmp[EmpList])=0))

The formula results spill down from cell D2, to the cells below, in as many rows as necessary.

How it Works

Here’s how the unused names formula works:

First, the COUNTIF function checks for each name in the Scheduled table
- If a name is not found, its count is zero
Next, the FILTER function returns all the names with a zero count (unused)
Finally, the SORT function puts those names in alphabetical order.

Drop Down List Setup

The data validation drop down list on the Schedule sheet uses the unused names list as its source.

Here’s how to set that up:

On the Schedule sheet, select all the employee cells in the table named tblSched
On the Excel Ribbon, go to the Data tab, and click Data Validation
For Allow, select List
As the Source, go to the List sheet, and click on cell D2 ( the cell with the SORT FILTER formula)
Add a number sign (#) at the end of the cell reference
- =Lists!$D$2#
Click OK, to save the changes

Note: The number sign (#) is a spill operator. It tells Excel to use the entire spill range that starts in D2, not just the single D2 cell.

Test the Dynamic List

To test the updated drop down, follow these steps:

On the Schedule sheet, for a date with no employee selected, choose a name from the employee drop down.
Then, go down to the next row, and click the drop-down arrow.
The name that you just assigned is removed from the list, so you can’t accidentally assign it again.

Note: If you delete any names from the schedule, those names automatically return to the unused list.

Get the Workbook

To get the Excel file for this video, go to the Hide Used Items in Excel Drop Down page on my Contextures site. The zipped Excel file is in xlsx format, and does not contain any macros.

That page also has detailed steps for Excel 365, and for older versions of
Excel.