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.
  1. Column E (Helper Column 3) uses a combination of IFERROR, SMALL, and ROWS. This stacks all the available serial numbers together.
  1. Column F (Helper Column 4) uses a combination of IFERROR and INDEX functions. This gives the name that corresponds to that serial number.
  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:

Excel Ebook Subscribe


Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

14 thoughts on “Creating Multiple Drop-down Lists in Excel without Repetition”

  1. also, right now it is delimited by a comma. and i want to apply the new line to all the cells it 4 columns and and i have 504 lines for each unit person

  2. Question, So lets say i orginally set up for mulitple colums to select form a drop down. 1. how can i apply the next line to all the cells without haveing to reselect? and 2 how can it auto alphabitized the list ( ie i hae trust names for my drop donw_ so if i have ABC Trust, KMJ Trust and YXF Trust but I selected them in different order so it KMJ, ABC adn YXF but i want it to be ABC, KMJ adn YXF.

  3. Hi,

    I am using multiple drop down lists but they are from different sources. It is for a rota where some people have more skills and therefore can be used more than others.

    The rota lists all the roles needed so each role has a drop down which comes from its own independent source list.

    What I am trying to do is when I have selected one person they either:
    do not appear in the other drop down lists or
    An error message comes up when a name has been selected more than once.

    By using different source lists, this is complicating the issue but as not all people can do all roles, i cannot see another option.

  4. 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 Start with Bill, Jo, Frank and Susan. The first person assigned is Susan, In Column 4 the following shows up.




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

  5. 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

    ‘ 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
    Oldvalue = Target.Value
    If Oldvalue = “” Then
    Target.Value = Newvalue

    If InStr(1, Oldvalue, Newvalue) = 0 Then
    Target.Value = Oldvalue & “, ” & Newvalue
    Target.Value = Oldvalue
    End If
    End If
    End If
    End If
    Application.EnableEvents = True
    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.

      • Hey Julie,

        To make sure the code works on protected sheets, add the following line of code to the macro (right after the Dim statement).
        Me.Protect UserInterfaceOnly:=True

        Also, make sure the cell that has the drop-down list is not protected (when you protect the entire sheet. You can do that by changing the Locked property). You can read more about it here:

  6. 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.


Leave a Comment