Creating Multiple Drop-down Lists in Excel without Repetition

Watch Video – Creating Multiple Drop-down Lists in Excel without Repetition

Excel Drop Down Lists are intuitive to use and extremely useful in when you are creating an Excel Dashboard or a data entry form.

You can create multiple drop-down lists in Excel using the same source data. However, sometimes, it is needed to make the selection exclusive (such that once selected, the option should not appear in other drop-down lists). For example, this could be the case when you are assigning meeting roles to people (where one person takes one role only).

Creating Multiple Drop-down Lists in Excel without Repetition

In this blog post, learn how to create multiple drop-down Lists in Excel, where there is no repetition. Something as shown below:

Multiple Drop-Down Lists in Excel - No Repeat

To create this, we need to create a dynamic named range that would update automatically to remove a name if it has already been selected once. Here is how the back-end data looks like (this is in a separate tab while the main drop-down is in a tab named ‘Drop Down No Repetition’).

Multiple Drop-Down Lists in Excel - Backend Data

Here is how you can create this back-end data:

  1. Column B (Member List) has the list of all the members (or items) that you want to show in the drop-down list
  2. Column C (Helper Column 1) uses a combination of IF and COUNTIF functions. This gives the name if the name has not already been used, else it gives a blank.
=IF(COUNTIF('Drop Down No Repetition'!$C$3:$C$7,B3)>0,"",B3)
  1. Column D (Helper Column 2) uses a combination of IF and ROWS functions. This gives the serial number if the name has not been repeated, else it gives a blank.
=IF(C3<>"",ROWS($C$3:C3),"")
  1. Column E (Helper Column 3) uses a combination of IFERROR, SMALL, and ROWS. This stacks all the available serial numbers together.
=IFERROR(SMALL($D$3:$D$9,ROWS($D$3:D3)),"")
  1. Column F (Helper Column 4) uses a combination of IFERROR and INDEX functions. This gives the name that corresponds to that serial number.
=IFERROR(INDEX($B$3:$B$9,E3),"")
  1. Use the following steps to create a dynamic named range
    • Go to Formula –> Name Manager
    • In the Name Manager dialogue box, select New
    • In the New Name Dialogue Box, use the following details
      • Name: DropDownList
      • Refers to: =List!$F$3:INDEX(List!$F$3:$F$9,COUNTIF(List!$F$3:$F$9,”?*”))
        This formula gives a range that has all the names in column F. It is dynamic and updates as the names change in Column F.Multiple Drop-Down Lists in Excel - Dynamic Named Range
  2. Go to Tab Drop Down No Repetition, and create a data validation drop-down list in cell range C2:C6. Here are the steps to do this:
    • Go to Data –> Data Tools –> Data Validation
    • In the Data Validation dialogue box, use the following:
      • Validation Criteria: List
      • Source: =DropDownList
    • Click OK

Now your drop down list is ready, where once an item is selected, it does not appear in subsequent drop-downs.

Try it yourself.. Download the file
Download File Pic

Other Useful Articles on Drop-Down Lists in Excel:

Online Excel Tips Tutorials Training and Videos

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)