How to select multiple items from Drop Down List in Excel

By SpreadCheaters
Page last updated: 02/11/2022 | Next review date: 02/11/2024
close

Author bio


SpreadCheaters
View More north_east
We can achieve selecting multiple items by adding custom VBA code to the worksheet. Let’s see how we can do it. Consider this data set which has information about team members of a hockey team and we wish to create a drop down list in a cell to select first names of the team members.

Microsoft Excel has a built-in option to create drop down lists through Data Tools. Through the drop down lists we can select only one item at a time from a long list of items. However, if we want to select multiple items then there is no way to do it through the available tools.

Step 1 – Locate the Data Tools Group to Create a drop down list

– Let’s first locate the tools which will help to create a drop down list by going to the Data tab and then locating the Data Validation in the Data Tools group.

Step 2 – Select the Data Source, type of Drop Down and Create

– Now click on the Data Validation button.
– In the Settings tab and Validation Criteria, select List. 
– Then select the source data from which you wish to create the drop down list and press OK. The drop down list will be created in the selected cell, which is I2 in our case, but you can choose only one item from the list.

Step 3 – Save the File as Macro Enabled Workbook

– Now to add the option to select multiple items from this drop down list, first save the file as Macro Enabled Workbook. 
– For this click on the File menu, click on SaveAs and save the file with the .xlsm extension. This will make the workbook to run the macros when required.

Step 4 – Open VBA Editor

– The VBA code has to be used to enable the multiple selection drop down list. To do this open the VBA code editor by pressing ALT+F11 keys simultaneously.
– Double click the Sheet’s Name in the project window. This will open up a white space in the right panel.

Step 5 – Insert the VBA Code

– Now it’s time to add the VBA code to the Workbook. However, the code has to be added in the right place so that it can work properly.

It is important to note that we have added this code to the working sheet because we wish to look for any change in the Cell I2 where we have inserted the drop down list. In case of any change in that particular cell the code will run and allow us to make multiple selections.

Step 6 – Test the Multiple Selection Feature

– After adding the code we can now select multiple items from the list. Let’s test it.

Code Snippet:

Here is the code. Before using the code you need to change the cell address “$I$2” to whatever cell you will use for creating the drop down list.