Hide Used Items in Drop Down List

Limit the choices in a Microsoft Excel drop down list, by hiding items that have been previously selected. Several examples of how to use this technique, and workbooks to download.

Introduction

You can limit the choices in a drop down list, hiding items that have been previously selected. For example, if you are assigning employees to a shift, you don't want to assign the same employee twice.

In the drop down list shown below, Fred and Joe have been assigned, so their names aren't in the list now. The instructions for this workbook are shown below.

NOTE: If your version of Excel has Dynamic Arrays, use the much simpler instructions shown below, for dynamic drop downs. Dynamic arrays are available in Microsoft 365 plans.

Thanks to Peo Sjoblom, who contributed the original formula for this technique, and to Daniel.M, who suggested the enhanced formulas.

Set up the Main Table

To set up the Employee Shift workbook, start by setting up the table in which you want to use the Excel Data Validation. In this example, the worksheet is named 'Schedule' and the range A1:C7 is being used.

• Dates have been entered in cells A2 to A7
• Column B will have Data Validation applied.

Create the List of Items

Next, create a list which contains the items you want to see in the Excel data validation dropdown list. Here, the employee names have been entered in cells A1:A6, on a sheet named 'Employees'

Calculate if a name has been used

Next, you'll add a formula beside each name, to check if that name has been used in the schedule.

1. On the Employees sheet, in cell B1, enter the following formula:

=IF(COUNTIF(Schedule!$B$2:$B$7,A1)>=1,"",ROW())

2. Copy the formula down to the last name, in cell B6.

This formula counts the occurrences of "Bert" in cells B2:B7 on the Schedule worksheet. If the count is greater than or equal to 1, the cell will appear blank. Otherwise, the row number will be displayed.

Create the list of unused names

The next step is to create a formula that will move any blank cells to the end of the list. There are two options:

1. For long lists, you can use the multi-cell array formula

2. For smaller ranges (fewer than 200 cells), use the single-cell formula, which is easier to edit.

Option 1: Multi-Cell Formula

1. Select cells C1:C6
2. Enter the following array formula (the formula is long, and should be all on one line)

   =IF(ROW(A1:A6)-ROW(A1)+1>COUNT(B1:B6),"",
       INDEX(A:A,SMALL(B1:B6,ROW(INDIRECT("1:"&ROWS(A1:A6))))))
   

3. Press Ctrl+Shift+Enter to enter the array formula in cells C1:C6

Option 2: Single-Cell Formula

If you'd prefer a single-cell formula (easier to edit), you could use this formula, also by Daniel.M. He recommends it for small ranges (<=200 cells):

1. Select cell C1
2. Enter the following formula (the formula is long, and should be all on one line)

   =IF(ROW(A1)-ROW(A$1)+1>COUNT(B$1:B$6),"",
   INDEX(A:A,SMALL(B$1:B$6,1+ROW(A1)-ROW(A$1))))

3. Press Enter
4. Copy the formula down to row 6

Name the List of Unused Names

Next, you'll create a named range, using the OFFSET function. Only the cells with names will be included, not the blank cells at the end of the list.

1. On the Ribbon's Formulas tab, click Define Name
2. In the Names in workbook box, type a one-word name for the range, e.g. NameCheck.
3. In the Scope box, leave Workbook as the selection
4. In the Refers to box, type the following formula (all on one line):
• =OFFSET(Employees!$C$1,0,0,COUNT(Employees!$B$1:$B$6),1)
5. Click OK

The OFFSET formula creates a range that starts in cell C1. The number of rows in the range is based on the count of numbers in cells B1:B6.

Tip: To see the defined names later, go to the Formulas tab ont the Ribbon, and click the Name Manager command

Add the Drop Down Lists

Next, you'll use Excel's Data Validation feature, to create drop down lists on the Schedule spreadsheet

1. On the Schedule sheet, select cells B2:B7, where the employee names will be entered.
2. On the Ribbon's Data tab, in the Data Tools group, click Data Validation.
3. In the Data Validation dialog box, go to the Settings tab
4. Click in the Allow box and from the drop down list, choose List
5. In the Source box, type an equal sign and the list name: =NameCheck
6. Click the OK button.

Test the Drop Down List

Select an employee name from the data validation list in cell B2, and another name from the drop down menu in cell B3.

When you open the list in cell B4, the list shows only the names that have NOT been used. Other names have been removed from the list.

Employee List with Dynamic Arrays

If your version of Excel has Dynamic Arrays, use these instructions -- the setup is much simpler.

• NOTE: Dynamic arrays are available in Excel for Office 365

This video shows the steps for setting up the drop down list that hides the used items. The written instructions are below the video.

Workbook Setup

On the Schedule sheet, there is a named table - tblSched. The table has 2 columns:

• Day - Weekday names are typed in this column
• Employee - use the drop down list of employee names to assign a person for each day

After an employee has been assigned, that name is removed from the drop down list.

In the screen shot below, Bert, Mike and Anne are no longer in the list.

On the Lists sheet, there is a named table - tblEmp. The table has 1 column - EmpList.

Employee names are typed in that column. The list is sorted alphabetically, but does not need to be sorted.

Available Employees List

Also on the Lists sheet, there is a dynamic array, starting in cell D2. The formula uses the new Excel functions, SORT and FILTER, combined with COUNTIF, to create a list of employees who have not been assigned on the Schedule sheet.

Here is the formula in cell D2:

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

The FILTER function returns:

• Employee names from the EmpList column
• For names that are not in the Employee column in the Schedule table (COUNTIF=0)

Then, the SORT function sorts the names alphabetically, for convenience when using the drop down list.

Note: Later, if you add a new item in the employee list table, it will automatically be included in the filtered results.

Employee Drop Down List

On the Schedule sheet, data validation was used to create drop down lists in the Employee column. Those drop down lists are based on the dynamic array of available employee names.

In the data validation window settings:

• Allow was set for List
• The Source is a formula that refers to the dynamic array starting cell, with the spill operator (#) at the end. That tells Excel to use the entire spill range for the dynamic array.
  • =Lists!$D$7#

Data Validation Errors

In the "hide used items" examples, where used items are removed from the list, Excel shows error markers on the cells, if you have that feature turned on.

You can ignore those error markers, or use the following technique (for Excel 365 -- uses spill functions (dynamic arrays).

Move Used Items to Bottom of List

This technique moves the used items to the bottom of the drop down list, instead of removing them. That prevents the data validation errors, because all of the items are in the drop down list, and valid.

Employee List

In the Employee list, there are two columns -- employee name, and a formula to calculate if the name has been used.

Here is the formula in cell C2:

• =IF(COUNTIF(tblSched[Employee],[@EmpList])>0,1,0)

Separator Item

In the list of employee names, a separator item has been added: zzz-Used

• Because it startes with "zzz", it will be sorted to the end of the unused employee names.
• It will separate the unused names from the used names, so it's clear which names can still be selected

Dynamic Names List for Drop Down

This formula is in cell E2, to create the list of names for the drop downs.

=SORTBY(SORT(tblEmp[EmpList]),tblEmp[Used],1)

Here's how the formula works:

• The SORT function sorts all the names in A-Z order
• The SORTBY function sorts that list based on the values in the Used column. That puts the unused names (zero) before the used names (1)

Drop Down List

On the Schedule sheet, the drop down lists have this data validation setting:

• Allow: List
• Source: =Lists!$E$2#

Click the drop down arrow, to see all the names, with the unused names at the top, and used names at the bottom.

For these drop down list cells, all the items are included in the list, so Excel does not mark the cells as errors.

Other Examples

There are other examples of this Hide Used Items technique. You can download these sample files in the Download section.

Baseball Players

Select a player for each position, for each of the nine innings of a baseball game. After you choose a name in an inning, it disappears from that inning's drop down list. The lists for the other innings are not affected.

In the screen shot below, Mike was selected as the pitcher, so his name is not in the drop down list. Players who have been picked for the second inning still show up in the list for the first inning.

Dependent Drop Downs

The "Hide Used Items" technique can also be adapted to work with dependent drop down lists.

In the screen shot below, a Type can be selected multiple times in column A. However, in column B, each dependent item can onlybe selected once.

For example, in cell B4, the SI-02 item is not in the SI list, because it was selected in cell B2.

Selections Across a Row

In this example, the layout is different -- selections are made across a row, instead of down a column.

In the screen shot below, Cam's name is removed from the lists in Row 2, because he has already been assigned a task for that day.

There are two versions of this sample file in the download section below:

• All Versions - uses functions available in all version of Excel
• Dynamic Arrays - uses functions available in Excel 2019 and Excel for Office 365

Hide Main Selection

In this example, people select their primary printer from a drop down list.

After that, they can select one or two backup printers, from the backup drop down lists. The primary printer does not appear in the backup lists.

On the Lists sheet, there is a named range -- PrimaryList (green cells). There is a formula in the NotUsed column, to number all the printers, except the one selected as the Primary printer.

In column F, formulas create a list of Backup Printer, and the Primary printer isn't numbered in the NotUsed column, so it isn't included in the Backup List..

Get the Workbooks

1. Employee Schedule
   • All Excel Versions: Download the zipped workbook with the employee schedule example. This workbook uses Excel functions that are available in all versions. Employee Schedule - Hide Used Items
   • Dynamic Array: Download the zipped workbook with the Employee Schedule based on Dynamic Arrays and the new Excel functions - SORT and FILTER.
2. Prevent Data Validation Errors: Download the zipped workbook with the Used Names at Bottom of Drop Down List. This file uses the new Excel functions - SORT and SORTBY.
3. Baseball Players: Download the zipped workbook to Assign Baseball Players per Inning.
4. Dependent Data Validation: Download the zipped workbook to Hide Used Items with Dependent Data Validation drop downs.
5. Hide Used Selections Across a Row
   • All Excel Versions: Hide employee names if already used in the same row.This workbook uses Excel functions that are available in all versions - Single Task Sample File
   • Dynamic Array: Hide employee names if already used in the same row. This workbook uses new Excel functions and the Spill operator (#) - Single Task -Dynamic
6. Remove Primary Printer from Backup List: Download the zipped workbook to hide the primary printer in the drop down lists for the Backup Printer selection cells.

More Tutorials

Data Validation Basics

Dependent Drop Down Lists

Data Validation Tips