When you search for “excel drop down list multiple columns” on Google, you may need to achieve one of the following:

In this tutorial, we will demonstrate step by step how to achieve these three operations.

Related Videos

Get Sample File:

Click to download the sample file

Make a Dependent Drop-Down List Based on Multiple Columns

As shown in the GIF image below, you want to create a main drop-down list for the continents, a secondary drop-down list containing countries based on the continent selected in the main drop-down list, and then the third drop-down list containing cities based on the country selected in the secondary drop-down list. The method in this section can help you achieve this task.

Using formulas to make a dependent drop-down list based on multiple columns

Step 1: Create the main drop-down list

1. Select the cells (here I select G9:G13) where you want to insert the drop-down list, go to the Data tab, click Data Validation > Data Validation.

2. In the Data Validation dialog box, please configure as follows.

Step 2: Create the secondary drop-down list

1. Select the entire range that contains the items you want to display in the secondary drop-down list. Go to the Formulas tab, and then click Create from Selection.

2. In the Create Names from Selection dialog box, only check the Top row box and then click the OK button.

3. Select a cell where you want to insert the secondary drop-down list, go to the Data tab, click Data Validation > Data Validation.

4. In the Data Validation dialog box, you need to:

=INDIRECT(SUBSTITUTE(G9," ","_"))

5. Select this drop-down list cell, drag its AutoFill Handle down to apply it to other cells in the same column.

The secondary drop-down list is now complete. When you select a continent in the main drop-down list, only the countries under this continent are displayed in the secondary drop-down list.

Step 3: Create the third drop-down list

1. Select the entire range that contains the values you want to display in the third drop-down list. Go to the Formulas tab, and then click Create from Selection.

2. In the Create Names from Selection dialog box, only check the Top row box and then click the OK button.

3. Select a cell where you want to insert the third drop-down list, go to the Data tab, click Data Validation > Data Validation.

4. In the Data Validation dialog box, you need to:

=INDIRECT(SUBSTITUTE(H9," ","_"))

5. Select this drop-down list cell, drag its AutoFill Handle down to apply it to other cells in the same column.

The third drop-down list containing cities is now complete. When you select a country in the secondary drop-down list, only the cities under this country are displayed in the third drop-down list.

The above method is troublesome for most of us, if you want to solve the problem easily and efficiently, the following method can help to achieve with a few clicks only.

A few clicks to create a dependent drop-down list based on multiple columns with Kutools for Excel

The GIF image below shows the steps of the Dynamic Drop Down List feature of Kutools for Excel.

As you can see, the whole operation can be done in just a few clicks. You just need to:

The GIF image above only demonstrates the steps to make a 2-level drop-down list. If you want to make a drop-down list with more than 2 levels, click here to know more . Or download the 30-day free trial.

Make Multiple Selections in a Drop-Down List in Excel

This section provides two methods to help you make multiple selections in a drop-down list in Excel.

Using VBA codes to make multiple selections in an Excel drop-down list

The following VBA script can help to make multiple selections in a drop-down list in Excel without duplicates. Please do as follows.

Step 1: Open the VBA code editor and copy the code

1. Go to the sheet tab, right click on it and select View Code from the right-clicking menu.

2. Then the Microsoft Visual Basic for Applications window pops up, you need to copy the following VBA code in the Sheet (Code) editor.

VBA code: Allow multiple selections in a drop-down list without duplicates

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 2019/11/13
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Step 2: Test the code

After pasting the code, press the Alt + Q keys to close the Visual Editor and go back to the worksheet.

Tips: This code works for all drop-down lists in the current worksheet. Just click a cell containing the drop-down list, select items one by one from the drop-down to test if it works.

Note: If you want to allow multiple selections in a drop-down list and delete existing items when selecting it from the drop-down again, you also can apply a VBA code to achieve: Click here to follow the tutorial step-by-step

A few clicks to make multiple selections in an Excel Drop-down list with Kutools for Excel

VBA code has many limitations. If you are not familiar with VBA script, it is hard to modify the code to meet your needs. For example, change the working scope, or the item separator. Here is a recommended powerful feature - Multi-selection Drop-down List that can help you handle this task easily. You can easily specify a scope to carry out the function, and change the separator to any one you like.

As you can see in the above GIF image, the whole operation can be done in just a few clicks. Suppose you have already inserted data validation drop-down list in your worksheet, now you just need to:

Tips: After finishing the setup, when you click on the cell containing the drop-down list, a list including the “+” and “-“ signs will be displayed on the right side. Just click the "+" sign to add the corresponding item to the cell, and click the "-" sign to remove it from the cell.

Click here to know more about this feature, or download the 30-day free trial.

Display Multiple Columns in a Drop-Down List

As shown in the screenshot below, this section is going to show you how to display multiple columns in a drop-down list.

By default, a data validation drop-down list only displays one column of items. To display multiple columns in a drop-down list, we recommend that you use a Combo Box (ActiveX Control) instead of a data validation drop-down list.

Step 1: Insert a Combo Box (ActiveX Control)

1. Go to the Developer tab, click Insert > Combo Box (ActiveX Control).

Tips: If the Developer tab does not display in the ribbon, you can follow the steps in this tutorial “Show Developer tab” to show it.

2. Then draw a Combo Box in a cell where you want to display the drop-down.

Step 2: Change the Properties of the Combo Box

1. Right click the Combo Box then select Properties from the context menu.

2. In the Properties dialog box, please configure as follows.

Step 3: Display the specified columns in the drop-down list

1. Under the Developer tab, turn off the Design Mode by just clicking the Design Mode icon.

2. Click the arrow of the combo box, the list will be expanded and you can see the specified number of columns being displayed in the drop-down.

Note: As you can see in the above GIF image, although multiple columns are displayed in the drop-down list, only the first item in the selected row is displayed in the cell. If you want to display items from other columns, go ahead and apply the following formulas.

Step 4: Show items from other columns in certain cells

Tips: In order to return exactly the same format data from other columns, you need to change the format of the result cells before or after the following operations. In this example, I change the format of cell C11 to Date format and change the format of cell C14 to Currency format in advance.

1. Select a cell under the combo box, enter the formula below and press the Enter key to get the value of the second column in the same row.

=IFERROR(VLOOKUP(B1,B3:F6,2,FALSE),””)

2. To get the values of the third, fourth and fifth columns, apply the following formulas one by one.

=IFERROR(VLOOKUP(B1,B3:F6,3,FALSE),””)

=IFERROR(VLOOKUP(B1,B3:F6,4,FALSE),””)

=IFERROR(VLOOKUP(B1,B3:F6,5,FALSE),””)

Notes:

Take the first formula =IFERROR(VLOOKUP(B1,B3:F6,2,FALSE),””) as an example,

Related Articles