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:

DOWNLOAD FREE EXCEL EBOOK

51 Excel Tips to Save Time & Increase Productivity

(22,000+ Enthusiasts have Already Downloaded)

  • shanks

    Love this! It’s possible to do with only three helper columns, though. You can remove the first helper column if you use this in the second:

    =IF(ISERROR(MATCH(B3,’Drop Down No Repetition’!C$3:C$7,0)),MATCH(B3,B$3:B$9,0),””)

    Always a fan of reducing helper columns!

    • Thanks for sharing Shanks 🙂 Glad you liked it. And you are right, the first helper column could have been avoided. Just wanted to make it simple to follow in the article.

  • Emily K

    How can you expand this to use for say 100 items in your drop down list.

  • Emily K

    I am working on a simple form using excel. I have 4 cells with drop
    down lists where I have the following code (with your help) to allow for multiple
    selections without repetition.

    Private Sub Worksheet_Change(ByVal Target As Range)
    ‘Code by Sumit Bansal from http://www.trumpexcel.com

    ‘ To Select Multiple Items from a Drop Down List in Excel
    Dim Oldvalue As String
    Dim Newvalue As String

    Application.EnableEvents = True
    On Error GoTo Exitsub
    If Target.Address = “$C$10” Or Target.Address = “$C$16” Or Target.Address = “$C$17” Or Target.Address = “$C$19” Then

    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    Else: If Target.Value = “” Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
    If Oldvalue = “” Then
    Target.Value = Newvalue
    Else

    If InStr(1, Oldvalue, Newvalue) = 0 Then
    Target.Value = Oldvalue & “, ” & Newvalue
    Else:
    Target.Value = Oldvalue
    End If
    End If
    End If
    End If
    Application.EnableEvents = True
    Exitsub:
    Application.EnableEvents = True
    End Sub

    My problem is that I need to protect the sheet. When I do this, the
    user can still access the drop down list but the option for multiple
    selections without repetition quits working. How can I fix this issue.
    Thanks in advance for any assistance.

  • Marylou Thurber

    Invaluable writing ! For my two cents , if someone require to merge PDF or PNG files , We discovered a service here http://goo.gl/1widdK

  • Mark Chalmers

    Thanks! Having trouble though. When I add a name everything works fine all the way to the numbers re-stacking (in helper column 3) but then all the names on the list get repeated in helper column 4.

    i.e.

    I Start with Bill, Jo, Frank and Susan. The first person assigned is Susan, In Column 4 the following shows up.

    Bill
    Jo
    Frank
    Bill

    Jo

    Frank
    Susan

    Any ideas? Im working in Google docs. Maybe that’s the problem?