Skip to main content

Three Types of Multi-Column Drop Down Lists – Step by Step Guide

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

Make Dependent Drop-Down List
Method A: Using formulas
Method B: Just a few clicks by Kutools for Excel

Display Multiple Selections in Drop-Down List
Method A: Using VBA Script
Method B: Just a few clicks by Kutools for Excel

Display Multiple Columns in Drop-Down
Method: Using combo box as an alternative

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

Related Videos

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.

1) Click the Settings tab;
2) Select List in the Allow drop-down list;
3) Click in the Source box, select the cells containing the continents you want to display in the drop-down list;
4) Click the OK button. See screenshot:

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:

1) Stay in the Settings tab;
2) Select List in the Allow drop-down list;
3) Enter the following formula in the Source box.
=INDIRECT(SUBSTITUTE(G9," ","_"))
Where G9 is the first cell of the main drop-down list cells.
4) Click the OK button.

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:

1) Stay in the Settings tab;
2) Select List in the Allow drop-down list;
3) Enter the following formula in the Source box.
=INDIRECT(SUBSTITUTE(H9," ","_"))
Where H9 is the first cell of the secondary drop-down list cells.
4) Click the OK button.

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 might be cumbersome for some users, if you want a more efficient and straightforward solution, the following method can be achieved with just a few clicks.

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:

1. Enable the feature;
2. Choose the mode you need: 2 level or 3-5 level drop-down list;
3. Select the columns you need to create dependent drop-down list based on;
4. Select an output range.

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. Here is a recommended powerful feature - Multi-selection Drop-down List that can help you easily select multiple items from drop-down list.

After installing Kutools for Excel, go to the Kutools tab, select Drop-down list > Multi-select Drop-down List. Then configure as follows.

  1. Specify the range containing the drop-down list from which you need to select multiple items.
  2. Specify the separator for the selected items in the drop-down list cell.
  3. Click OK to complete the settings.
Result

Now, when you click on a cell with a drop-down list in the specified range, a list box will appear next to it. Simply click the "+" button next to the items to add them to the drop-down cell, and click the "-" button to remove any items you don't want anymore. See the demo below:

Notes:
  • Check the Wrap Text After Inserting a Separator option if you want to display the selected items vertically within the cell. If you prefer a horizontal listing, leave this option unchecked.
  • Check the Enable search option if you want to add a search bar to your drop-down list.
  • To apply this feature, please download and install Kutools for Excel first.

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 displays only one column of items. To display multiple columns in a drop-down list, we recommend using 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.

1) In the ColumnCount field, enter a number that represents the number of columns you want to display in the drop-down list;
2) In the ColumnWidths field, please define the width for each column. Here I define the width of each column as 80 pt;100 pt;80 pt;80 pt;80 pt;
3) In the LinkedCell field, specify a cell to output the same value as the one you selected in the drop down. This cell will be used in the following steps;
4) In the ListFillRange field, enter the data range you want to display in the drop-down list.
5) In the ListWidth field, specify a width for the entire drop-down list.
6) Close the Properties dialog box.

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
Tip: 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,
1) B1 is the cell you specified as the LinkedCell in the Properties dialog box.
2) The number 2 represents the second column of the table range "B3:F6".
3) The VLOOKUP function here searches for the values in B1 and returns the value in the second column of the range B3:F6.
4) The IFERROR handles errors in the VLOOKUP function. If the VLOOKUP function evaluates to a #N/A error, the IFERROR function will return the error as null.

Related Articles

Autocomplete when typing in Excel drop down list
If you have a data validation drop down list with large values, you need to scroll down in the list just for finding the proper one, or type the whole word into the list box directly. If there is method for allowing to auto complete when typing the first letter in the drop down list, everything will become easier. This tutorial provides the method to solve the problem.

Create drop down list from another workbook in Excel
It is quite easy to create a data validation drop down list among worksheets within a workbook. But if the list data you need for the data validation locates in another workbook, what would you do? In this tutorial, you will learn how to create a drop fown list from another workbook in Excel in details.

Create a searchable drop down list in Excel
For a drop down list with numerous values, finding a proper one is not an easy work. Previously we have introduced a method of auto completing drop down list when enter the first letter into the drop down box. Besides the autocomplete function, you can also make the drop down list searchable for enhancing the working efficiency in finding proper values in the drop down list. For making drop down list searchable, try the method in this tutorial.

Auto populate other cells when selecting values in Excel drop down list
Let’s say you have created a drop down list based on the values in cell range B8:B14. When you selecting any value in the drop down list, you want the corresponding values in cell range C8:C14 be automatically populated in a selected cell. For solving the problem, the methods in this tutorial will do you a favor.

More tutorial for drop down list...

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations